DLookUp

C

Curt Hand

I am using Access 2003 and I have entered the folowing code in one of my
forms to retrieve data from a table.

LFTestNo = DLookup("LFTestNo", "tblLFTests", "TestNo='" & [TestNo] & "'")
LFTestType = DLookup("LFTestType", "tblLFTests", "TestNo='" & [TestNo] &
"'")
GCMethod = DLookup("GCMethod", "tblLFTests", "TestNo='" & [TestNo] & "'")
LFGCRunDate = DLookup("LFGCRunDate", "tblLFTests", "TestNo='" & [TestNo]
& "'")
LFExperimentInfo = DLookup("LFExperimentInfo", "tblLFTests", "TestNo='"
& [TestNo] & "'")
LFExperimentSampNo = DLookup("LFExperimentSampNo", "tblLFTests",
"TestNo='" & [TestNo] & "'")
LFLabIDNo = DLookup("LFLabIDNo", "tblLFTests", "TestNo='" & [TestNo] &
"'")
LFCount = DLookup("LFCount", "tblLFTests", "TestNo='" & [TestNo] & "'")
LFTimeCount = DLookup("LFTimeCount", "tblLFTests", "TestNo='" & [TestNo]
& "'")
LFResearchCount = DLookup("LFResearchCount", "tblLFTests", "TestNo='" &
[TestNo] & "'")
TSCount = DLookup("TSCount", "tblLFTests", "TestNo='" & [TestNo] & "'")
LFArea = DLookup("LFArea", "tblLFTests", "TestNo='" & [TestNo] & "'")
IntStdArea = DLookup("IntStdArea", "tblLFTests", "TestNo='" & [TestNo] &
"'")

When I tab through the form it only picks up the first line that corresponds
to the data I am looking for. The original information has many lines but
only certain lines have information that needs to be looked up and entered
into the form and stored in a different table.

I would appreciate any help on this matter. I have tried some if statements
but none that I tried worked.

Thanks,
Curt Hand
 
W

Wayne Morgan

What do you mean by "... it only picks up the first line..."? DLookup()
won't return multiple entries that fit the criteria, it will only return the
first match it finds.

What is it you're trying to accomplish? Where is the code (which procedure)?
 
S

stefan hoffmann

hi Curt,

Curt said:
I am using Access 2003 and I have entered the folowing code in one of my
forms to retrieve data from a table.

LFTestNo = DLookup("LFTestNo", "tblLFTests", "TestNo='" & [TestNo] & "'")
LFTestType = DLookup("LFTestType", "tblLFTests", "TestNo='" & [TestNo] &
"'")
GCMethod = DLookup("GCMethod", "tblLFTests", "TestNo='" & [TestNo] & "'")
LFGCRunDate = DLookup("LFGCRunDate", "tblLFTests", "TestNo='" & [TestNo]
& "'")
LFExperimentInfo = DLookup("LFExperimentInfo", "tblLFTests", "TestNo='"
& [TestNo] & "'")
LFExperimentSampNo = DLookup("LFExperimentSampNo", "tblLFTests",
"TestNo='" & [TestNo] & "'")
LFLabIDNo = DLookup("LFLabIDNo", "tblLFTests", "TestNo='" & [TestNo] &
"'")
LFCount = DLookup("LFCount", "tblLFTests", "TestNo='" & [TestNo] & "'")
LFTimeCount = DLookup("LFTimeCount", "tblLFTests", "TestNo='" & [TestNo]
& "'")
LFResearchCount = DLookup("LFResearchCount", "tblLFTests", "TestNo='" &
[TestNo] & "'")
TSCount = DLookup("TSCount", "tblLFTests", "TestNo='" & [TestNo] & "'")
LFArea = DLookup("LFArea", "tblLFTests", "TestNo='" & [TestNo] & "'")
IntStdArea = DLookup("IntStdArea", "tblLFTests", "TestNo='" & [TestNo] &
"'")
Click to expand...
Use the following code to avoid the DLookups and to speed up reading the
data:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblLFTests " & _
"WHERE TestNo = '" & [TestNo]& "'")
If Not rs.Bof And Not rs.Eof Then
LFTestNo = rs![LFTestNo]
LFTestType = rs![LFTestType]
GCMethod = rs![GCMethod] 'and so on...
End
rs.Close
Set rs = Nothing
When I tab through the form it only picks up the first line that corresponds
to the data I am looking for. The original information has many lines but
only certain lines have information that needs to be looked up and entered
into the form and stored in a different table.
Click to expand...
Is your form bound to a data source? If it is, then use the On Current
event to populate your fields with the code above.



