Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: Adding the results of a query or function to a text box in a report

    I have a report its data is set through the reports datasource. I would like to add 2 text boxes, one that would return the result of a vba function that is declared in the code behind of the report. The second txt i would like to set the value to the scalar result of a query. I tried selecting query-> myQry, myScalarResult and it shows in the expression builder as =[myQry]![myScalarResult] when i run the report i get an input box requesting the parameter value myQry.

    I created a vba function in mod1 and set the control source value for the other txt as
    =myVBAResult()

    Public Function myVBAResult() As Integer
    myVBAResult = 1
    End Function

    Again when i run the report i get an input box requesting the parameter value myVbaResult.

  2. #2
    Join Date
    May 2010
    Posts
    601
    Might try using DLookup()
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Oct 2003
    Posts
    233
    Quote Originally Posted by HiTechCoach View Post
    Might try using DLookup()
    From what i recall that is bad practice or am i wrong?

  4. #4
    Join Date
    May 2010
    Posts
    601
    In Access 2.0 the DLookup() was slow with linked tables. That has not been an issue since JET 3/Access 97.

    You do want to avoid using a DLookup() within a query. Now that is really sloooooooooow.

    If you really donlt wat to use DLookup() then you can always use a recordset.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    domain functions are a powerful tool, but they do come with a performance hit. they should be used, but used with caution. that probably means sparingly. when coming across the need to use a domain function alwasy look at the performance issue and decide if there is another way of doing it.

    ferinstance I inherited a reports which made use of average dlookup and other functions, replacing the domain functions with better designed queries and structure reduced the reports runtime from over 10 minutes to less than 30 seconds. that suggests to me use in forms is probably no problem, use in reports may well be.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2003
    Posts
    233
    Quote Originally Posted by HiTechCoach View Post
    In Access 2.0 the DLookup() was slow with linked tables. That has not been an issue since JET 3/Access 97.

    You do want to avoid using a DLookup() within a query. Now that is really sloooooooooow.

    If you really donlt wat to use DLookup() then you can always use a recordset.
    I havent used access in long time, how do u use dlookup?

  7. #7
    Join Date
    May 2010
    Posts
    601
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •