Best way to lookup a single value from a table...

[Chatbox]

What's the least CPU-hungry way of reading a certain value from a table?
Any other way better than DLookup()?

 

[llkhoutx]

Use a recordset search, it's substantially faster a DLookUp.

 

[Chatbox]

Really? But I don't have a recordset setup yet.
I mean, DLookup is slower than "init a recordset, make a connection, open the recordset, do a search and then read off the result"??
Also, when you said recordset, are you referring to ADO or DAO?

 

[llkhoutx]

I use DAO.

DLookUp is substantially slower that coding!

Post the DLookUp you're using and I'll write a simple DAO routine which searches a recordset snapshop for you.

 

[Chatbox]

dlookup("Customer_Name", "Customers", "ID=" & CustID)

I greatly appreciate it.

 

[Rich]

Use a combo box instead, it's faster than both methods suggested and doesn't need any code.

 

[Chatbox]

You mean by setting the recordsource?
There really should be a book about what's faster than what in Access....

 

[Jon K]

Running dlookup("Customer_Name", "Customers", "ID=" & CustID) should be fast if ID is indexed.


I tested the following code on a table containing 524,288 records (using Access 2003 on Windows XP, Pentium III 600 MHz, 256M RAM):

Private Sub Command0_Click()

  Dim x
  x = Timer()
  
  Dim CustID As Long
  CustID = 524288
  
  Me.txtLookup = [b]DLookup("FTE", "Table1", "ID=" & CustID)[/b]
  
  MsgBox Timer() - x

End Sub

When the ID field in the table was indexed, the result was almost instantaneous. It took only 0.009762 second to display the result in txtLookup.

When ID was not indexed, it took 2.013672 seconds.
.

 

[raskew]

I just responded to dLookUp without a record ID. In the process created a form as described by the poster. You might want to spend a minute creating a wide command button and a label and inserting the code. Suppect you'll be astounded at how quickly the code reacted to movement of the mouse. Visually, there is no time lag.

Bob