DLookup from a Recordset?

[kgcrowther]

I was using DLookup from a Query, but it was very slow (I assume Access must create the query each time I DLookup from it). Here is the original slow code that works fine, but slow:

d8p3 = DLookup("[D8P3]", "SPLIT-Destinations", "[SCTG_CODE] = " & sctg2)

I have three of these each time the do loop enters an if statement. It takes about 3 to 4 seconds to perform each one. (I estimate that it will enter the if statement about 14,000 times, and that's a lot of seconds!) I figure a solution is just to put the query in memory, something like:

Dim rsSplit As DAO.Recordset
Set rsSplit = CurrentDb.OpenRecordset("SPLIT-Destinations")

But then how do I write the DLookup?

 

[RuralGuy]

Is there no way to Join the two tables in a query to achieve your results?

 

[Bat17]

Assuming that SCTG_CODE is an indexed field then you should be able to use the Seek methoed of the record set to find what you want the fastest. Look it up in help for details. Otherwise look at Findfirst/FindNext.

HTH

Peter

 

[kgcrowther]

Thanks -- Much Faster Now

Rural Guy, I thought and thought about how to do this, but I use the Lookup value to edit and add new records to the original table. I couldn't think of a way to do it with a query.

Peter, I tried to use RecordSet.Index and RecordSet.Seek, but Access gave me an error that said the object was inappropriate for these methods.

The FindFirst method works great! Here's is the code that I'm using now, which is much, much faster:

Dim rsSplit As DAO.Recordset
Set rsSplit = CurrentDb.OpenRecordset("SPLIT-Destinations")
rsSplit.FindFirst "[SCTG_CODE] = " & sctg2
d8p3 = rsSplit("D8P3")

The program, which took days using the DLookup (I'm not sure how many days, since I never let it finish), now take just over a minute. Thanks for all the comments. I learn something new everytime I post!!!

--KGC
Charlottesville, VA

 

[RuralGuy]

Glad you got it working and thanks for posting back with your success.