Link to home
Start Free TrialLog in
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!
Avatar of mbizup
mbizup
Flag of Kazakhstan image

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)
The above control source is looking up the employees address.  If you need it to lookup the name, use EmployeeName instead of Address
Avatar of JoeBarbone
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 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
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<<
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" )
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
Lol!  Like this?

User generated image

Glad to help out :-)