Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DLookup (Access)

    Hi,
    I am trying to use the DLookup to return a value in a text box. I am using an unbound textbox as the "matching" criteria. But whenever I type in a value, that I know should match the DLookup box remains blank or it has an "#error".

    Below is the expression used.

    =DLookUp("[Oracle Account #]","[Chart of Accounts]","[CU Account #] = Forms![Product Category Listing]![Text33]")

    Did I type something incorrectly? Chart of Accounts is not a table but a query, does that make a difference?

    Thanks.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: DLookup (Access)

    Do you have code setting the value of the textbox, or is your expression simply in the data source for the criteria? In any event, you will need something to fire on the AfterUpdate event of the unbound textbox to either refresh the control, or to actually set the value of the control.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access)

    I don't think I do. I am fairly new to Access and self taught through the help of some books.

    Here is the code for the dlookup box:
    Private Sub Text35_BeforeUpdate(Cancel As Integer)

    End Sub

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

    Re: DLookup (Access)

    What kind of field is CU Account #, a text field or a number field?

  5. #5
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access)

    It is a number field.

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

    Re: DLookup (Access)

    Try this:

    =DLookUp("[Oracle Account #]","[Chart of Accounts]","[CU Account #] = " & [Forms]![Product Category Listing]![Text33])

    If that also results in an error, check very carefully for typos - the names must be exactly right. An extra or missing space is enough to make the function fail.

  7. #7
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access)

    It still does not return a value. Does it matter that [Chart of Accounts] is a query, does it know to look for a query and not a table?

    I have attached a screenshot of the form I am trying to create.

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

    Re: DLookup (Access)

    DLookup can retrieve data from a query as well as from a table.

    I'm afraid your screenshot and description have only confused me more (VLookup is an Excel function, not an Access function). Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access)

    Attached is the stripped down database

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

    Re: DLookup (Access)

    The query Chart of Accounts is based on Eclipse Chart of Accounts (among other tables), but the latter isn't present in the database you attached.

  11. #11
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access)

    oops...Sorry. I have included the eclipse table. Thanks

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

    Re: DLookup (Access)

    If you look carefully, you will see that you have two exclamation marks !! after Forms in the DLookup expression. There should be only one.

    To make Text35 update automatically when the user has typed a number in Text 33, create an After Update event procedure for Text33:

    Private Sub Text33_AfterUpdate()
    Me.Text35.Requery
    End Sub

  13. #13
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access)

    I made the suggested changes, but there still is no value populating text35

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

    Re: DLookup (Access)

    That's because you have

    Me.Text33.Requery

    It should be

    Me.Text35.Requery

    (see my previous reply)

  15. #15
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access)

    It still doesn't work. Is it on the correct text box?

Page 1 of 2 12 LastLast

Posting Permissions

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