Dlookup returns same value every time

  • Thread starter Thread starter T Smith
  • Start date Start date
T

T Smith

I have two tables-key log and vendor. The vendor table consists of two
columns: vendor and vendor phone number. Vendor is the primary key. I have a
form based on key log that has fields: vendor name and vendor phone number.
I am trying to use Dlookup to lookup the vendor phone number from the vendor
table based on the entry in the vendor name field. (the vendor name field is
a list box which pulls it's data from the vendor name column of the vendor
table). There is a requery macro that runs when a change or update is made
to the vendor name field but regardless of what vendor is selected in the
name field the same vendor phone number is returned each time (the weird
thing is that the phone number returned is the second number in the column
not the first). I even tired playing with Dlookup in the immediate window
and had the same results-what am I doing wrong?

Any help is appreciated. Thank you.
 
Can you post more info about your table's contents, and post the DLookup
function call that you're actually using?

If you have more than one record in the table that meets the
criterion/criteria that you specify, DLookup will return the first record
that it finds, not all of them.
 
Two Tables: key log and vendor.

Table!vendor contains only two fields: "vendor name-(primary key-no
duplicates)" and "vendor phone number" table!vendor is designed as a data
source for two fields labeled "vendor name" and "vendor phone number"
contained in a form named form!entry log based on table!keylog. The
objective is to create a new
record in table!keylog using the form. The user inputs the appropriate
other data and then selects the "vendor name" from a list box on the form
(this list box draws the vendor names from table!vendor!vendor name). Once
the user has selected a "vendor name" I have a requery macro that is
supposed to requery the Dlookup function in the next field "vendor
phone-(unbound text box)" to pull in the correct matching phone number if
one exists in table!vendor!vendor name that matches the "vendor name" At
this point if no phone number is returned then the user has an opportunity
to input one. After the "vendor phone" field is updated I was going to have
another macro write the entry to table!keylog.

Essentially I want to form the fill in the phone number for a vendor if it
exists, if it doesn't exist then I want the user to be able to type in the
phone number and have the form save it along with the new record in
table!keylog.

The Dlookup call I have been playing with is:
=DLookUp("[vendor phone number]","vendor","'[vendor name]=forms![entry
log]![vendor name]'")

There may be a mistake or two in syntax since I am new to Dlookup and have
played around with this call numerous times.

Thanks for the help.
 
To answer your DLookUp question, try this:

=DLookUp("[vendor phone number]","vendor","[vendor name]= '" & [vendor name]
& "'")

If the control [Vendor Name] used in the Where clause is on the same form as
the control using the DLookUp, you do not need to use the forms!FormName
syntax.
Note also the placement of the single and double quotes in the Where clause.

I notice you are using [Vendor Name] as a prime key.
I'm assuming in the above that [Vendor Name] is a text datatype, not a
Number Datatype.

If it is a Number datatype, then use:
"[vendor name]= " & [vendor name] )

However, you already have a ListBox that shows the names of the Vendors.
Why not just add the Phone Number field to the ListBox SQL and show it
alongside the Vendor Name.
Joe Smith & Co (213) 123-4567

If you still want a separate control to display the phone number,
then instead of DLookUp, set the unbound control's control source to:
=ListBoxName.Column(1)
if the phone number is the 2nd column.

It's simple enough then to open the Vendor form
and add the phone number if it doesn't exist.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


T Smith said:
Two Tables: key log and vendor.

Table!vendor contains only two fields: "vendor name-(primary key-no
duplicates)" and "vendor phone number" table!vendor is designed as a data
source for two fields labeled "vendor name" and "vendor phone number"
contained in a form named form!entry log based on table!keylog. The
objective is to create a new
record in table!keylog using the form. The user inputs the appropriate
other data and then selects the "vendor name" from a list box on the form
(this list box draws the vendor names from table!vendor!vendor name). Once
the user has selected a "vendor name" I have a requery macro that is
supposed to requery the Dlookup function in the next field "vendor
phone-(unbound text box)" to pull in the correct matching phone number if
one exists in table!vendor!vendor name that matches the "vendor name" At
this point if no phone number is returned then the user has an opportunity
to input one. After the "vendor phone" field is updated I was going to have
another macro write the entry to table!keylog.

Essentially I want to form the fill in the phone number for a vendor if it
exists, if it doesn't exist then I want the user to be able to type in the
phone number and have the form save it along with the new record in
table!keylog.

