ACC: Filter By Form Options Do Not Apply to Lookup Fields

• Microsoft Access versions 7.0, 97

SYMPTOMS

When you use the Filter By Form feature, a combo box or list box contains more values than the number you specified in the "Don't display list where more than this number of records read" box on the Edit/Find tab of the Options command on the Tools menu.

CAUSE

The Filter By Form combo box or list box is based on a look up field and the field's DisplayControl property is set to Combo Box or List Box in the underlying table. Microsoft Access uses the field's RowSource property setting from the table to fill the combo box or list box with values instead of reading records from the form's recordset.

RESOLUTION

You can set the field's RowSource property in the underlying table to a top values query, which limits the number of records (or rows) returned to the field's combo box or list box. To do so, follow these steps:

1. Open the table with the look up field in Design view.

2. Select the look up field, click the Lookup tab, select the RowSource property, and then click the Build button to open the Query builder.

3. In the Query builder, click Properties on the View menu.

4. In the Query Properties box, for the TopValues property, enter the maximum number of items you want in the field's value list.

5. Close and save the query.

6. Close and save the table. Note that when you later use the Filter By Form feature, the value list for your look up field contains only as many rows as you specified in step 4.

STATUS

This behavior is by design.

MORE INFORMATION

The Filter By Form feature displays a form with various controls (such as text boxes, combo boxes, and list boxes), which you can use for entering or selecting values to filter the form's underlying recordset. To create a combo box or list box on a Filter By Form, Microsoft Access reads records in the form's recordset to find unique values per field and then fills the box's value list with the unique values. If the form's recordset contains thousands of records, you can use the Options command to specify the number of records to read when filling value lists for a Filter By Form combo box or list box. The setting is called "Don't display list where more than this number of records read" and is located on the Edit/Find tab of the Options command on the Tools menu.

If the form's recordset contains more records than the number specified on the Options' Edit/Find tab, then Microsoft Access does not fill the combo box or list box with unique values from the form's recordset. Rather, it fills the list with two values: Is Null and Is Not Null. The only exception is when a field is a look up field (that is, when the field has its DisplayControl property set to combo box or list box in an underlying table). Then, Microsoft Access uses the table's RowSource property setting to fill the value list for a Filter By Form combo box or list box instead of reading records from the form's recordset.

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb.

2. On the Tools menu, click Options, and then click the Edit/Find tab.

3. In the "Don't display list where more than this number of records read" box, type the number 10. Click Apply, and then click OK.

4. Open the Products table in Datasheet view.

5. On the Records menu, point to Filter, and then click Filter By Form.

6. On the Products: Filter By Form form, click the ProductID field, and then click the arrow to view its list. Note that only two values appear: Is Null and Is Not Null.

7. Click the SupplierID field, and then click the arrow to view its list. Note that more than 10 values appear in the list even though you set the "Don't display list where more than this number of records read" option in step 3.

REFERENCES