To use this tutorial you will need to have worked through Part 1 – click here to go to Part 1.
Now that we have created our Form and Query, we now need to start a chain of events that will lead to the retrieval of data based on the Query.
It is unusual, (and bad practice) to have your users see the results of a query directly in the query window, the results will usually be displayed on a form or report. For the purposes of demonstration in this article we will assume that a report called rptAnnualSummary is going to be displayed using the results of the query.
The chain of events that we now need to create are:
- User presses button to run report and Access displays the form frmCritSelection.
- The user selects the criteria from the dropdown box, and clicks the ‘OK’ button.
- The form frmCritSelection opens the report, which automatically runs the query and displays the results.
- The report closes the form frmCritSelection
Step 1
Depending on how your menu system is set up you will need to get the form frmCritSelection on screen, if you use the switchboard you can just set it to open the form in Edit mode. If you are using your own menu system you will need to put the following line of code in the ‘On Click’ event of the object you’re using to trigger the report generation.
DoCmd.OpenForm “frmCritSelection”, acNormal, “”, “”, acEdit, AcNormal
Steps 2/3
Now that we are able to trigger the opening of the form we will need to open the report when the user clicks the ‘OK’ button. To do this we will use some VBA code in the ‘On Click’ event of the ‘OK’ button.
- Open frmCritSelection in design mode
- Right click the ‘OK’ button and select ‘Build Event’, select ‘Code Builder’ from the dialog and you will be passed to the VBA editor already in the ‘On Click’ event of the button.
- In the ‘On Click’ event you will need to place the following line of code.
- DoCmd.OpenReport “rptAnnualSummary”, acViewPreview
What we have now created is a situation where the frmCritSelection form opens and obtains the users choice for the query via the dropdown box. Once the ‘OK’ button is pressed, the form then opens the report, that in turn runs the query, which in the last part we pointed towards the frmCritSelection form’s combo box control.
Step 4
All that remains from our list of the tasks above is to close the now redundant frmCritSelection form. The easiest way to achieve this is to add a line of code to the ‘On Close’ event of the report, so that when the user closes the report, the form also closes with it, a line such as the below will suffice.
DoCmd.Close acForm, “frmCritSelection”
By following the steps in these two tutorials, and customising the object names to suit your situation you will now be able to create a query based report where the user can select the search criteria from a list of pre-defined items