The Dlookup call I have been playing with is:
=DLookUp("[vendor phone number]","vendor","'[vendor name]=forms![entry
log]![vendor name]'")

There may be a mistake or two in syntax since I am new to Dlookup and have
played around with this call numerous times.

Thanks for the help.
Click to expand...
 
Thank you for the suggestions. I tired: =DLookUp("[vendor phone
number]","vendor","[vendor name]= '" & [vendor name]
& "'") with the same result-it only returns one phone number.

In addition, if I use the above call in the immediate window it returns an
error saying "compile error, expected list separator or )"

I know how I am doing the phone number is a little cumbersome but I wanted
it to be as simple as possible for the users who have no Access experience
at all.

What am I doing wrong?

Thanks for the help.
 
DLookup and other domain functions are a bit different in how you are to use
the [ ] notation. For the field name (argument 1) and the table/query name
(argument 2), do not use [ ] even if there are blanks in the names:

=DLookUp("vendor phone number","vendor","[vendor name]= '" & [vendor name] &
"'")

Other than this, have you tried using your DLookup function in the Immediate
Window of the Visual Basic Editor to see if it returns the correct value
when you use an actual "vendor name" value?

=DLookUp("vendor phone number","vendor","[vendor name]= 'ActualVendorName'")
 
Thanks Fredg. I was having the same problem. Your help here cleaned
everything up.

Fredg said:
To answer your DLookUp question, try this:

=DLookUp("[vendor phone number]","vendor","[vendor name]= '" & [vendor name]
& "'")

If the control [Vendor Name] used in the Where clause is on the same form as
the control using the DLookUp, you do not need to use the forms!FormName
syntax.
Note also the placement of the single and double quotes in the Where clause.

I notice you are using [Vendor Name] as a prime key.
I'm assuming in the above that [Vendor Name] is a text datatype, not a
Number Datatype.

If it is a Number datatype, then use:
"[vendor name]= " & [vendor name] )

However, you already have a ListBox that shows the names of the Vendors.
Why not just add the Phone Number field to the ListBox SQL and show it
alongside the Vendor Name.
Joe Smith & Co (213) 123-4567

If you still want a separate control to display the phone number,
then instead of DLookUp, set the unbound control's control source to:
=ListBoxName.Column(1)
if the phone number is the 2nd column.

It's simple enough then to open the Vendor form
and add the phone number if it doesn't exist.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


T Smith said:
Two Tables: key log and vendor.

Table!vendor contains only two fields: "vendor name-(primary key-no
duplicates)" and "vendor phone number" table!vendor is designed as a data
source for two fields labeled "vendor name" and "vendor phone number"
contained in a form named form!entry log based on table!keylog. The
objective is to create a new
record in table!keylog using the form. The user inputs the appropriate
other data and then selects the "vendor name" from a list box on the form
(this list box draws the vendor names from table!vendor!vendor name). Once
the user has selected a "vendor name" I have a requery macro that is
supposed to requery the Dlookup function in the next field "vendor
phone-(unbound text box)" to pull in the correct matching phone number if
one exists in table!vendor!vendor name that matches the "vendor name" At
this point if no phone number is returned then the user has an opportunity
to input one. After the "vendor phone" field is updated I was going to have
another macro write the entry to table!keylog.

Essentially I want to form the fill in the phone number for a vendor if it
exists, if it doesn't exist then I want the user to be able to type in the
phone number and have the form save it along with the new record in
table!keylog.

The Dlookup call I have been playing with is:
=DLookUp("[vendor phone number]","vendor","'[vendor name]=forms![entry
log]![vendor name]'")

There may be a mistake or two in syntax since I am new to Dlookup and have
played around with this call numerous times.

Thanks for the help.
Click to expand...
Click to expand...
 
And it just gets stranger. Please excuse a novice's troubles but this is
what has happened so far when using the immediate window.

DLookUp("[vendor phone number]","vendor") produces a phone number form the
list but it is not the first number in the list (list being tables!vendor).

DLookUp("vendor phone number","vendor") produces a syntax error.

DLookUp("[vendor phone number]", "vendor", "'Atlantic Appliance'") produces
the same "wrong" phone number as the first example.

DLookUp("vendor phone number", "vendor", "'Atlantic Appliance'") produces a
syntax error.

DLookUp("[vendor phone number]", "vendor", "[vendor name] = Form![vendor
name]") produces a error message which says "run-time error 2001. You
cancelled the previous operation" what the heck does that mean?

