ACC: Base Combo Box on Parameter Query to Filter Values

ID: Q99937


The information in this article applies to:


SUMMARY

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

This article show 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 Microsoft 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.

This article describes the steps to create a combo box that displays only the suppliers that meet the criteria that you specify. The example uses the sample database Northwind.mdb (or Nwind.mdb in versions 1.x and 2.0).

  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
          Field: CompanyName
             Table: Suppliers
             Sort: Ascending
             Criteria: Like [My Criteria Box] & "*" 
    NOTE: In versions 1.x and 2.0, there is a space in Company Name.


  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 unbound form 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 TestForm in Form view and click the arrow on the combo box. Note that all suppliers appear in the list.


  6. Type a single letter in My Criteria Box (for example, the letter E).


  7. Click the arrow on the combo box. Note that only suppliers whose names begin with the letter "E" are displayed.


  8. Type a new letter in My Criteria 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.



REFERENCES

For more information about creating combo boxes, search for "combo boxes," and then "creating and editing" using the Microsoft Access 97 Help Index.

For more information about creating criteria queries, search for "queries," and then "criteria, entering" using the Microsoft Access 97 Help Index.

Additional query words: limit drop down list box ddb


Keywords          : kbusage FmsCmbo 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbinfo 

Last Reviewed: March 23, 1999