Find Record

[Michaels41]

Is there a more efficient way find values from multiple fields in a record other than using DLookup multiple times?

 

[tehNellie]

I'd say yes, but it's difficult to expand on that answer much based on the information you've given.

 

[Michaels41]

If I want to lookup fields in a table such as EmployeeNumber, FirstName, LastName, DeptNo and assign to 4 different variables, I have to use the DLookup function 4 times. Is there an easier or more efficient way to do this?

 

[tehNellie]

Yes, use a query.

SELECT EmployeeNumber, FirstName, LastName, DeptNo 
FROM a table
WHERE criteria = somevalue

open that in a recordset, now you can do what you want with it:

dim db as dao.database
dim rs as dao.recordset

set db = currentdb
set rs = db.openrecordset("queryname or SQL string")

Do while not rs.EOF
  debug.print rs![Employeenumber] & " " & rs![Firstname] etc
  rs.movenext
Loop

 

[Michaels41]

Thanks, tehNellie....works great. Also works using "SELECT * FROM etc. Funny how something so simple can be so hard.

 

[tehNellie]

It was only hard because you didn't know how to do it.

I'd try and convince you to not use SELECT * in your production code/queries. It works for sure, but if you know you want "EmployeeName" from your table, SELECT it specifically. Other than reducing the amount of data that you retrieve from the database it makes it very clear what the query or SQL string is supposed to be doing when you come back to it in 6 months time and try and figure out what it was up to.