Hi there

I am working in Access 2000 and am fairly experienced with Access, but self taught so have limited scope of experience.

I have a table called Leases with a field for RSM_ID.

I have a lookup table called tbl_RSM_Lkp which contains the fields RSM_ID, RSM_FirstName, RSM_LastName, RSM_CellPhone, etc.
In this table, RSM_ID is an Autonumber and is not set as a primary key (maybe it should be?)

I have a form based on the table Leases.
On this form I have fields for the RSM name, cell phone, etc.
I put the field RSM_ID as a Combo Box that displays the First name and Last name of the RSM by basing the field on an embedded query with one field that reads:
Name: [FirstName] & " " & [LastName]
From the Combo Box on the form, I see a pull down list of my RSMs and I can select one and it displays as their name.

I have an adjacent text field in which I am trying to insert a Dlookup function to display the cell phone number of the selected RSM, but I can’t make it work. I think I’ve done this before, but it’s not something I’ve done much or in recent years.

I’ve tried many variations of this:
=DLookUp("[RSM_CellPhone]", " tbl_RSM_Lkp", "[RSM_ID] = Forms![RSM_ID]”)

without success.
I think I’m having trouble with the criteria component.
Can you help?

I’d prefer to base this form on a query which would include both tables, but this is someone else’s database and the form is very complicated and I don’t want to have to rebuild/rename each field, which I think I would need to do . . .

Thanks for any help,
Laurie

---

Answer from Richard Rost:

Yes, I believe your criteria is your problem. First, you're completely missing your FORM NAME, and second you should put the VALUE of the field (not the field name itself) in the criteria, by placing it outside of the quotes. Try this:

=DLookUp("[RSM_CellPhone]", " tbl_RSM_Lkp", "[RSM_ID]=" & Forms!FormName![RSM_ID])

Obviously replace "FormName" with the actual name of your form (use square brackets if you have spaces in it).

See this tutorial for more help:

You can use the DLOOKUP function to look up a specific value from a table or query.

Please see the following FREE video tutorial on my web site for step-by-step instructions on how to use DLOOKUP:

http://599cd.com/tips/access/dlookup-function?key=BlFAlEx