Results 1 to 9 of 9
  1. #1
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49

    DLookup Trouble

    I have a dlookup expression as follows:



    Me.PO_Number = DLookup("[Customer PO]", "Order Header", "[SO #] = " & Me.[Sales Order Number])

    Order Header is a query. Sales order is a number and matches appropriately to the table data type. When entering a number that I know has a match in the query, I get:

    Runtime Error: The number you entered isn't valid for this field. It will allow any number to be entered except those that have a match in the query. Testing out the lookup in its own query I get the correct answer, but the value repeats for all lines in the query. However, the sales order number only appears in one row in the query.

    Any ideas as to why this is happening? Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why do you need to use DLookup? Is this a form/subform arrangement?

    Think I will have to review the db to understand what is happening. If you want to provide, follow instructions at bottom of my post. If zip is still too large can upload to fileshare site and post link. I recommend Box.com.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49
    Silly mistake. I was pulling in a PO Number that had text so I had to change the field type. While I'm here...here's another.

    Me.[Item Number] = DLookup("[Item #]", "Data", "[SO #] = " & Forms![Main Order Issue Table]![Sales Order Number] And [SO Line #] = Me.[Line Number])

    This is a form / sub form setup. I get the following error when trying to populate the field (dlookup is fired by AfterUpdate)

    Runtime error 2465
    "Access can't find the field "1" refered to your expression

    There's no one anywhere in the express?!

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    First, I think "Order Header" should be "[Order Header]", with square brackets since it has a space in it.

    If it isn't the square brackets, it sounds as if the problem might not be with the DLookup itself, but rather with what the form does with the result. Is there a validation rule on the form control PO_Number or the table field it is bound to? Might the table field the PO_Number is bound to be defined as a numeric integer, and you are trying to give it a number that is too large?

    Just some ideas for where you might look.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Advise no spaces or punctuation/special characters (underscore only exception) in naming convention.

    Need to concatenate " AND [SO Line #] = " as literal text.

    Me.[Item Number] = DLookup("[Item #]", "Data", "[SO #] = " & Forms![Main Order Issue Table]![Sales Order Number] & " AND [SO Line #] = " & Me.[Line Number])
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Syntax error here:

    Me.[Item Number] = DLookup("[Item #]", "Data", "[SO #] = " & Forms![Main Order Issue Table]![Sales Order Number] And [SO Line #] = Me.[Line Number])

    should be

    Me.[Item Number] = DLookup("[Item #]", "Data", "[SO #] = " & Forms![Main Order Issue Table]![Sales Order Number] & " And [SO Line #] = " & Me.[Line Number])

    (not enough quotes)

  7. #7
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49
    John G, I figured out my original issue. It was a silly mistake. I do still have a dlookup issue if you see my post above. If you can think of anything there, I'd appreciate it.

  8. #8
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49
    Thanks June7 that was it. Sloppy on my part, but I'm thankful you noticed what I did wrong!

  9. #9
    Chad Access is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    49
    Thanks John!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Trouble with DLookup on Subform
    By GregShah in forum Forms
    Replies: 13
    Last Post: 12-14-2016, 12:41 AM
  2. Shortening if / dlookup >0 / then dlookup
    By Jack Russel in forum Modules
    Replies: 1
    Last Post: 02-19-2016, 03:01 PM
  3. Trouble with DLOOKUP in a Subform
    By Mubashir sabir in forum Forms
    Replies: 2
    Last Post: 08-21-2015, 01:38 PM
  4. Replies: 2
    Last Post: 03-06-2013, 11:37 PM
  5. Replies: 13
    Last Post: 11-02-2012, 01:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums