Optimize list box, drop-down list box, or combo box performance

There are several things you can do to make your list boxes, drop-down list boxes, and combo boxes run faster:

• Base the list box, combo box, or drop-down list box on a saved query instead of an SQL statement. On a form, if you use a wizard to create a list box or combo box, Microsoft Access automatically sets the RowSource property of the control to an SQL statement. To change the RowSource property to a saved query, click the Build button next to the RowSource property box. With the Query Builder window open, click Save on the File menu, and type a name for the query. When you close the Query Builder window, click Yes when Microsoft Access asks if you want to update the property.
  
  
• Base the drop-down list box on a saved query instead of a table. On a data access page, if you use a wizard to create a drop-down list box, Access automatically sets the ListRowSource property of the control to the table you specified in the wizard. To retrieve rows from a query instead of a table, select the query name from the ListRowSource property box.
  
  
• In the query specified in the RowSource or ListRowSource property box, include only fields that are absolutely necessary. Extra fields can decrease performance.
  
  
• Index both the first field displayed in the combo box, list box, or drop-down list box and the bound field (if the fields are different).
  
  
• In combo boxes on a form, set the AutoExpand property to No if you don't need the fill-in-as-you-type feature.
  
  
• On a form, if the AutoExpand property for a combo box is set to Yes, the first displayed field should have a Text data type instead of a Number data type. In order to find a match in the list, Microsoft Access converts a numeric value to text. If the data type is Text, Microsoft Access doesn't have to do this conversion.
  
  
• On a form, if the bound field in a lookup combo box is not the displayed field:
  • Don't use expressions for the bound field or the displayed field.
    
    
  • Don't use restrictions in the row source.
    
    
  • Use single-table (or query) row sources rather than multiple-table row sources, if possible.
• Don't create list boxes, drop-down list boxes, or combo boxes based on data in a linked table if the data won't change. It's better to import the data into your database, in this case.

Note   Builders are not available in a data access page. Wizards are not available in a stand-alone data access page, or when you point a data access page to a database other than the one currently open. In both cases, you will have to create a list box or drop-down list box on your own.