How to improve subform performance
- To improve subform performance, try these tips:
If you can, base your subforms on queries rather than tables.
- Include only those fields from the record source that are absolutely
necessary. Extra fields can decrease subform performance.
- Index all the fields in the subform that are linked to the main form.
Indexes help speed the search process to find the matching subform records.
- Index any fields used for criteria (such as when a subform is based on a
- If you are linking on multiple fields, add a calculated field to the main
form that concatenates the fields. Then create a calculated column in the
subform's RecordSource property query with the same expression.
For example, to link to the subform on an Employee ID field and an Order ID
field, add a text box to the main form with the following properties:
ControlSource: =[EmployeeID] & [OrderID]
Next, add the following field to the query that the subform is based on:
EmployeeIDOrderID: [Employee ID] & [Order ID]
Finally, link the main form and the subform on the concatenated field rather
than on the two individual fields. The subform properties may appear as follows:
Because Microsoft Access has to compare only one criteria to return the
subform's recordset, the subform's performance should improve.
- Set the subform's DefaultEditing property to Read-Only if the records in
the subform are not going to be edited.
- If your subform is a continuous form and contains combo boxes, explicitly
justify the combo box in the subform's form Design view. This practice
prevents Microsoft Access from determining the proper justification of the
combo box values for each record, and therefore speeds the display of subform
records that have combo boxes.