Results 1 to 6 of 6
  1. #1
    powermixx is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    2

    Dlookup on a form to get values from a table

    Hello, first time post. I'm trying to get a value from another table to appear in a form:


    TABLES:
    MainTable
    TBL_Engineers


    FORM:
    FORM_MainTable


    I can't figure out the syntax for Dlookup. I want to be able to select a name from a list on the FORM_MainTable, then have the form populate with the persons phone number and email address (from the TBL_Engineers).




    I have this so far:
    =DLookUp("Eng_Phone","TBL_Engineers","Eng_ID='" & "Eng_Combo" & "'")

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hmmm?? I'm not following your FORM_MainTable???

    Please explain or describe with an example or 2.

    Forms typically have a recordsource (the table from which values are displayed in the form).
    This may just be a terminology issue.

    =DLookUp("Eng_Phone","TBL_Engineers","Eng_ID='" & "Eng_Combo" & "'")

    You may need to adjust (remove the single quotes) if Eng_ID is numeric
    =DLookUp("Eng_Phone","TBL_Engineers","Eng_ID='" & "Eng_Combo" & "'")

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't quote references or variables, otherwise what you're passing to the function as criteria is literally "Eng_ID" and not the value that Eng_ID contains. Try
    =DLookUp("Eng_Phone","TBL_Engineers","Eng_ID='" & Eng_Combo & "'") and that presumes that the criteria is text. If the bound column of the combo is a number, don't enclose it in quotes of any kind.

    I guess the table in question isn't part of the form recordsource. Should it be?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    powermixx is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    2
    Wow, quick responses. THANK YOU! It's been 15 years or more since my VB experience. Much to catch up on! Maybe my question should be: What's the best method to achieve my goal: To be able to select a persons name from a combo box, then have their related phone number and email populated on the form, just for reference - not needed to be stored in another field. I hope that makes sense. Thank you so much!

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Need more info about the form(s) involved -single? Main form/subform? If this is just to show user details for one user on a single record form, then you could use the result of the combo selection and apply a filter to the form.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The easiest would be for you to add the phone number and email fields to the combo's row source (("Select EngID,EngName,Eng_Phone,Eng_EMail From Tbl_Engineers;"), modify the combo to have Column Count=4,Column Widths = 0",3",1",1" (or make the last two 0 to hide them) then for the text boxes where you want to display the phone and email replace the current dlookups with =[cboEng].Column(2) for phone and =[cboEng].Column(3) for email.
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 2
    Last Post: 12-08-2014, 08:55 AM
  2. Replies: 5
    Last Post: 11-17-2013, 04:20 PM
  3. Replies: 5
    Last Post: 12-21-2011, 07:16 PM
  4. Replies: 1
    Last Post: 08-13-2011, 12:03 AM
  5. How do I get dlookup values to store in table
    By rpmyhero in forum Access
    Replies: 1
    Last Post: 11-25-2009, 05:57 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