Access Cookbook - Ken Getz [170]
The frmSurveySlow form in 08-02b.MDB contains a combo box, cboPersonId, bound to a 15,000-row table. This makes form load and combo box list navigation slow. Combo and list box controls are excellent for allowing users to choose from a list of values and work well with a small number of list rows. However, they perform poorly when the size of the list exceeds a few thousand rows, even with very fast hardware.
We were able to improve the load time of the survey form significantly by limiting the rows in the person combo box. This was done using a pop-up form containing the same combo box control, but linked to a text box control that filtered the combo box's rows via a parameter query. Using a little VBA code, we disabled the combo box control until at least one character was entered into the text box. In this way, we reduced a 15,000-row combo box to, on average, 577 rows (15000 / 26), and that's when only the minimum number of characters (one) is typed into the text box. You could increase performance by waiting for at least two or even three characters, rather than filling the list after the user has typed only one letter.
Besides reducing the number of rows in the row source for cboPersonId, two other improvements were made to boost combo box performance. On the original frmSurveySlow form, a SQL statement was used as the row source for the combo box; the cboPersonId combo box on the pop-up form uses a saved query instead. Saved queries are always faster than SQL statements because the query optimizer optimizes the query when it is saved instead of when it is run.
In addition, the SQL statement for frmSurveySlow's combo box includes the following ORDER BY clause:
ORDER BY [LastName] & ", " & [FirstName]
In contrast, the SQL statement for the qryPersonComboBox query used as the row source for frmPersonPopup uses the following ORDER BY clause:
ORDER BY tblPeople.LastName, tblPeople.FirstName
Although these two ORDER BY clauses look similar, the first one sorts on an expression, whereas the second sorts on two indexed fields. It's always faster to sort on individual fields rather than expressions.
There are several other things to consider when looking for ways to speed up your forms. You may wish to try some or all of the following suggestions:
Preload and keep loaded forms (see the Solution in Recipe 8.1).
Ensure that fields used to sort or filter rows are indexed in the underlying tables (see the Solution in Recipe 8.4 for more on indexing and its effect on query performance).
Use referential integrity throughout your database. Besides the obvious improvements to the quality of your data when you create enforced relationships, Access creates hidden foreign key indexes that improve the performance of queries, forms, and reports based on the joined tables.
Create simpler forms with less color, fewer graphics, and fewer fonts.
Limit the number of records in the form's recordset (see the Solution in Recipe 8.6).
Watch out for Tab controls with many pages and subforms on each page. Loading all those subforms will slow the opening of your form. One alternative is to load the subforms on a Tab control page only when that page is selected. You can do this by using the Change event of the Tab control to check the Value of the control—this tells you the PageIndex of the selected page. You can set the SourceControl property of your subforms only when the page they appear on is selected; you can't set it in design view.
8.3. Make Combo Boxes Load Faster
Problem
Sometimes you need to use combo boxes that list many items. It takes the user a long