A common question from Access developers is ‘How can I have a query that has a ‘drop-down’ to specify the criteria?’ Whilst Access has no actual function built in to the query builder, it is possible to add this functionality with just a little bit of work.
This tutorial assumes that you are already conversant with designing forms, queries and tables in Access, and that you have some knowledge of VBA programming. The tutorial also uses various names of forms and tables for the use of demonstration, you will need to substitute your own table, form, query and control names as necessary.
The first step is to create a table with the options that you want for your drop down list (Assuming they are not already held in a table somewhere), and for the purposes of this tutorial we will assume that the table is called tblChoices and the field name where the options are held is called Selection
Next we will need to design a form that will be used to make the selection, this will need to be an unbound form, as it will not update any data tables. On this form you will need to place three objects, A combobox control that will be used to select the query criteria, and two command buttons, one to accept the selection, and one to cancel the form. Your completed form will probably look something like the one below, save it with the name frmCritSelection
Ok, now that we have a selection form we need to link the combobox to the Selection field in tblChoices, to do this we need to open the form in design mode, select the combobox and bring up the properties window.
Once the properties window is displayed, click the All tab, and on approximately the 4th row down you will see an option to specify a Row Source. In here we will need to enter the target for the choices in the drop down box, so enter the following phrase:
SELECT tblChoices.Selection From tblChoices;
While we are here you should also give the combobox a meaningful name, we will call ours CritSelBox. Once this is done you can save the changes to your form and close it.
The next step is to link the query that this parameter is passed to, to the form. In the design mode for the query, you will need to find the field which you are querying, and enter the following phrase in the criteria field
Now that you have completed this first part of this tutorial you will need to make the buttons on your form perform the actions required. This is explained in Part 2 of this article.