Dlookup speed

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have a Form, Demographics, with a subform, Transactions. Within the
Transactions form, there are 2 text controls that use Dlookup to
translate from a code in the Transaction table to a transaction
description and other descriptions. This is fairly slow to load the
form and display initially, as well as I scroll down the list of
transactions.

Is there a better way to do this? Using Access2K

Steve
 
I have used the lookup functions a lot in the past and they were never so
"slow" that I couldn't live with it.
In fact I never really noticed any impact.
I am not sure what you are doing here that is causing it to be noticeably
slow.

Maybe you can post some sample code to illustrate the issue better.

One idea would be to return the description in a JOIN for your controls and
just display the extra data and hide the common key value.
 
I guess slow is a relative term!!! There is a 1 or 1.5 second delay in
displaying the form in it's entirety, and the Dlookups are the last to
appear. One of the Dlookups is this:
=DLookUp("TRDescription","TRcodes","[trcode]=[trcodeid]")

where this is the Control Source code for the TextBox in the detail
section of the subform. I have one other Dlookup in this detail
section. I also have 2 other subforms (one of which has its own
dlookup). The code runs, my concern is the noticeable delay in
populating to form down the details. One record in particular has 271
detail lines, only 20 or so are visible at any one time. This may
contribute to my delay, but the records with less also have a delay.

Steve
 
Steve said:
I guess slow is a relative term!!! There is a 1 or 1.5 second delay
in displaying the form in it's entirety, and the Dlookups are the
last to appear. One of the Dlookups is this:
=DLookUp("TRDescription","TRcodes","[trcode]=[trcodeid]")
Click to expand...

Unless you have "[trcode]= " & [trcodeid] depending on the value of
trcodeid you will have a problem.

Why aren't you using a related table? Without more information this is wrong
from a relational database aspect.
 
Steve,

I've used multiple sequential DLookups on extremely large tables (>6 million
records) in Access 2000. In that scenario, one or more DLookups took so long
to return that they presented the appearance of a corruption. Replacing the
DLookups with recordset queries resolved the issue.

But I think that scenario was extraordinary, and would be extremely rare.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
Graham R Seach said:
Steve,

I've used multiple sequential DLookups on extremely large tables (>6 million
records) in Access 2000. In that scenario, one or more DLookups took so long
to return that they presented the appearance of a corruption. Replacing the
DLookups with recordset queries resolved the issue.

But I think that scenario was extraordinary, and would be extremely rare.
Click to expand...

Agreed. Domain aggregates should generally be avoided in situations where they
will be called many times in a row (as in a query or code loop). Your situation
would be dictated by the typical number of records in the subform. If more than
half a dozen I would recommend trying a query for the subform that pulls in the
other data rather than using DLookup(). If less than that it would likely fall
into the category "not optimal, but workable given the speed of modern
hardware".
 
Back
Top