Results 1 to 11 of 11
  1. #1
    cap0557 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2019
    Posts
    6

    Nested Dlookup... struggling to make it work

    Hello all...

    I've searched around to find an appropriate solution to a simple issue I'm at odds with trying to resolve. Hopefully, this post will attract a positive response, so thanks in advance.

    What I am trying to do is return a value on a form for which its purpose is purely display. In order to get the result I need first to find the correct value reference and then search for that reference in the relevant table and return the string directly related.

    I have successfully created the first part, whereby I establish the correct code... =DLookUp("[gr_brand_ref]","tbl_ref_gear","[gr_ref]=Forms![frm_data_act_gear_sub]![act_hdr_gear_ftw_ref]")
    What then needs to happen is to look for the result in another table [tbl_ref_msc_cmp] and within that table link to the field [msc_cmp_ref] the linkage should then deliver the string from the field [msc_cmp_title]

    I have tried numerous permutations which have either returned a null value or an error.



    I know it is likely to fairly straightforward but it has now reached the point where I can't see the wood for the trees!

    Many thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Post sample data. If you want to provide db for analysis, follow instructions at bottom of my post. Assuming [msc_cmp_ref] is a text type field:

    =DLookup("[msc_cmp_title]", "[tbl_ref_msc_cmp]", "[msc_cmp_ref]='" & textboxname & "'")


    Last edited by June7; 09-24-2019 at 03:50 AM.
    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
    cap0557 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2019
    Posts
    6
    Thanks for responding and providing an answer. One of my attempts I had something similar;

    DLookup("[msc_cmp_title]", "[tbl_ref_msc_cmp]", "[msc_cmp_ref]='' DLookUp("[gr_brand_ref]","tbl_ref_gear","[gr_ref]=Forms![frm_data_act_gear_sub]![act_hdr_gear_ftw_ref]")).

    The reason have shown this is I don't have a separate text box from which I am deriving the initial reference number. How it works is that I have a drop down box from which the user can select the appropriate item. The selected item has its own unique text code (as there could be multiple incidents of the item) which is then posted to the field, as part of the item's dataset are other codes relating to a brand and brief description (note there are multiple brands and descriptions hence the separate db [tbl_ref_msc_cmp]). What I am attempting to do is when the user selects the item from the drop down rather than just show the reference I have a separate text field which then displays the brand and description for memo purposes.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Sorry, not following. Are you saying [act_hdr_gear_ftw_ref] is a combobox?

    Might not be able to resolve this without seeing db.



    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.

  5. #5
    cap0557 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2019
    Posts
    6
    Cheers for persevering. [act_hdr_gear_ftw_ref] is a combo box. I have a seperate field in which I have the DLookup function I posted in the first thread. Once I select an item from the drop down
    [act_hdr_gear_ftw_ref] the separate field is populated with a "brand code". What I want to be able to do is then use that code to look at [tbl_ref_msc_cmp] and return the brand name [msc_cmp_title]. I want to achieve this if I can by using a formula that takes the first result and then reads it against the second table ([tbl_ref_msc_cmp]) to provide the memo text.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Sorry, I am lost. Don't understand why what I suggested won't work.

    Post db.
    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.

  7. #7
    cap0557 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2019
    Posts
    6
    Hi,

    I looked at your suggestion and the piece that has me concerned is the latter part of the statement,
    & textboxname & "'"). What this suggests to me is point at a text box which already has the code in it and then apply that to the remainder of your DLookup solution, I do not have a separate textbox. In order to prove it I created a second text box and as its data source used
    =DLookUp("[gr_brand_ref]","tbl_ref_gear","[gr_ref]=Forms![frm_data_act_gear_sub]![act_hdr_gear_ftw_ref]"). This creates the reference number. I then applied your full formula and replace the textboxname with the actual name and inserted that into the original textbox. The result is, it works. However this isn't what I wanted to do. I was hoping the DLookup function could be nested so that the first part finds the code and the second DLookup uses the result and I don't need to run the function in two parts.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Nesting is tricky but should be possible, try:
    =DLookup("[msc_cmp_title]", " [tbl_ref_msc_cmp]", " [msc_cmp_ref] ='" & DLookUp("[gr_brand_ref]", "tbl_ref_gear", "[gr_ref]='" & [act_hdr_gear_ftw_ref] & "'") & "'")

    2 textboxes should work as well.

    tbxFirst:
    =DLookUp("[gr_brand_ref]", "tbl_ref_gear", "[gr_ref]='" & [act_hdr_gear_ftw_ref] & "'")

    tbxSecond:
    =DLookup("[msc_cmp_title]", " [tbl_ref_msc_cmp]", " [msc_cmp_ref] ='" & tbxFirst & "'")


    If criteria fields are number type, remove apostrophe delimiters.
    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.

  9. #9
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Not sure if this is relevant to your issue but could your combo box be based on a query that has all the records/results linked already? Then once they select the right combo box code or ref, then just use me.Combobox.column(2) or whatever to reference that last value you need? Make the field length for those other fields 0 to not display them in the combobox itself. Just a thought...

  10. #10
    cap0557 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2019
    Posts
    6
    Thanks June7, went with the nested DLookup and it worked first time! Already had the two text box solution working but the nested route keeps it a little "cleaner". Thanks for your support!

  11. #11
    cap0557 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2019
    Posts
    6
    Bulzie. thanks for the post. Your was the original solution I had running but it failed as the drop down list is ever only a subset of the data. over a given period of time items will drop-off and be added to the list. As such the items that do not meet the drop-down selection criteria will not display and hence the codes and descriptors cannot be found. Going the more difficult way maintains integrity throughout.

    Cheers

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

Similar Threads

  1. DLookup Nested ?
    By d9pierce1 in forum Programming
    Replies: 2
    Last Post: 04-06-2019, 06:16 PM
  2. Can't make a Dlookup work - please help !
    By mermaidboy in forum Access
    Replies: 3
    Last Post: 11-24-2017, 12:54 AM
  3. Nested DLookup
    By jamarogers in forum Programming
    Replies: 8
    Last Post: 10-13-2013, 12:20 AM
  4. Replies: 0
    Last Post: 06-29-2010, 12:04 PM
  5. Nested IIf query will not work
    By ddog171 in forum Queries
    Replies: 1
    Last Post: 06-20-2006, 02:03 PM

Tags for this Thread

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