Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DLookup in Code (Access 2000)

    In the Control Source of a text box in a report i have the following expression:


    =DLookUp("SumOfcartons";"OutVa";"productid = " & [productid])

    This expression works.
    Is it possible to write a code in the OnOpen event of the report using the same expression? I have tried the following:

    Me![CartonsOutput].Control Source = =DLookUp("SumOfcartons";"OutVa";"productid = " & [productid])

    CartonsOutput is the name of my textbox.When i tiried to write the record source, the name Record source did not appear
    as one of the default suggestions, and hence i suppose something is wrong with my efforts.

    Can somebody help me write the code ?

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup in Code (Access 2000)

    I should try the On Format property for the report.
    Your code :


    Me.CartonsOutput =DLookUp("SumOfcartons";"OutVa";"productid = " & [productid])

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DLookup in Code (Access 2000)

    Actually, my experience has been that the list separator determined by the regional settings is the proper one to use in VBA functions like DLookup and Nz. In Croation settings, for instance, you have to write an Nz function like this: <pre>Nz([MyField];0)</pre>

    or you get a syntax error. SQL, on the other hand, insists on US format values, so if your decimal delimiter is a comma, you have to convert the number to a US format or the SQL (and functions like DLookup, etc.) will blow up on your. The same holds true for dates in those functions and in SQL.
    Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup in Code (Access 2000)

    Please note that you *must* use US notation in Visual Basic code: the list separator has to be a comma instead of a semicolon.

    You can follow Dave Willetts suggestion and set the Value of the control in the OnFormat event of the detail section:<pre>Me![CartonsOutput] =DLookUp("SumOfcartons", "OutVa", "productid = " & [productid])</pre>

    Note that the semicolons have been replaced by commas.

    Or you can set the ControlSource property (no space between Control and Source!) in the OnOpen event of the form. IntelliSense won't show this property, but you can still use it. You need to set the ControlSource property to a string, and you have to be very careful about using quotes within this string. To put a quote within a string, you must use two quotes.<pre>Me![CartonsOutput].ControlSource = _
    "=DLookUp(""SumOfcartons"", ""OutVa"", ""productid = "" & [productid])"</pre>

    Again, commas have been used instead of semicolons.

    Finally: why do you want to do this? It's a lot more complicated than setting the property in design mode.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup in Code (Access 2000)

    Charlotte,

    I use Access 97 SR2, and my Windows regional settings are Dutch, i.e. comma as decimal separator, period as thousands separator and semicolon as list separator.

    In expressions in queries, (controls on) forms and reports entered interactively I must use the local settings, e.g.<pre>=Format([MyField];"0,00")</pre>

    In VBA code and in SQL statements, I must use US format, e.g.<pre>strFormattedValue = Format([MyField], "0.00")</pre>

    When setting properties in code, I *must* use US format, even if the property is a string such as ControlSource, e.g.<pre>txtSomething.ControlSource = "=Nz([MyField],0)"</pre>

    It works the same in Excel: to enter a formula in the formula bar, I must use local settings and local names of worksheet functions. But if I want to set the formula of a cell in code, the formula string must follow US format and use the English names of worksheet functions.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DLookup in Code (Access 2000)

    Ah, I see what you were talking about, and you're correct. However, when using the same kind of functions in controls themselves, not in code, you have to use the semicolon if that's the list separator in the regional settings, which is what *I* was referring to. It's confusing, to be sure.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup in Code (Access 2000)

    Yes, it's certainly confusing, even if you have to deal with it all the time, living in a country with non-US settings.

Posting Permissions

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