Access parameterised reports allow you to prompt the database user for variable criteria (parameters) that change the underlying query upon which a report is based. The advantage is that the user does not need to amend the underlying query, so the database structure can remain hidden, ensuring it remains intact. In this example, the database includes a report that gathers mailing data based on a month of birth (Hebrew months). The Report is linked to a form that has the available parameters for the user to choose. The Query that underlies the report then runs based on the user choice, and the report data is displayed. This method represents the easiest way for a user to change the Report parameters.
To begin with, a table has been added to the database called 'LookUpHebrewMonths'. This is a non-indexed table that does not form part of any relationships, and simply has the Hebrew Months listed (Tishrei, Cheshvan, Kislev, Tevet, Shevat, Adar, AdarI, AdarII, Nissan, Iyar, Sivan, Tammuz, Av and Elul).
A form has been designed that uses the values in the LookUpHebrewMonths table. This includes an Unbound List Box called HebrewMonths.
The properties of the List box use SQL to get the list of Months from the LookUpHebrewMonths table.
The small form is only for use with the Report, so an optional step is not to allow it to be directly opened. Also to make it invisible as the report runs. The code for achieving these
is shown here, for the small QueryBirthMonths form.
The button on the Query Birth Month Form will run the Report. We now need to design the Query upon which the Report will be based.
The QueryBirthMonth query uses Criteria that is linked to the value of the HebrewMonths listbox on the 'Query Birth Month' form.
All the other data to be included in the Report is extracted from the members table. Here is the design of the Report, which is based on the QueryBirthMonth query.
The criteria for Hebrew Birth Month is taken from the HebrewMonths List box on the 'Query Birth Month' form.
When the Report is clicked, we need to tell Access that the action to take is *not* to simply run the report, but to open the small Query Birth Month form and await instructions. Here is the code for the Report that does this.
IsLoaded is not a built in Access function, so we must add it to the Modules : -
Here is the IsLoadedModule code so that the customised IsLoaded function can be used in the Report_Open event.
We must also close the small form, each time the Report is closed
Here is the code for the Run Query button on the small Query Birth Month form
Summary : When the user clicks the Report, instead of the Report opening, the Report_Open event instructs Access to open small form instead. The Report is closed by the IsLoaded customised function. The listbox on the small form includes values from the look up table. When one of these is selected and the Run Query button is clicked on the small form, the On Click event opens the Query where the criteria is linked to that control, changing the recordset produced by the query according to whichever month was chosen. When the Report runs, the small form is then closed.
Here is the design for the report, which is named 'MemberBirthAnniversaries'
When the report is opened, the action that we need to occur is that the small form opens, giving the user the chance to choose a month from the list box. When the user does this, and clicks the Run Query button, this changes the recordset being produced by the query.
The report can then open based on that recordset.
This concludes the example on using Parameterised Reports in Access.
When the Report is clicked, we need to tell Access that the action to take is *not* to simply run the report, but to open the small Query Birth Month form and await instructions. Here is the code for the Report that does this. 