Microsoft KB Archive/209542

= ACC2000: Base Combo Box on Parameter Query to Filter Values =

Article ID: 209542

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209542



This article applies only to a Microsoft Access database (.mdb).

Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
This article shows you how to filter values that appear in a combo box by basing the combo box on a parameter query.



MORE INFORMATION
Sometimes, you may want to limit the values that appear in a combo box. For example, you may want to show only suppliers whose names begin with a certain letter. Although Access does not allow filters on combo boxes, if you base your combo box on a parameter query, you can filter the values by using a criteria clause.

To create a combo box that displays only the suppliers that meet the criteria that you specify, follow these steps.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

 Open the sample database Northwind.mdb.  Create the following query based on the Suppliers table to use as the row source in the combo box:   Query: Filtered Supplier List -  Type: Select Query

Field: CompanyName Table: Suppliers Sort: Ascending Criteria: Like [My Criteria Box] &"*"   Create the following macro to update the combo box with new criteria:   MacroName: CB Test Macro Action: Requery ControlName: My Combo Box   Create the following form not based on any table or query to test your controls:   Form: TestForm Caption: Test Filtered Combo Box

Combo Box -  Name: My Combo Box RowSourceType: Table/Query RowSource: Filtered Supplier List

Text Box ---  Name: My Criteria Box After Update: CB Test Macro  Open the TestForm form in Form view, and click the arrow in the combo box. Note that all suppliers appear in the list.</li> Type a single letter in the text box (for example, the letter "E").</li> Click the arrow in the combo box. Note that only suppliers whose names begin with the letter "E" are displayed.</li> Type a new letter in the text box (for example, the letter "P").</li> Click the arrow. Note that although the combo box value still shows a company starting with "E" (if you selected one), only suppliers whose names begin with the letter "P" are displayed in the list.</li></ol>

<div class="references_section">