If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DLookup returns same name
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. |
#2
|
|||
|
|||
DLookup returns same name
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. |
#3
|
|||
|
|||
DLookup returns same name
=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 |
#4
|
|||
|
|||
DLookup returns same name
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] |
#5
|
|||
|
|||
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] . |
#6
|
|||
|
|||
DLookup returns same name
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" wrote: 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. |
#7
|
|||
|
|||
DLookup returns same name
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" wrote: =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 . |
#8
|
|||
|
|||
DLookup returns same name
Is this something new? I don't recall you mentioning this problem. Is the
solicitor control (the text box or whatever that shows the solicitor name) bound to the LName field in the form's Record Source table or query? Wordwonderor wrote: 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 [quoted text clipped - 26 lines] This does assume that there is a control named [Soliciter ID] on the form, which contains the desired ID. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201005/1 |
#9
|
|||
|
|||
DLookup returns same name
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? Wordwonderor wrote: 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 [quoted text clipped - 26 lines] This does assume that there is a control named [Soliciter ID] on the form, which contains the desired ID. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201005/1 |
#10
|
|||
|
|||
DLookup returns same name
On Fri, 14 May 2010 08:42:01 -0700, Wordwonderor
wrote: 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. 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? -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|