Prev: Need a button to print 5 copies
Next: Sending a link for a form to the desktop - Access 2007
From: Wordwonderor on 12 May 2010 11:18
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.
From: KARL DEWEY on 12 May 2010 12:26
I do believe you must requery when you change the solicitor's ID is on the
form.

--
Build a little, test a little.


"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.
From: BruceM via AccessMonster.com on 12 May 2010 13:07
=DLookUp("[Lname]","[Solicitor]","[SolicitorID] = " & [SolicitorID])

This assumes SolicitorID is a number field, that it is a field in the record
source for the Contacts form, and that it is also a field in the Solicitor
table. The expression is saying "Look up LName from a record in the
Solicitor table in which SolicitorID matches SolicitorID on the current form".


Note that if more than one record matches the criteria Access will return
only the first matching value.


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.

--
Message posted via http://www.accessmonster.com

From: John W. Vinson on 12 May 2010 13:38
On Wed, 12 May 2010 08:18:01 -0700, Wordwonderor
<Wordwonderor(a)discussions.microsoft.com> 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]
From: Wordwonderor on 14 May 2010 11:42
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
> <Wordwonderor(a)discussions.microsoft.com> 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]
> .
>
 |  Next  |  Last
Pages: 1 2 3
Prev: Need a button to print 5 copies
Next: Sending a link for a form to the desktop - Access 2007