DLookUp

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)?
 
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 <--
 
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.
 
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...
 
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 <--
 
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...
 

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

Similar Threads

Using Max or DMax in DLookUp criteria? 1
DLookUp to write to new table 5
DLookup 5
DLookup Help 4
Trigger an Insert on a Subform 14
DLookup 2
Access Access DLookup Function – more than 1 criteria 0
DLookUp 1

Back
Top