Dlookup a number from another table to appear in another form

[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.

 

[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.

 

[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

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

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.

 

[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]")