Dlookup a number from another table to appear in another form

N

Neil

I have a table (Project information sheet) where i have a column for
ProjectID (autonumber,unique) and QAT Number (text). I created a form
Customer Enquiry List wherein there is an unbound text box that should fill
in automatically the projectID if a fillup the QAT Number. I use the
following formula in the control source:

=DLookUp("[productid]","[project information sheet]","[productid]='" & [qat
number] & "")

however, only an error message appears.

Please help me. Thanks.
 
B

Beetle

There are a couple of problems with your DLookup.

First, ProductID is an Autonumber data type, but you are attempting
to delimit it as a string. In addition to that, you are then attempting to
compare this improperly delimited number to a string ([QATNumber]).

Your DLookup should probably look like;

DLookup("[ProductID]", "[Project Information Sheet]", _
"[QATNumber] = """ & [QATNumber] & """")

Further, just as a point of information, it is usually not a good idea
to use Autonumber on fields that are intended to have some
identifiable meaning to the users. Autonumber is only guaranteed to
be unique. It is not guaranteed to be consecutive, or even positive for
that matter. You will end up with gaps in the sequence that probably
won't make sense to the users.
 
N

Neil

Thanks for the reply.

I tried the formula given below and typed it in the control source of a text
box. However, it only shows an error message that is blinking continously

Beetle said:
There are a couple of problems with your DLookup.

First, ProductID is an Autonumber data type, but you are attempting
to delimit it as a string. In addition to that, you are then attempting to
compare this improperly delimited number to a string ([QATNumber]).

Your DLookup should probably look like;

DLookup("[ProductID]", "[Project Information Sheet]", _
"[QATNumber] = """ & [QATNumber] & """")

Further, just as a point of information, it is usually not a good idea
to use Autonumber on fields that are intended to have some
identifiable meaning to the users. Autonumber is only guaranteed to
be unique. It is not guaranteed to be consecutive, or even positive for
that matter. You will end up with gaps in the sequence that probably
won't make sense to the users.
--
_________

Sean Bailey


Neil said:
I have a table (Project information sheet) where i have a column for
ProjectID (autonumber,unique) and QAT Number (text). I created a form
Customer Enquiry List wherein there is an unbound text box that should fill
in automatically the projectID if a fillup the QAT Number. I use the
following formula in the control source:

=DLookUp("[productid]","[project information sheet]","[productid]='" & [qat
number] & "")

however, only an error message appears.

Please help me. Thanks.
 
S

stathis

Dllokup has three parts ("FOR";"FROM";"WHERE") : Look FOR (ProjectID), FROM
(Project information sheet) WHERE (condition). So, your expression has to be

Dlookup("ProjectID";"[project information sheet]";"[QAT Number]=Forms!
[Customer Enquiry List]![QAT Number]")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top