Article ID: 112747 - Last Review: January 18, 2007 - Revision: 2.1

ACC: Tips for Improving Subform Performance

Retired KB ArticleRetired KB Content Disclaimer
View products that this article applies to.
This article was previously published under Q112747
Moderate: Requires basic macro, coding, and interoperability skills.
Expand all | Collapse all

SUMMARY

This article lists several things that you can do to improve the speed and performance of your subforms.
Back to the top

MORE INFORMATION

To improve subform performance, try these tips:
  • If you can, base your subforms on queries rather than tables. Include only fields from the record source that are absolutely necessary. Extra fields can decrease subform performance.
  • Index all the fields on 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 criteria query).
  • 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: 
          Name: EmployeeIDOrderID
      ControlSource: =[EmployeeID] & [OrderID]

      NOTE: In Microsoft Access version 2.0, there is a space in the
      [Employee ID] and [Order ID] fields.
    Next, add the following field to the query that the subform is based on: 
          EmployeeIDOrderID: [Employee ID] & [Order ID]
    Then, link the main form and the subform on the concatenated field rather than on the two individual fields. The subform properties might look as follows: 
          LinkChildFields: EmployeeIDOrderID
      LinkMasterFields: EmployeeIDOrderID
    Because Microsoft Access only has to compare 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 prevents Microsoft Access from determining the proper justification of the combo box values for each record and thus speeds the display of subform records which have combo boxes.
Back to the top

REFERENCES

For more information about general performance and indexing recommendations, search the Help Index for "Performance Analyzer," or ask the Microsoft Access 97 Office Assistant.
Back to the top
APPLIES TO
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
Back to the top
Keywords: 
kbinfo kbusage KB112747
Back to the top
Retired KB ArticleRetired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top
 

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations