Avatar of JoeBarbone
JoeBarbone
 asked on

lookup data from another table in Access 2010

Hello everyone,

I have two tables, Employees and TableB. Employees contains EmployeeID and EmployeeName, TableB contains their address.

I have a form in TableB that holds the employees address,  and on the top of the form, I'd like to be able to enter the EmployeeID into the field and have it find the EmployeeName and display it in an unbound field on the TableB form. I know this is easy, but for the love of me, I cannot figure it out.

The example above is only an example of what I am trying to do, it is not the exact scenario so please do not ask why am I keeping that data in two tables, etc. The concept I am looking for is simple, enter EmployeeID in a field and have it retrieve the EmployeeName from the Employees table and display it in an unbound field.

Thanks!

Joe
Thanks!
Microsoft Access

Avatar of undefined
Last Comment
mbizup

8/22/2022 - Mon
mbizup

Use this as the control source to your unbound field (include the =)


= DLookup("address", "TableB", "EmployeeID = " & txtEmployeeID)

Open in new window


That assumes employee ID is numeric.  FOr text:

= DLookup("address", "TableB", "EmployeeID = '" & txtEmployeeID & "'")

Open in new window



(txtEmployeeID is the control where you are entering the employeeID)
mbizup

The above control source is looking up the employees address.  If you need it to lookup the name, use EmployeeName instead of Address
JoeBarbone

ASKER
So it's =DLookup("the data I want to lookup", "the form I want the data to be displayed on", "the field I want the search to be performed on" and "the field name that I am typing the search item into" in this case, it woudl be 2279 because that is the employee number.

Is this correct?

Thanks for your quick reply.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
JoeBarbone

ASKER
This is what I have but it's giving me an #error in the unbound field when I type the EmployeeID field.

=DLookUp("EmployeeName", "frmQA032", "EmployeeID = " & [EmployeeID])

Any idea?
ASKER CERTIFIED SOLUTION
mbizup

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mbizup

<<
So it's =DLookup("the data I want to lookup", "the form I want the data to be displayed on", "the field I want the search to be performed on" and "the field name that I am typing the search item into" in this case, it woudl be 2279 because that is the employee number.
>>

Your explanation is almost there, but not quite

DLookup("the data I want to lookup", "The table/query that contains the data you are looking up", "The criteria you are using to isolate the value needed" )
JoeBarbone

ASKER
Of course it is, I'm just a donut head.

And, it works when you give it the right syntax. Thank you for your patience.

Joe
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mbizup

Lol!  Like this?

Homer Simpson

Glad to help out :-)