Recipe 8.2 Make Slow Forms Run Faster
8.2.1 Problem
You are not happy with the
speed at which your forms load and display. How can you change your
forms so they will load and display faster?
8.2.2 Solution
Access gives you a lot of flexibility to develop dynamite-looking
forms. Unfortunately, Access also makes it easy to create forms that
run painfully slowly. The Solution in Recipe 8.1 explained how you can speed up the loading
time of all forms by preloading them. This solution discusses how to
track down and fix various performance bottlenecks, thus improving
form execution performance. We also discuss the use and misuse of
graphic elements and combo and list box controls.You should consider several potential issues when analyzing your
forms for performance. We discuss here two common performance
bottlenecks: controls involving graphic or memo field data, and combo
and list box controls.
8.2.2.1 Graphic and memo controls
Load the
08-02a.MDB database. Open the
frmCategoriesOriginal form (see Figure 8-5). This
form, although attractive, loads slowly and has a noticeable delay on
slower machines when moving from record to record. Now open
frmCategoriesStep3, which is the final version of the form after
various optimizations have been applied to it (see Figure 8-6). Its load and execution times should be
noticeably faster.
Figure 8-5. The original form, frmCategoriesOriginal, is slow
Figure 8-6. The final form, frmCategoriesStep3, is faster
Follow these steps to improve the
performance of forms that include unbound graphic controls or bound
controls that hold OLE or memo fields:
- Open the problem form in design view. If you have any unbound object
frame controls (also know as unbound OLE controls) that are used to
store fixed graphic images, change them to image controls by
right-clicking on the object and selecting Change To Image (see Figure 8-7). The frmCategoriesStep1 form in the
08-02a.MDB sample database is identical to
frmCategoriesOriginal except that ctlLogo has been converted from an
unbound object frame control to an image control.
Figure 8-7. Changing an unbound object frame control to an image control
- If
you created a watermark for the form, consider removing it. To do
this, select the word "bitmap" in
the form's Picture property, press the Del key, and
answer Yes to the confirming dialog. The frmCategoriesStep2 form in
08-02a.MDB is identical to frmCategoriesStep1,
except that we deleted the watermark. - If your form contains any bound controls that hold either OLE or memo
fields, consider moving the controls to a second page of the form. In
the final version of the Categories form, named frmCategoriesStep3
(Figure 8-6), we moved the ctlDescription and
ctlPicture controls to a second page.
8.2.2.2 Combo and list box controls
Load the
08-02b.MDB database. Open the frmSurveySlow
form. This form contains a combo box control, cboPersonId, that has
as its row source a SQL Select statement that
pulls in 15,000 rows from the tblPeople table. Load time for the form
is slow because Access has to run the query that supplies the 15,000
rows to cboPersonId. Tab to the cboPersonId control and type
"th" to search for the name
"Thompson, Adrian" (see Figure 8-8).
Figure 8-8. The cboPersonId combo box in frmSurveySlow is very slow
Note the long delay before the "th"
list of records appears. Now open the frmSurveyFast form (see Figure 8-9); its load time is significantly faster. Press
the ">" command button to open
the frmPersonPopup form. Type "th"
in the first field and press Tab.
Figure 8-9. In frmSurveyFast, the combo box is replaced with a text box and command button
After a short delay, you'll be able to select
"Thompson, Adrian" from the
drop-down list as shown in Figure 8-10. Press the OK
button, which will drop the chosen name back into the txtPersonName
text box on frmSurveyFast.
Figure 8-10. Selecting a name from the drop-down list is much faster
Follow these steps to improve the speed of forms containing combo or
list boxes that must display a lot of information:
- Make a copy of the problem form and open the copy in design view.
Select the slow combo or list box control. Right-click on the control
and select Change To Text Box. - Create a new unbound pop-up form
with the property settings shown in Table 8-2.
Leave the remaining property settings at their defaults. In the
sample database, this form is named frmPersonPopup.
Property | Setting |
---|---|
ScrollBars | Neither |
RecordSelectors | No |
NavigationButtons | No |
AutoResize | Yes |
AutoCenter | Yes |
PopUp | Yes |
Modal | Yes |
MinMaxButtons | None |
- Create four unbound controls on this form: a text box, a combo box,
and two command buttons. In the sample database, we created the
controls shown in Table 8-3. The text box will be
used to limit the number of items in the combo box, using the
parameter query created in Step 4.
Control type | Control name | Notes |
---|---|---|
Text box | txtChar | Limits the values in the row source of the combo box |
Combo box | cboPersonId | Uses the parameter query created in Step 4 as its row source |
Command button | cmdOK | Hides form |
Command button | cmdCancel | Closes form |
- Create a new query that will serve as the row source for the combo
box of the pop-up form. If you used a query as the source for the
combo or list box on the original form, you should be able to modify
its design. Add the necessary fields to the query. Add a parameter to
the form that limits the rows based on a value typed into the text
box on the pop-up form. Choose any sort fields. In the sample
database, we created the qryPersonComboBox query with the fields
shown in Table 8-4. Save and close the query.
Query field | Sort | Criteria |
---|---|---|
PersonId | (None) | (None) |
FullName: [LastName] & ", " & [FirstName] | (None) | (None) |
LastName | Ascending | Like [Forms]![frmPersonPopup2]![txtChar] & "*" |
FirstName | Ascending | (None) |
- Reopen the pop-up form
created in Steps 2 and 3. Set the Enabled property of the combo box
to No. Set the RowSource property to point to the query created in
Step 4. In the sample database, we set the properties of the
cboPersonId combo box to the values in Table 8-5.
Property | Setting |
---|---|
Enabled | No |
RowSourceType | Table/Query |
RowSource | (Blank) |
ColumnCount | 2 |
ColumnHeads | No |
ColumnWidths | 0";2.5" |
BoundColumn | 1 |
ListRows | 8 |
ListWidth | 2.5" |
- Create a new event procedure for
the text box's Change event. (If
you're unsure of how to do this, see
How Do I Create an Event
Procedure? in the Preface of this book.) Add the
following code to the event procedure:Private Sub txtChar_Change( )
Change txtChar to the name of your text box and cboPersonId to the
If Not IsNull(Me.txtChar.Text) Then
Me.cboPersonID.Enabled = True
Else
Me.cboPersonID.Enabled = False
End If
End Sub
name of your combo box. - Create a new event procedure
for the text box's AfterUpdate event and add the
following code to it:Private Sub txtChar_AfterUpdate( )
Change txtChar to the name of your text box, and cboPersonId to the
Dim ctlPersonId As ComboBox
Dim ctlChar As TextBox
Set ctlPersonId = Me.cboPersonID
Set ctlChar = Me.txtChar
If Not IsNull(ctlChar) Then
ctlPersonId.RowSource = "qryPersonComboBox"
ctlPersonId.SetFocus
ctlPersonId.Dropdown
End If
End Sub
name of your combo box. Change qryPersonComboBox to the name of the
query you created in Step 4. - Create the following new event
procedure for the OK command button's Click event:Private Sub cmdOK_Click( )
Me.Visible = False
End Sub - Create the following new event procedure for the Cancel command
button's Click event:Private Sub cmdCancel_Click( )
DoCmd.Close acForm, Me.Name
End Sub - Save the pop-up form and close it.
- Reopen the form from Step 1 in design view. Add a button called
cmdPopup to the right of the text box. Add the following event
procedure to cmdPopup's Click event:Private Sub cmdPopup_Click( )
Change frmPersonPopup to match the name of the pop-up form. Change
Const acbcPopup = "frmPersonPopup"
' Open up pop-up form in dialog mode.
DoCmd.OpenForm acbcPopup, WindowMode:=acDialog
' Check if form is still loaded.
' If yes, then OK button was used to close pop-up.
If SysCmd(acSysCmdGetObjectState, acForm, acbcPopup) <> 0 Then
Me.PersonID = Forms(acbcPopup)!cboPersonID
DoCmd.Close acForm, acbcPopup
End If
End Sub
PersonId and cboPersonId to the names of the appropriate controls.
8.2.3 Discussion
When you have a form that loads and executes slowly, you need to
analyze the form and weigh the advantages and disadvantages of using
graphic features. After a careful analysis of the
frmCategoriesOriginal form in the 08-02a.MDB
database, we made several changes.First, we changed the unbound
object frame control to an image control. The OLE-based object frame
control can be used to hold graphic images, sound, and other
OLE-based data such as Excel spreadsheets or Word documents. But if
you need to display only an unbound bitmap, you're
better off using the more resource-conservative image control.Second, we removed the form watermark, as this feature slows down
form execution slightly. The improvement in performance depends on
the color-depth of the removed image and the speed of your machine.Finally, we
created a second page and moved the text box bound to the memo field
and the bound object frame bound to the OLE field to this second
page. These field types (memo and OLE) are stored separately from the
rest of the fields in a record and thus require additional disk reads
to display. Fortunately, Access does not fetch these potentially
large fields from the database unless they are visible on the screen.
By placing them on the second page, you can quickly navigate from row
to row without having to fetch the memo or OLE data. When you need to
view the data in the fields, you can easily flip to the second page
of the form.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 controlthis 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.