DLookup returns same name

  • Thread starter Thread starter Wordwonderor
  • Start date Start date
W

Wordwonderor

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.
 
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.
Click to expand...

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.
 
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 said:
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.
Click to expand...

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.
Click to expand...
 
I am not sure what you mean. I scroll throught the conrtact forms and the
Solicitor ID field updates as I do but the name (Dlookup value) is always
that of Solicitor ID 1.

KARL DEWEY said:
I do believe you must requery when you change the solicitor's ID is on the
form.

--
Build a little, test a little.


Wordwonderor said:
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.
Click to expand...
Click to expand...
 
Didn't work. Yes, the Solicitor ID in the Soliciotr table is an auto number
and key field. In the contacts table it is a number field (indexed but allow
duplicates).

In the contacts table from which the Solicitor ID on the form originates,
there are more than one record with the same Soliciotr ID. In the Solcitor
table, there is only one distinct ID per record. Is there a problem with this.

BruceM via AccessMonster.com said:
=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.

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.
Click to expand...
Click to expand...
 
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.
Click to expand...

Please post the SQL view of the form's Recordsource, and that of the combo
box's Rowsource. Also indicate the datatype of the SolicitorID.

I'd be inclined just to have a combo box bound to SolicitorID but displaying
the solicitor name, rather than having any DLookUp or separate control; is
there some reason you have chosen not to do so?
 
I've changed to a combo box. It seems to work. But I'd like to understand why
the DLook up didn't, as that approach sem to be more direct.

The Solicitor ID is a number field in the form's record source. It is tied
to the Contacts ID.

BruceM via AccessMonster.com said:
Actually, I realize now that you did mention this, but I misunderstood the
problem. Is SolicitorID a number field? Is the Solicitor text box bound to
LName in the form's Record Source?
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.
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
Click to expand...
[quoted text clipped - 26 lines]
This does assume that there is a control named [Soliciter ID] on the form,
which contains the desired ID.
Click to expand...
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
I used the combo box but not sure how it works. I'd still like to know why
the DLookup which seems more direct didn't.

The forms record source is simply the Contacts table. The Solicior ID is a
number field in the Contacts Table and a autonumber in the Solicitors table.
 
On Mon, 24 May 2010 08:19:01 -0700, Wordwonderor

I'll be glad to try to help, but you'll need to help me do so: plsese post
your form's Recordsource, the combo box's Rowsource, and the datatype of the
solicoitor ID. I can't see them from here, and only that information will
enable me to reply.
 
Back
Top