Seven Performance Hints For Combo And List Boxes In Access

By Sandeep Anand

Things not to do (if you can help it) include ...

1. Using an SQL statement instead of a Query

Don't use an SQL statement as your RowSource for the combo/list box instead use a saved query.

Whenever I use a combo/list box in a form, I first create a query that will be used to supply the data for this combo/list box.

Using a wizard to create a combo/list box is easy but it does one thing wrong. The wizard sets the RowSource of the combo/list box to an SQL statement, which does not give the best performance.

If you used a wizard to create a combo/list box in a form, you should change the RowSource property to a saved query.

To change the RowSource property to a saved query, click the Build button. This opens the Query Builder window. Click on Save on the File menu, and type a name for the query. When you close the Query Builder window, click on Yes to update the property.

2. Keeping unnecessary fields in the RowSource

Leaving extra fields in the query that is used as the RowSource for the combo/list box can decrease performance.

Don't put more fields in the query than are being used in the combo/list box. Include only the fields that are absolutely necessary.

3. Using Linked tables

If the data in the combo/list box is not going to change, don't use linked tables as the RowSource. In this case, it's better to import the data into your database instead.

4. No Indexing

You should index the bound field of the combo/list box. If the first field is not the bound field you should index the first field as well.

5. Using a Number field instead of Text When Using AutoExpand

If you are using the fill-in-as-you-type feature of combo boxes and have the AutoExpand property of the combo box set to Yes, then make sure that the first displayed field is of Text data type.

Access converts a numeric value to text in order to find a match in the list, when AutoExpand is on. If the data type is Text, Access doesn't have to do this conversion.

6. Leaving AutoExpand On

Don't leave the AutoExpand property to Yes, if you are not using the fill-in-as-you-type feature of the combo box. Set this property to No.

7. Using Multiple-table RowSource

If possible, don't use Multiple-table RowSources. Use single-table or query RowSources instead. This is even more important on a form, if the bound field in a lookup combo box is not the displayed field.

Article by Sandeep Anand of Mata Technologies.

Click on the button for the next tips page.

Other Related pages at vb123 are

Persistent Caching >>>Take a Load Off Your Server with Access 97-2000
Consolidation Queries
Smart Selection Criteria For Queries using Combo Boxes
Form Based Selection Criteria For Queries using Combo Boxes