Try:
Me.FN LN =DLookUp("[First Name] & ' ' & [Last Name]","tblStaffID", "Staff ID='" & Me.Staff ID & "'")
From my code repository:
Getting a value from a table: DLookup()
Sooner or later, you will need to retrieve a value stored in a table. If you regularly make write invoices to companies, you will have a Company table that contains all the company's details including a CompanyID field, and a Contract table that stores just the CompanyID to look up those details. Sometimes you can base your form or report on a query that contains all the additional tables. Other times, DLookup() will be a life-saver.
DLookup() expects you to give it three things inside the brackets. Think of them as:
Look up the _____ field, from the _____ table, where the record is _____
Each of these must go in quotes, separated by commas.
This is probably easiest to follow with some examples:
1. you have a CompanyID such as 874, and want to print the company name on a report;
2. you have Category such as "C", and need to show what this category means.
3. you have StudentID such as "JoneFr", and need the student’s full name on a form.
Example 1:
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])
Example 2:
The example above is correct if CompanyID is a number. But if the field is text, Access expects quote marks around it. In our second example, we look up the CategoryName field in table Cat, where Category = 'C'. This means the DLookup becomes:
=DLookup("CategoryName", "Cat", "Category = 'C'")
Single quotes within the double quotes is the easiest way to do quotes within quotes. But again, we don't want Category 'C' for all records: we need the current value from our Category field patched into the quote. To do this, we close the quotation after the first single quotemark, add the contents of Category, and then add the trailing single quotemark. This becomes:
=DLookup("CategoryName", "Cat", "Category = '" & [Category] & "'")
Example 3:
In our third example, we need the full name from a Student table. But the student table has the name split into FirstName and Surname fields, so we need to refer to them both and add a space between. To show this information on your form, add a textbox with ControlSource:
=DLookup("[FirstName] & ' ' & [Surname]", "Student", "StudentID = '" & [StudentID] & "'")
Dave
