USING QUERY BY FORM TO CREATE DYNAMIC GROUPINGS FOR THE SAME REPORT
You can actually make dynamic groupings without using code on the report. The report used for this example is named DynamicGroupingsExample. Also used is a form with the same name.
Before you look at the report itself, notice that the form used with the report is similar to the last example. This form contains an option group (optGroupBy) and two command buttons (cmdPreview and cmdClose). Figure 11.7 shows the DynamicGroupingsExample form in Run view.
Figure 11.7. By accessing the optGroupBy option group, you can group the report in different ways.
As with the last example, the Command Button Wizard created both command buttons. Listing 11.5 shows the code for the cmdPreview button, which opens the DynamicGroupingsExample report in Print Preview.
Listing 11.5. Chap11.mdb: Opening the DynamicGroupingsExample Report in Print Preview
Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click
Dim stDocName As String
stDocName = "DynamicGroupingsExample"
DoCmd.OpenReport stDocName, acPreview
Exit_cmdPreview_Click:
Exit Sub
Err_cmdPreview_Click:
MsgBox Err.Description
Resume Exit_cmdPreview_Click
End Sub
|
The report itself does most of the work. The IIf() (immediate if) function looks at the optGroupBy value in three places on the DynamicGroupingsExample form. The first place is in the Sorting and Grouping property sheet (see Figure 11.8).
Figure 11.8. Most people don't realize that you can use an IIf() function to change groupings dynamically.
The following command does the comparison:
=IIf([Forms]![DynamicGroupingsExample]![optGroupBy]=1,[Region],[Country])
If optGroupBy equals 1, the grouping is set to the Region field; otherwise, it's set to the Country field.
The next place that uses IIf() to examine the optGroupBy option group is the txtRegionOrCountry text box, located in the Group Header section on the report. Here's the command used:
=IIf([Forms]![DynamicGroupingsExample]![optGroupBy]=1,[Region],[Country])
As you can see, this command is identical to the one used in the Sorting and Grouping dialog.
The last control to use IIf() is the txtRegionOrCountryLabel text box. It's used to display a heading for the first column. The statement itself is
=IIf([Forms]![DynamicGroupingsExample]![optGroupBy]=1,"Region","Country")
Although this looks similar to the first two uses, rather than display the Region and Country fields, the statement shows the literals Region and Country. Figures 11.9 and 11.10 show the DynamicGroupingsExample report in Print Preview—first with the region chosen and then with the country.
Figure 11.9. By reading this option group on the form, the report can control how it groups the information.
Figure 11.10. By choosing the Country option on the form, IIf() helps control how to group the information on the report.
That's all there is to it. It's nice that not all the tricks need coding. Another trick—actually just a feature people don't think to use—is the snaking report feature.