DLookup returns same name
Thank you, but it didn't help. The Solicitor ID control is on the form and it
updates as I scroll though the Contacts. But the Solicitor's name field (the
dlookup) doesn't. It always shows the solicitor with Solicitor ID 1.
"John W. Vinson" wrote:
On Wed, 12 May 2010 08:18:01 -0700, Wordwonderor
wrote:
In an Access 2003 database, I have a form, Contacts, based on a Contacts
table that includes potential donors and the Solicitor ID of their assigned
solicitor.
I also have a Contacts form for displaying the contact information. On the
form, I would like to display the solicitor's name from the table called
Solicitor, where the Solicitor ID is the key field. To do this I have a text
box on the Contacts form with the following control:
=DLookUp("[Lname]","[Solicitor]","[Solicitor ID]=Forms![Contacts]![Solicitor
ID]")
But, when run, the name of only one solicitor is displayed regardless of
which solicitor's ID is on the form. I've tried using the " & format after
the = sign in the formula and using just [Solicitor ID]. I still get the same
name and, in addition, a #name error when a record has no solcitor ID. The
name I get is that of Solicitor ID 1.
Help, please.
Try pulling the forms reference out of the text string:
=DLookUp("[Lname]","[Solicitor]","[Solicitor ID]=" &
Forms![Contacts]![Solicitor ID])
or (since you're on the form already) just
=DLookUp("[Lname]","[Solicitor]","[Solicitor ID]=" & [Solicitor ID])
This does assume that there is a control named [Soliciter ID] on the form,
which contains the desired ID.
--
John W. Vinson [MVP]
.
|