dlookup slow results

Question

I have a problem in access 2010 where my dlookup runs very slow but if I already have the saved query open it runs pretty quickly. Any Idea has to why this is and how to get around the problem

edit

txtPurchase_Price = DLookup("Purchase_Price", "qryPurchaseInfo", "ID = " & check)

That is the dlookup

And the sql for the query is

SELECT Receiving.Serial,Purchase_Info.Purchase_Price, Purchase_Info.Check_Number,
  Purchase_Info.Purchase_Date, Purchase_Info.ID, Receiving.Source
FROM Purchase_Info INNER JOIN Receiving ON 
  Purchase_Info.ID = Receiving.Purchase_Price_ID;

Answer 1

I'm unsure why you're using a query as the data source (the Domain option) for DLookup. That makes sense if your intention is to restrict the set of allowable Purchase_Info.ID values to those also present as Receiving.Purchase_Price_ID values.

But if that is the case, try a simpler query instead.

SELECT
    Purchase_Info.Purchase_Price,
    Purchase_Info.ID
FROM Purchase_Info INNER JOIN Receiving
ON Purchase_Info.ID = Receiving.Purchase_Price_ID;

Although, I'm skeptical that query would produce a dramatic speed improvement, I can't understand why it would be slow as long as you have indexes on both Purchase_Info.ID and Receiving.Purchase_Price_ID.

OTOH, if that wasn't your intention, then you should be able to retrieve Purchase_Price directly from your Purchase_Info table without needing to join it to the Receiving table. This should be quick with an index on Purchase_Info.ID:

DLookup("Purchase_Price", "Purchase_Info", "ID = " & check)

Finally, I don't know what check is. It could be the name of a control on a form. But, whatever it is, I would prefer to give it a different name because check is a reserved word. I can never predict when reserved words as object names will cause trouble, so prefer to avoid them completely.