mfG
--> stefan <--
 
K

Klatuu

The DLookup will only find the first occurance of a match in your criteria
argument. If there are multipe rows in your table with the same TestNo, it
will always return the first.

Also, your method here will also be very slow. You are hitting the database
13 times to return one row. The proper technique would be to create a query
that includes the fields you want in your form. Once you have selected a
TestNo, you will be able to navigate all the matching rows.
 
C

Curt Hand

Hi Stefan,

I am using the DLookUp on lost focus of a text box. Where do you suggest I
enter the code that you sent me.

Curt

stefan hoffmann said:
hi Curt,

Curt said:
I am using Access 2003 and I have entered the folowing code in one of my
forms to retrieve data from a table.

LFTestNo = DLookup("LFTestNo", "tblLFTests", "TestNo='" & [TestNo] & "'")
LFTestType = DLookup("LFTestType", "tblLFTests", "TestNo='" & [TestNo] &
"'")
GCMethod = DLookup("GCMethod", "tblLFTests", "TestNo='" & [TestNo] & "'")
LFGCRunDate = DLookup("LFGCRunDate", "tblLFTests", "TestNo='" & [TestNo]
& "'")
LFExperimentInfo = DLookup("LFExperimentInfo", "tblLFTests", "TestNo='"
& [TestNo] & "'")
LFExperimentSampNo = DLookup("LFExperimentSampNo", "tblLFTests",
"TestNo='" & [TestNo] & "'")
LFLabIDNo = DLookup("LFLabIDNo", "tblLFTests", "TestNo='" & [TestNo] &
"'")
LFCount = DLookup("LFCount", "tblLFTests", "TestNo='" & [TestNo] & "'")
LFTimeCount = DLookup("LFTimeCount", "tblLFTests", "TestNo='" & [TestNo]
& "'")
LFResearchCount = DLookup("LFResearchCount", "tblLFTests", "TestNo='" &
[TestNo] & "'")
TSCount = DLookup("TSCount", "tblLFTests", "TestNo='" & [TestNo] & "'")
LFArea = DLookup("LFArea", "tblLFTests", "TestNo='" & [TestNo] & "'")
IntStdArea = DLookup("IntStdArea", "tblLFTests", "TestNo='" & [TestNo] &
"'")
Click to expand...
Use the following code to avoid the DLookups and to speed up reading the
data:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblLFTests " & _
"WHERE TestNo = '" & [TestNo]& "'")
If Not rs.Bof And Not rs.Eof Then
LFTestNo = rs![LFTestNo]
LFTestType = rs![LFTestType]
GCMethod = rs![GCMethod] 'and so on...
End
rs.Close
Set rs = Nothing
When I tab through the form it only picks up the first line that corresponds
to the data I am looking for. The original information has many lines but
only certain lines have information that needs to be looked up and entered
into the form and stored in a different table.
Click to expand...
Is your form bound to a data source? If it is, then use the On Current
event to populate your fields with the code above.



mfG
--> stefan <--
Click to expand...
 
S

stefan hoffmann

hi Curt,

Curt said:
I am using the DLookUp on lost focus of a text box. Where do you suggest I
enter the code that you sent me.
Click to expand...
Use the after update event of the text box, if it is the place where you
enter [TestNo].

Where do you get the value [TestNo] from?

mfG
--> stefan <--
 
C

Curt Hand

Hi Stefan,

i get the TestNo from a table called tblLFTests.

stefan hoffmann said:
hi Curt,

Curt said:
I am using the DLookUp on lost focus of a text box. Where do you suggest I
enter the code that you sent me.
Click to expand...
Use the after update event of the text box, if it is the place where you
enter [TestNo].

Where do you get the value [TestNo] from?

mfG
--> stefan <--
Click to expand...
 
Top