Help and Support

ACC2000: Base Combo Box on Parameter Query to Filter Values

View products that this article applies to.
Article ID:209542
Last Review:June 29, 2004
Revision:2.0
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.

Back to the top

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.

1.Open the sample database Northwind.mdb.
2.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] &"*"
3.Create the following macro to update the combo box with new criteria:
   MacroName: CB Test Macro
   ------------------------
   Action: Requery
   ControlName: My Combo Box
4.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
5. Open the TestForm form in Form view, and click the arrow in the combo box. Note that all suppliers appear in the list.
6.Type a single letter in the text box (for example, the letter "E").
7.Click the arrow in the combo box. Note that only suppliers whose names begin with the letter "E" are displayed.
8.Type a new letter in the text box (for example, the letter "P").
9.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.

Back to the top

REFERENCES

For more information about creating combo boxes, click Microsoft Access Help on the Help menu, type creating and editing combo boxes in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about creating criteria queries, click Microsoft Access Help on the Help menu, type creating criteria queries in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Back to the top

APPLIES TO
•Microsoft Access 2000 Standard Edition

Back to the top

Keywords: 
kbinfo kbusage KB209542

Back to the top

Article Translations

 

Related Support Centers

Other Support Options

  • Contact Microsoft
    Phone Numbers, Support Options and Pricing, Online Help, and more.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.