recordset method to replace dlookup (lazy) 'habit'?

Question

please pardon my virgin post and if my question somehow got answered elsewhere, please kindly point me to that link (i did read related dlookup topic but didnt get what i want).

i freq use dlookup (due to newbie in access and lazy too) on forms for my textbox to hold values for my purpose (calculations, display) and knowing dlookup is random and takes time if db isnt local (which i am task to move it online) and time for me to utilize more professional approach to replace dlookup usage in general.

what i used to do is to retrieve data from 1 table (inventory) to populate textbox in a form:

txtItemName=dlookup ("ItemName","Inventory","Inv_ID=" & cboItem)
txtItemDesc=dlookup ("ItemDesc","Inventory","Inv_ID=" & cboItem)
txtItemPrice=dlookup ("ItemPrice","Inventory","Inv_ID=" & cboItem)

and such for data i need and sometimes could be more 10 dlookup(s) to lookup data for my textboxes.

i did read about recordset (DAO/ADO) but due to newbie in ms-access usage, i couldnt figure how to write (or copy) code that could perform what i need like the above dlookup(s).

i did try creating query to pull fields (say 10 fields) i need in 1 query and use dlookup (again) in recordsource to hold the value. (if this method has any advantage compared to 10x original dlookup i did before...)

i am willing to learn the right method to do the right thing in ms-access and seek all seniors to show me the way and kick my bad/newbie habit of abusing dlookup.

i thank you all in advance.

Answer 1

Actually, using dlookup() or using VBA code and a reocrdset?

The performance is 100% the SAME.

I repeat the performance is 100% the SAME.

You gain ZERO benefits by writing some VBA code that creates a recordset, and then creates the sql, and then pulls the one record. It is in fact a waste of time to pursue such a road and concept. It does NOT HELP performance at all, and using VBA code + a reocrdset to pull the one record and value is NOT faster.

They both perform the SAME speed!!!

What can make a difference? If you have dlookup() over and over in the sql, and you execute dlooup() for EACH ROW of the sql. Then that is slow.

So, say we had a combo box, and we have say the "id, HotelName".

So, in a query you might do this:

 select FirstName, LastName, dlookup("HotelName","tblHotels","ID = " & Hotel_ID) from bookings

In above, we used dlookup() to get the Hotel name, since I did not want to display the "hotel_id". Above is going to be VERY slow. The faster way is to use a sql join like this:

 SELECT FistName, LastName, Hotel_id, tblHotels.HotelName
 LEFT JOIN tblHotels on tblBookings.Hotel_id = tblHotels.id
 FROM tblBookings.

So, in above, we dumped the dlookup() (slow), to above, which will run very fast.

So, running ONE dlookup() vs VBA + reocrdset to get the ONE value? Same speed, no need to use VBA code to replace the dlookup() function.

But, if you run the function over and over - one time for each row? Then BOTH use of dlookup(), and use of a custom VBA function to do that for each row will BOTH be slow.