Filtering and Grouping
Eventually, you are going to need to be able to dynamically change the content or format of the data contained in reports. While your needs may vary, here are some common methods for accomplishing simple filtering and grouping.
The simplest way to filter for a single parameter is to put a criteria expression in the query, like this:
SELECT * FROM tblDownloads WHERE tblDownloads.Type = [Enter Type of Download]
The problem with this approach is that your users need to know what "types" are available. Wouldn't it be nicer to offer them a drop-down list of options? You can easily do this by creating a form as shown in the image below, but then your query must be modified to reference this control value, as follows:
SELECT * FROM tblDownloads WHERE tblDownloads.Type = Forms!frmFilter!cboType
This form is included in the download for this article, so you can examine it yourself, but it has a combo box that looks up valid Type values so that users can simply select a valid value. It stores that value in the control named "cboType".
On the other hand, how do you get this form to "pop up" at the right moment? Again, there are various ways to accomplish that, but here's a very simple approach: Load the form in the Report's Report_Open() event. The trick necessary to make this process work is that you open the form in Dialog mode.
In Dialog mode, the form halts all other processing until it is either closed or hidden. In our case, we cannot close it or we will lose the value of cboType, so the OK button simply hides the form, which allows the report to continue opening. As it does, its RecordSource, our query described above, is filtered for the selected type. The code is simple, and looks like this:
Private Sub Report_Open(Cancel As Integer) On Error Resume Next) DoCmd.OpenForm "frmFilter", , , , , acDialog) End Sub
If you want to filter more than one field, then simply add combo boxes to the filter form and additional parameter criteria to the query. You can even pass sorting information, as shown in the image below of a much more complicated filter criteria form. The code behind this example is too complicated to describe here, but you may examine it yourself by downloading the Report Filter Example code file.
One last trick: Dynamic Grouping. The idea here is to create a report with detail records and group totals but determine at run-time whether or not you want to display the detail lines or not.
We use the same method as above, but instead of altering a query, we add code to the Report_Open() event to show or hide the detail section. In the example below, I have renamed the detail section to Detail_001 (by default, it is named "Detail"). This is just to illustrate that each report section is really just a control, whose properties you can modify. Notice the code required to hide the detail section:
Private Sub Report_Open(Cancel As Integer) On Error Resume Next DoCmd.OpenForm "frmOption", , , , , acDialog blnHideDetail = CBool(Nz(Forms!frmOption.cboType, False)) If blnHideDetail Then GroupFooter1.Height = 315 Detail_001.Visible = False End If End Sub
So, based on the user's selection, the detail section is either left untouched (visible) or is hidden. The result is a report with group totals only. You now have effectively delivered two reports to your users: one with detail records and one without. I've saved the report output as snapshot files, which you can examine for yourself:
There is just so very much you can do with Access reports. What I have shown here only scratches the surface and your current needs may vary, but the ideas described in this article and the associated code will get you started. The rest is limited only by your imagination. All the code for this article is available below. Download it, play around with it, and soon you will come up with some tricks of your own.