How to enhance the speed of "DLookup" in query?

[hkgary33 via AccessMonster.com]

Dear all,
In order to show the calculation results from a query in a form, i've used 10
control textboxes to show the 10 calculated result fields in a query, and I
used DLookup function in each of these 10 textboxes to show these result
fields.
It works, but the problem is that once the number of records increases (about
1000 records), the results in the textboxes need so much time to display the
result. However, if I run the query alone and view the result in Datasheet
view, it requires less time. So I wonder if the DLookup function runs too
slow to show the result.
Or can anyone suggest other method to show the fields in a query in textboxes
in forms?
Thanks so much!
Gary

 

[Marshall Barton]

In order to show the calculation results from a query in a form, i've used 10
control textboxes to show the 10 calculated result fields in a query, and I
used DLookup function in each of these 10 textboxes to show these result
fields.
It works, but the problem is that once the number of records increases (about
1000 records), the results in the textboxes need so much time to display the
result. However, if I run the query alone and view the result in Datasheet
view, it requires less time. So I wonder if the DLookup function runs too
slow to show the result.
Or can anyone suggest other method to show the fields in a query in textboxes
in forms?

The reason it's so slow is that each DLookup reruns your
entire query. The best way to speed it up is to not use
DLookup for multiple fields.

One alternative is to use a subform/subreport bound to your
query. This is usually the easiest way to deal with this
kind of situation.

Another is to use an appropriate event procedure in the
form/report to open a recordset on the query and copy all 10
query fields to the text boxes.