USING THE SAME REPORT FOR SUMMARY, DETAIL, AND SUMMARY/DETAIL REPORTS
Usually, when a client asks that data be displayed in summary or detail format, the developer creates two versions of the same report. For this example, three choices are requested, so three reports would be needed: summary, detail, and summary/detail. This section shows you how to do it with just one report.
This example uses the SectionsDisplayExample form, which calls the report of the same name. The form consists of an option group named optDisplay and two command buttons: cmdPreview, which performs the OpenReport method, and cmdClose, which closes the form. Figure 11.1 shows the form in Design view.
Figure 11.1. This form calls a report with three different layouts.
The Command Button Wizard created the command buttons used on this form. This wizard not only provides the graphics for the buttons, but also creates VBA code behind the forms. The Command Button Wizard generated the code for the cmdPreview button (see Listing 11.1).
Listing 11.1. Chap11.mdb: Opening the SectionsDisplayExample Report in Print Preview
The real power in this example is the code behind the SectionsDisplayExample report. Notice in Figure 11.2 that CustomerID and CompanyName show up twice in this report—once in the CustomerID Header section and once in the Detail section.
Figure 11.2. Only one copy of CustomerID and CompanyName actually shows up in the report when it's run.
Before you look at the code behind the report, the other object needed for this report is the qrySectionsDisplayExample query (see Figure 11.3).
Figure 11.3. qrySectionsDisplayExample is a straightforward query used by the SectionsDisplay-Example report.
The code that does the work is attached to the OnOpen event of the report. Listing 11.2 shows the code in its entirety.
Listing 11.2. Chap11.mdb: Setting Up How the Sections Will Look
This code starts by examining the optDisplay option group on the SectionsDisplayExample form and acting appropriately based on the setting. The first setting is for Detail and Summary:
Case 1 '–– Display Detail and Summary Me.Caption = "Freight Charges – Detail & Summary" Me!CustomerID.Visible = False Me!CompanyName.Visible = False
This part of the code sets the report's Caption property to reflect the display choice. Then it turns off the CustomerID and CompanyName fields in the Detail section because the CustomerID and CompanyName are displayed in the CustomerID Header section. Figure 11.4 shows the SectionsDisplayExample report in Print Preview with the Detail & Summary option chosen.
Figure 11.4. With an option group and an OnOpen event, you can have almost unlimited views of a particular report.
The next part of the code sets up the report to display only the Detail section. The first task sets the caption up correctly and then turns off the Group Header and Footer sections.
Note
Reports and forms have a Section property that's actually an array of all the sections for that object. The following table lists the standard elements of the Section array. If more than two groupings are on a report, they continue by pairs from 9 on.
| Section Index | Description of Section |
|---|---|
| Section(0) | Form detail section or report detail section |
| Section(1) | Form or report header section |
| Section(2) | Form or report footer section |
| Section(3) | Form or report page header section |
| Section(4) | Form or report page footer section |
| Section(5) | Group-level 1 header section (reports only) |
| Section(6) | Group-level 1 footer section (reports only) |
| Section(7) | Group-level 2 header section (reports only) |
| Section(8) | Group-level 2 footer section (reports only) |
Each section has its own set of properties, such as the Visible property used in this example.
Listing 11.3 shows the code section that displays the Detail section only; Figure 11.5 shows the resulting report.
Figure 11.5. This version of the report displays just the detail information.
Listing 11.3. Chap11.mdb: Controlling Aspects of Each Report Section
The last choice given is Summary Only; Listing 11.4 shows the code for it. All column headings, except the Freight column, are set to not visible, and the Detail and Group Header sections are turned off.
Listing 11.4. Chap11.mdb: Setting Up Sections for Summary Only
Case 3 '–– Display Summary Only Me.Caption = "Freight Charges – Summary Only" '–– Turn off Column Headings Me!CustomerIDLabel.Visible = False Me!CompanyNameLabel.Visible = False Me!OrderDateLabel.Visible = False Me!OrderIDLabel.Visible = False '–– Turn Detail Section off Me.Section(0).Visible = False '–– Turn Group Header Section off Me.Section(5).Visible = False |
Figure 11.6 shows the SectionsDisplayExample report in Print Preview, with the Summary Only option chosen.
Figure 11.6. This version of the report displays just the summary information, with most of the column headings and the Detail and Group Header sections turned off.
You can toggle the visibility of sections even further by having sections of reports created by using subreports on the main report, toggled as visible or invisible at runtime based on the user's choice. Another nice technique is the ability to switch fields on which report groupings are performed. This technique is explored next.