lookup data from another table in Access 2010

JoeBarbone
JoeBarbone used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
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)
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

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

Author

Commented:
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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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?
Nerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
The second argument should be the name of the table/query containing the field you are looking up:

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

Open in new window

mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
<<
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" )

Author

Commented:
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
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Lol!  Like this?

Homer Simpson

Glad to help out :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial