Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an access database that i have gracefully been deemed responsible for.. I for the life of me cannot figure out how to do this simple relationship scenario..

Call Table

id - PKey Auto Inc
employee_id - Text -> links to UserInfo Linked SharePoint List Table

User Info Linked SharePoint Table

This table is readonly, a linked table from a SharePoint list..

id - PKey Auto Inc
Name - Full name of employee

On the input form the call center tech will fill the form out, and enter the employee's ID number. Once they enter that i want it to show the employees name from the [UserInfo].[Name] column in a textbox..

I tried using a DLookup function like so:

=DLookUp("[Name]","[UserInfo]"," [UserInfo]![Employee ID] =" & [Forms]![Call Entry]![employee_number])

but all i'm getting is #Error. the form's data source is set to the Call Table.

enter image description here

share|improve this question
Why not use a combobox so they when they type in their employee number the name is also right there. – wbeard52 Jul 31 '12 at 16:25
becuase i dont want to store the name, i only want to store their id # :) – gorelative Jul 31 '12 at 16:34
You won't be storing the name. The combo box will pull it into the form, but only the ID will be stored. – user3463 Jul 31 '12 at 16:53
then provide the answer on how to do it.. – gorelative Jul 31 '12 at 16:55
Why don't you fire some little vbscript on the on-change event? At least back in 2001 or so that worked. – hakre Aug 1 '12 at 14:18
up vote 2 down vote accepted

Look up the CompanyName field from table Company, where CompanyID = 874. This translates to:

=DLookup("CompanyName", "Company", "CompanyID = 874")

You don't want Company 874 printed for every record! Use an ampersand (&) to concatenate the current value in the CompanyID field of your report to the "Company = " criteria:

=DLookup("CompanyName", "Company", "CompanyID = " & [CompanyID])

If the CompanyID is null (as it might be at a new record), the 3rd agumenent will be incomplete, so the entire expression yields #Error. To avoid that use Nz() to supply a value for when the field is null:

=DLookup("CompanyName", "Company", "CompanyID = " & Nz([CompanyID],0))
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .