Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
job_number
employee_id - Text -> links to UserInfo Linked SharePoint List Table
call_description

User Info Linked SharePoint Table

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

id - PKey Auto Inc
employee_id
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
1  
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

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.