Slow form with dlookups

[hudaz]

Hi everyone,

I have a form that displays certain data input throughout the day so management can see how the machine is performing, however they requested to see some information that has resulted in another 24 text boxes being added to the form all with dlookup's in the control source.

As you can imagine this has slowed the system down to a crawl and as it refreshes every 10 seconds its now painful to use, Could anyone give me some advice onto how to speed this up ?, i've tried converting it to code but it is just as slugish!


Thanks!

Andy

 

[CJ_London]

perhaps use a query to collect the data as a recordsource to your form. queries are faster by a very large factor (perhaps 100 times). Also take a look at this website which analyses alternatives to dlookup with suggested solutions

https://www.experts-exchange.com/articles/1921/Access-Techniques-Fast-Table-Lookup-Functions.html

 

[Ranman256]

You shouldn't be using dlookups.
Use a query.

 

[hudaz]

You shouldn't be using dlookups.
Use a query.

How would i get that information into a text field if i didn't use dlookups ?

 

[jdraw]

Perhaps you could show us a few screen shots (jpgs) and some of the code you're using to fill the text boxes.
The link provided by CJ has some great points/examples.

Keeping 24 text boxes updated every 10 seconds seems like a lot of activity.
What is the difference to management if updates were every 2-5 minutes vs. 10 seconds?

 

[The_Doc_Man]

You should be aware that each DLookup synthesizes an ad-hoc query, so if you have 24 DLookup calls, you have 24 ad-hoc queries - NONE of which can be optimized or pre-analyzed.

By any chance, when you do these lookups, are the criteria fields the same for each case? Or at least, do large numbers of them share the same criteria and source table? Because if they do, you might get better response as suggested above by making a query to drive the lookups that have have table and criteria in common and BINDING a status form to that query so that the textboxes would automatically populate for you.

 

[missinglinq]

How would i get that information into a text field if i didn't use dlookups ?

Probably need to use the Query as the RecordSource for a Subform to display the 24 fields.

As a caveat, and as has been pointed out by jdraw, refreshing these fields every 10 seconds is a lot of processing and is probably going to remain relatively slow, whatever approach you take.

Linq ;0)>