How to create a flexible report selection form in Microsoft Access

This FAQ details an easy way of creating a form in Access with selection criteria to run a report with your own criteria. The user will be able to select as many or few fields as they require, and there are a few tips on making it more advanced. It should be considered a way of extending the features detailed in http://www.tek-tips.com/faqs.cfm?spid=702&sfid=3431.

To begin with, you must have a report to run. This should run and display all data to which it relates if double clicked in the Reports tab or section of the Access database window. If it asks for information as a result of being based on a parameter query, then please take this out so it will use all details by default.

Next, on a piece of paper make a list of the fields that you want to select from, their data types in the underlying table or query and how they are going to be represented on your form (text box, combo box, tick box etc). If a combo or list box, also list the source of records for it. Also note down the name of the report it has to run.

Create a new unbound form pressing escape to bypass the wizard, set the form's Border Style property to Dialog and add each of the items listed in your design document. Name them after the appropriate fields with the appropriate Reddick VBA prefix (or any other naming convention that you use). This makes it easier to understand the underlying VBA code. So for example, you may have txtDistrict for a text box relating to districts, cboKeyword for a combo box list of keywords or lstOrders for a list box showing orders.
Ensure that every field on the form is appropriately labelled as well, as it looks professional and also aids screen reading software for visually impaired users.

Add two CommandButtons to the form either at the bottom or side, depending upon the layout of your form: cmdCancel with caption Cancel and the Cancel Button property set to True and cmdRunReport with caption Run Report and the Default Button property set to True.

Set the form's caption property to "Selection Criteria" or something similar and that if you go into View form, the tab order runs from left to right, top to bottom. If not, go back into design and correct this by using the Tab Index properties on each control. Check also that any combo and list boxes contain the correct data if dropped down and that it is sorted appropriately. You may also want to make sure any combo boxes that take data from lookup tables have the "Limit To List" property on their Data tab set to Yes, to stop the user putting something in that is not in the list. You could also put in a simple messagebox to display a custom error if they tried to do this.

Right click on cmdCancel and choose Build Event and then Code Builder. In the VBA editor type the following line:

Private Sub cmdCancel_Click()
End Sub

This is all there is to the cancel button.

For the main Run Report button, I have several code snippets appropriate for different circumstances for what you want to do. It is a good idea to work out what you want to do on a piece of paper or copy of Notepad first. For each control from the top down to the bottom, decide what you want to do and copy the appropriate code segment, then adapt it to the control and field name.

For a checkbox to ensure if it is true or false:

For a textbox to provide an exact match against one text field:

For a numeric variable with an exact match against one field:

For a single match against a date/time field:

A more complex structure with a Fromà and Toà box matched against one specific field can be created using the following structure (in this case, a date variable, but can be adapted easily using the following information):


The pieces in brackets starting Me, refer to controls on the form such as text boxes, combo boxes and list boxes. The "datefield, textfield, boolfield and numfield" items inside the double quotes refer to the field name in the underlying data or query so must be changed to reflect those.

If you are a newcomer to VBA programming, you may wonder why hashes have been put around the values of dates and apostrophes around strings but nothing around numeric or true/false values. This is because computers work with numbers far better than strings or dates, and that Boolean (true/false) values are also stored as numeric data.

Hash characters are VBA's method of delimiting date/time values, and apostrophes are the same for strings. If there is a likelihood of there being an apostrophe in your string, you can put double quote marks at each end to get around this problem. Thus:

strWhere = strWhere & " AND textfield = """ & Me.txtTextfield & """

With text fields, you can use the word LIKE instead of = and then use wildcards * to match multiple characters and ? to match singles, to provide a more flexible search system. So you could have

strWhere = strWhere & " AND textfield LIKE " & Me.txtTextfield & "*" to match anything that starts with the contents of the textbox.

By now, you should have a basic idea of what to do, so copy the template below into your code and change the reportname to that of your report.
Copy and paste the code from Notepad and run Tools -> Compile this module to check for basic syntax problems. Switch to Form view and try out selecting a few values and clicking Run Report. Note that you can close the form by clicking Escape (it triggers the Cancel Button) and run the report by pressing Enter (because of the Default Button).


This code above will open the report in a print preview mode, which gives you the option to view it before printing. You could change acViewPreview to acViewNormal and it will print on the default printer when the button is pressed.

All you have to do now is put a DoCmd.OpenForm line in your code where you want to open your selection screen.