DLookUp("[vendor phone number]", "vendor", "[vendor name] = " & forms![entry
log]![vendor name]) produces a syntax error.

DLookUp("[vendor phone number]", "vendor", "[vendor name] = '" &
forms![entry log]![vendor name] & "'") again produces the "you cancelled the
previous operation" error message.

DLookUp("vendor phone number", "vendor", "[vendor name] = '" & forms![entry
log]![vendor name] & "'") produces a syntax error.

As you can see, I have played around with this a lot and can not get it to
work. Any ideas, I am very frustrated.

Thank you.
 
Comments inline.....

--
Ken Snell
<MS ACCESS MVP>

T Smith said:
And it just gets stranger. Please excuse a novice's troubles but this is
what has happened so far when using the immediate window.

DLookUp("[vendor phone number]","vendor") produces a phone number form the
list but it is not the first number in the list (list being
Click to expand...
tables!vendor).

The above expression will return the value of the vendor phone number that
is in the first record found. Note that a table has NO order, and therefore
the first record found by DLookup most likely will not be the "first" record
that you see when you open the table. Without the third argument in the
DLookup function, there is no "filtering" being done to find the desired
value based on the value of a primary key field.
DLookUp("vendor phone number","vendor") produces a syntax error.
Click to expand...

This is because you need the [ ] in the first argument ("[vendor phone
number]") because of the spaces.
DLookUp("[vendor phone number]", "vendor", "'Atlantic Appliance'") produces
the same "wrong" phone number as the first example.
Click to expand...

Same explanation as for the first example. Additionally, in this case, your
third argument is incomplete, as you're not telling DLookup which field is
supposed to have the value of "Atlantic Appliance". The way you've written
the third argument, the DLookup function is seeing "Atlantic Applicance" as
a value that is valid for all records.
DLookUp("vendor phone number", "vendor", "'Atlantic Appliance'") produces a
syntax error.
Click to expand...

Same explanations as for the second and third examples.
DLookUp("[vendor phone number]", "vendor", "[vendor name] = Form![vendor
name]") produces a error message which says "run-time error 2001. You
cancelled the previous operation" what the heck does that mean?
Click to expand...

The error that you're getting is because you didn't put the Form![vendor
name] outside the " marks. Plus it's Forms, not Form. Also, you need to
delimit the string from the form's control with ' marks. And, you need to
include the name of a control in the forms reference (you must tell DLookup
which control has the value to be used):
DLookUp("[vendor phone number]", "vendor", "[vendor name] = '" &
Forms![vendor name]![Control Name] & "'")
DLookUp("[vendor phone number]", "vendor", "[vendor name] = " & forms![entry
log]![vendor name]) produces a syntax error.
Click to expand...

You need to delimit value from the form's control with ' marks:
DLookUp("[vendor phone number]", "vendor", "[vendor name] = '" &
forms![entry log]![vendor name] & "'")
DLookUp("[vendor phone number]", "vendor", "[vendor name] = '" &
forms![entry log]![vendor name] & "'") again produces the "you cancelled the
previous operation" error message.
Click to expand...

Was the form "entry log" open when you tried running the DLookup function in
the Immediate Window? It must be open when you run it.
DLookUp("vendor phone number", "vendor", "[vendor name] = '" & forms![entry
log]![vendor name] & "'") produces a syntax error.
Click to expand...

See answer for second example.


Here is what you use (and entry log form must be open!):
DLookUp("[vendor phone number]", "vendor", "[vendor name] = '" &
forms![entry log]![vendor name] & "'")
 
Thank you for taking the time to explain the differences. I still am up
against the same wall though.

I put the following call in the immediate window and still got the "run-time
error 2001. You cancelled the previous operation" message when the entry log
form was open.

? DLookUp("[vendor phone number]", "vendor", "[vendor name] = '" &
forms![entry log]![vendor name] & "'")


What is that and why does it keep coming up-I can not find anything about
this run-time error in support. HELP! is it me?
 
Only thing I can tell you is that most likely one (or more) of the names in
the function's arguments is not matching the actual name of that
field/form/table.

Is there a "vendor name" control on the form?
 
Yes, "vendor name" is a name of a control on the form-it is a drop down list
box which uses table![vendor]![vendor] as its' data source.
 
If you can zip up your database (< 1MB) and email it to me (remove this is
not real from my email address), I'll take a look and see what may be the
problem.
 
I finally got it after making a change to the field name. Thank you very
much for all your help.
 
Back
Top