databasedev.co.uk - database solutions and downloads for microsoft access

Filtering the results of a Microsoft Access query using a Drop-Down combo box:

You should be aware that you can limit the results of a database select query by using particular criteria. The article relating to Microsoft Access Database Query Basics gives a breakdown on applying simple query criteria.

What if you are not too sure about what criteria applies to the particular field - wouldn't it be better to be able to choose this from a list rather than having the standard Microsoft Access input box appear...

You can create a simple Access form, containing a combo box, and base the query criteria on the value chosen in the form as detailed below:

To begin with we will create a table in Microsoft Access containing some values to search on - the table is shown below:

The table that will be used to create the query on.

The next stage is to create the Access query that we will later apply criteria to. The query design, shown below, prior to any criteria being applied:

The query design, prior to any criteria being applied.

Once we have created the query, we then need to create the Microsoft Access database form that we are going to use to apply the criteria to the query. This form will contain the drop-down combo box with a list of values to choose from that will run the query. The form design is shown below:

The form, showing the drop down list of values to choose from.

The row source that this combo box uses is derived from the table and uses the following SQL:

SELECT tblProducts.PartNumber, tblProducts.Description FROM tblProducts;

Once a value is chosen from the drop down list the On_Change event procedure runs using the following code:

Private Sub cboSearch_Change()
    DoCmd.OpenQuery "qryProducts_And_Locations"
    Me.cboSearch = ""
End Sub

The above runs the query and re-sets the search drop down list to a blank selection. What we now need to do is apply the criteria to the query design that will ensure that the filter is applied based upon the value chosen in the form and this criteria is shown below:

The query design with the form criteria applied.

So now, when we choose a value from the drop down list on the form, the query will run and return only the item chosen from the list:

The results of the query once the form criteria has been applied.

You can download an example Microsoft Access 2000 Database that shows this example from the Microsoft Access Queries menu or from the Microsoft Access Downloads menu.