ACC1x: Company Pick List in Suppliers Form Doesn't Filter

• Microsoft Access versions 1.0, 1.1

SYMPTOMS

When you choose one of the alphabet filter buttons in the Suppliers form in the sample database NWIND.MDB, the suppliers in the underlying records are filtered correctly. However, the list of suppliers in the Select Company To Find box is not filtered, and still displays all the suppliers.

CAUSE

The alphabet filter buttons are not meant to filter the entries in the Select Company To Find combo box when the underlying record set is filtered.

RESOLUTION

The following steps describe how to modify the Suppliers form so that the alphabet filter buttons will filter the list in the Select Company To Find combo box:

1. View the Suppliers form in Design view. Place an unbound text box with the following properties anywhere on the form:

         ControlName: Company Pick List Filter
         Visible: No
         Default Value: *
   

2. Open the Alpha Filter Buttons macro in Design view. Add the following macro actions at the end of EACH ApplyFilter action:

         SetValue
            Item: [Company Pick List Filter]
            Expression: "[Aaaa]*"
   
         Requery
            ControlName: Company Pick List
   
         SetValue
            Item: [Company Pick List]
            Expression: [Company Name]
   
      The expression in the first SetValue action in each entry should be
      copied from the Where Condition argument of each ApplyFilter section.
      For example, for the A button the ApplyFilter Where Condition argument
      is "[AAAAA]*" (these characters are actually international versions of
      A, represented by A so they will appear on all computer displays).
      The B button's Where Condition argument is "B*," and so on.
   
   

3. After the ShowAllRecords action, add the following actions:

         SetValue
            Item: [Company Pick List Filter]
            Expression: "*"
   
         Requery
            ControlName: Company Pick List
   
         SetValue
            Item: [Company Pick List]
            Expression: [Company Name]
   
   

4. Open the Supplier List query in Design view and add the following criteria to the Criteria row of the Company Name column:

         Like [Forms]![Suppliers]![Company Pick List Filter] & "*"
   

How the Company Pick List Filter Works

After the ApplyFilter action filters the underlying records, the SetValue action places the criteria in the hidden text box control on the Suppliers form.

The Requery action forces the Select Company To Find combo box to refresh its list, forcing the Supplier List query to rerun. The query then runs using the hidden text box contents as the criteria for filtering the company names to appear in the list.

The final SetValue action ensures that a valid company name from the filtered list appears in the combo box.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior