Lookup Question for Form Entry

[dmills]

I am currently using a form to enter data - this form includes NDC (National
Drug Code) and Drug Names. When entering a certain number (NDC) onexit of
that field I want to do a lookup to a currently designed query that has a
match on ndc and returns name of drug amongst other fields... I want to pull
a field (drugname) from that data when the (NDC) codes match between the
current entry and the query so that I can use that field in the data form so
it can show the drug name for the current ndc code...the current way I had it
designed is like this:

Dim varX As Variant
varX = DLookup("[rosi.ndc]", [Match For Drug And Manufac Name.NDC], "[Match
For Drug And Manufac Name.name] = 1")

rosi is the name of the table I designed for data entry
Match For Drug And Manufac Name is a query I designed
Name is the name of the drug that I want returned so I can display it in the
data form

could someone help me with this syntax...

 

[John Spencer MVP]

DLookup has three arguments and they should all be strings. The last argument
is optional.

DLookup("SOME FIELD NAME","SOME TABLE OR QUERY NAME","A WHERE CLAUSE")

So you might want something like the following to get the DrugName.
Assumptions:
Table Name is Rosi.ndc
Field name in table is DrugName
NDC code field in Rosi.ndc is named NDC
A control on the form is named NDCCODEControl and contains the NDC code you
want to look up to find the Drug Name.

DLookup("DrugName","[Rosi.ndc]","NDC='" & Me.NDCCODEControl & "'")

Try looking in VBA help for further explanation on how DLookup works.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County