Microsoft KB Archive/109406

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

Q109406

The information in this article applies to:

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:

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: * 
```
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.

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]

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

Open the Suppliers form in Form view.
Choose the B alphabet filter button at the bottom of the form to filter for company names that start with the letter B.
Click the drop-down arrow on the Select Company To Find combo box. Note that all suppliers, not just those with a company name that starts with the letter B, are listed.

Keywords : kbusage
Issue type : kbprb
Technology :