ACC95: Filter By Form Fails in Combo Box Filled with Value List

• Microsoft Access version 7.0

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

If you use Filter By Form and select a value in a combo box to filter on, no records are returned.

CAUSE

The combo box is filled with a value list and the bound column is not the first displayed column in the list. Microsoft Access does not support Filter By Form on combo boxes filled with a value list when the bound field is not the first displayed field.

RESOLUTION

To work around this problem, use Filter By Selection, or display the bound column as the first visible column in the combo box list, or fill the combo box with values from a table or query.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 7.0. This problem no longer occurs in Microsoft Access 97.

MORE INFORMATION

In Filter By Form mode, Microsoft Access treats the displayed field as the bound field, and then places the restriction on the looked-up value for the table or query that the combo box is filled from. This technique cannot be used for combo boxes filled with a value list.

Steps to Reproduce Problem

1. Open the sample database Northwind.mdb and add the following new field to the Employees table:

         Field Name: Gender
            Data Type: Text
            Field Size: 1
            Display Control: combo box
            RowSourceType: Value List
            RowSource: Male;M;Female;F
            BoundColumn: 2
            ColumnCount: 2
            ColumnWidths: ;0
            ListRows: 2
   
   

2. Save the changes and view the Employees table in Datasheet view.

3. Click Male or Female for the new Gender field for each Employee record.

4. Click the Filter By Form toolbar button, click "Male" in the combo box for the Gender field, and click the Apply Filter toolbar button. Note that no records are returned.

5. Click the Remove Filter toolbar button.

6. Highlight "Male" in the Gender field for an exiting record and click the Filter By Selection toolbar button. Note that records with Male selected are returned.