ACC2000: Filter By Form Options Do Not Apply to Lookup Fields (208573)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q208573
Novice: Requires knowledge of the user interface on single-user computers.

SYMPTOMS

When you use the Filter By Form feature, a combo box or list box contains more values than the number that 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 Lookup 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 Lookup field in Design view.
  2. Click the Lookup field, click the Lookup tab, click 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 that 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 Lookup field contains only as many rows as you specified in step 4.

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 Don't display list where more than this number of records read setting to specify the number of records to read when filling value lists for a Filter By Form combo box or list box.

If the form's recordset contains more records than the number specified on the Edit/Find tab of the Options command, 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 Lookup 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

For more information about limiting reports, click Microsoft Access Help on the Help menu, type TopValues property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:6/29/2004
Keywords:kbprb kbusage KB208573
©2004 Microsoft Corporation. All rights reserved.