Slow form opening

  • Thread starter Thread starter M Skabialka
  • Start date Start date
M

M Skabialka

I have a form with a subform. To select data in the subform there is a
combo on the main form that contains the distinct results of a query, and it
returns over 25,000 records.

SELECT DISTINCT CDINDEX.DocNumber FROM CDINDEX ORDER BY CDINDEX.DocNumber;

It is very slow to open the first time but after that selecting a DocNumber
is fast enough.

Should I add the control source to the combo after opening, when the user
selects a DocNumber, or will the first instance of selecting that be just as
slow?

What is the best way to do this? This form opens when the database opens,
and it almost seems that Access has hung for several seconds.

Thanks,
Mich
 
When you open the CDINDEX table in design view, is the DocNumber field
indexed? That would make a big difference.

You could also try the things that Tony Toews suggests in the Access
Performance FAQ at:
http://www.granite.ab.ca/access/performancefaq.htm
Particularly, things like Name AutoCorrect and SubdatasheetName make a big
difference.

Beyond that, you could delay-load the combo, as described here:
Combos with Tens of Thousands of Records
at:
http://allenbrowne.com/ser-32.html
 
The index covers 3 fields, the combo only one, so it doesn't seem any
faster. However I will try some of your ideas. I have used Tony's ideas
before but they do not seem applicable to Access 2003.

Thanks,
Mich
 
If the combo is the first field in the index, it does not need to be indexed
separately.

If this is the 2nd or 3rd field of a 3-field index, then it strikes me that
you may be able to set the combo's RowSource so that it has no records until
the user chooses something in the earlier one(s). Its initial RowSource
would be:
SELECT DISTINCT CDINDEX.DocNumber FROM CDINDEX
WHERE (False) ORDER BY CDINDEX.DocNumber;

Then in its Enter event (if the bound column is not zero-width) or the
AfterUpdate event of the previous combo and the Current event of the form
(if it is), load only the necessary records. This kind of thing:

Const strcStub = "SELECT DISTINCT CDINDEX.DocNumber FROM CDINDEX WHERE
("
Const strcTail = ") ORDER BY CDINDEX.DocNumber;"
Dim strWhere As String

If IsNull(Me.MyPreviousCombo) Then
strWhere = "[SomeField] = " & Me.MyPreviousCombo
Else
strWhere = "(False)"
End If
Me.CDINDEX.RowSource = strcStub & strWhere & strcTail

The goal is that only a subset of records are loaded into the combo's list
instead of all 23k.
 
Back
Top