Hiding Report Lines Conditionally-2 ~ LEARN MS-ACCESS TIPS AND TRICKS

Continued from Last Week's Topic.

Last week, we began exploring how to hide data lines in the Report Detail section and worked through a simple example. However, we haven’t yet examined how this action affects the normal calculations in a report, such as computing group-wise sub-totals or report footer totals.

We’ve learned that this can be handled with a simple VBA code solution—provided we understand how MS Access processes data during the Format and Print actions before displaying the final results.

Handling Summary Information.

This week, we’ll work with a sample report to examine how MS Access calculates group-level sub-totals when certain group records or data lines are conditionally hidden from the report using VBA code.

For this example, we’ll use three data tables from the C:\Program Files\Microsoft Office\Office11\Samples\NorthWind.mdb sample database. Import the following Tables into your database:
- Orders
- Order Details
- Products
Open a new Query and display its SQL Window.

Copy and Paste the following SQL String into the SQL Editing Window and Save the Query with the Name Order_DetailsQ:

SELECT Orders.CustomerID,
   [Order Details].OrderID,
   Products.ProductName,
 [Order Details].Quantity
FROM Orders INNER JOIN ([Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID) ON Orders.OrderID = [Order Details].OrderID
WHERE ((([Order Details].OrderID) Between 10248 And 10300) AND ((Left([CustomerID],1)) Between "A" And "L"));

We have used conditions in the Query to select only a few records, enough to limit the number of Pages on the Report to three, so that we can check the accuracy of values appearing in Group-wise Sub-Totals and Report-Total Values.

Click on the Order_DetailsQ query to select it and select Report from the Insert Menu.
Select Report Wizard from the displayed list of options and click OK.
Select all fields from the Available Fields list and move them to the Selected Fields list, and click Next Command Button.
The CustomerID field is already selected for Grouping Levels. If any other Field is appearing on the Report sample View, then click the < Button to remove it and select the CustomerID Field from the Field List and click > Button to use it as Group Level and click Next.
Click on the Summary Options Command Button and insert a check mark under the Sum option in the Quantity Field, click OK, and then click Finish to create the Report.
Save the Report with the name Order_Details.
Open the report in Print Preview and check the sub-totals and report footer total. The report appears similar to the sample image shown below. If you have access to a printer, go ahead and print it—it’s only three pages long. This will allow us to compare the results later when we hide the report lines using the program.
Sample Report Image.
Right-click to open Large Image in New Tab/Window.
We will hide the data lines for OrderID numbers 10280 (three records) and 10297 (two records) from the customer groups BERGS and BLONP, respectively. Let’s see how this affects the sub-totals and report totals when these five lines are excluded from the report display.
Open the Report in Design View.
Display the Code Module (View --> Code) of the Report.
Copy and paste the following Code into the Module, save and close the Report.
```
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [OrderID] = 10280 Or [OrderID] = 10297 Then
      Cancel = True
Else
      Cancel = False
End If
End Sub
```
Read the earlier Article with the Title: hiding-report-lines-Conditionally to learn how to use a parameter table to add several OrderIDs to filter, instead of Constant values in the If...Then statement.
Print Preview of Report.
Open the Report in Print Preview.
Cross-check the sub-totals for CustomerIDs BERGS and BLONP with the printout you took earlier.
Even though we have removed three entries from the first customer and two from the second, there is no change in the group sub-totals or report footer total. The code only prevents these data lines from appearing on the report—MS Access still includes them in the summary totals.
The label above the summary totals, reading “Summary for CustomerID = BERGS (7 records)”, is also misleading. Before addressing the summary totals, we’ll remove this label, as we don’t want to add extra code to update its value.
Open the Report in Design View and delete the Text Box with the expression that shows the above Summary message.

Hiding Report Detail Section or Cancel Report Line Formatting

Before we proceed with corrective actions to display subtotals and report totals correctly, I want to draw your attention to the code used in this example.

If you tried last week’s example, you’ll notice some differences between the two VBA codes. In last week’s method, based on the selected OrderID criteria, we hid the Detail section using the statement:


Report.Section(acDetail).Visible = False

This was done during the execution of the report line Format event.

In this example, instead of hiding the Detail section, we instruct MS Access to cancel the report line Format action. As far as the report output is concerned, both approaches produce the same result.

You can run another experiment by executing the same code under the Print event procedure to observe the difference. I’ll provide the code below, which you can copy and paste into the report module, overwriting the earlier code.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If [OrderID] = 10280 Or [OrderID] = 10297 Then
   Cancel = True
Else
      Cancel = False
End If
End Sub

Open the report in Print Preview and examine the area for the records with the OrderIDs used in our criteria. Even though the data lines are suppressed from appearing on the report, the empty space for those lines remains visible.

We will now move on to calculate the sub-totals and the report footer total. Specifically, we need to calculate the quantity totals while excluding the values for orders 10280 and 10297, and write these corrected totals directly into the sub-total and report footer text boxes. Let’s see how this is done.

Performing Summary Calculations.

Open the Report in Design View.
Remove the expression =Sum([Quantity]) from the Text Box Control Source Property in the CustomerID Group Footer and in the Report Footer Sections.
Click on the Group Footer Sub-Total Text Box and display its Property Sheet (View --> Properties).
Change the Name Property Value to SubTotal.
Similarly, change the Name Property Value of Page Footer Text Box to GTotal.

Copy and paste the following Code into the Report Module, replacing the earlier Code.

'global declarations
Dim x_SubTotal As Long, x_GTotal As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [OrderID] = 10280 Or [OrderID] = 10297 Then
    Cancel = True
Else
    Cancel = False
End If
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then
    x_SubTotal = x_SubTotal + [Quantity]
    x_GTotal = x_GTotal + [Quantity]
End If
End Sub

Private Sub GroupFooter0_print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then
    [SubTotal] = x_SubTotal
    x_SubTotal = 0
End If
End Sub

Private Sub ReportFooter_print(Cancel As Integer, PrintCount As Integer)
If PrintCount = 1 Then
    [GTotal] = x_GTotal
End If
End Sub

Save the Report and open it in Print Preview, and check whether the Sub-Totals and Report Footer Totals are appearing correctly now or not.

In the code above, we prevent the report from displaying the record lines in the Format event, while performing the calculations in the report’s Print event. We check whether the PrintCount parameter value is 1 to ensure the totals are calculated correctly. Note that the Print event can occur multiple times if you navigate from one page to an earlier page using the Retreat event.

The Report Summary Calculation Issues.

So far, everything has gone well, but there is a major problem with this method, and the User must be aware of this to avoid undesirable results.

If you jump directly to a page—for example, from page 2 to the last page by typing the page number in the report’s page control in Print Preview—the line-by-line calculation on each page will fail, and the report footer totals will be incorrect. Therefore, if the user is unaware of this issue and navigates through the report pages in preview before printing, it can lead to incorrect totals.

To work around this problem, we can use any of the following methods:

Use Custom Menus and Toolbars in your application and use separate options for Printing and Previewing the Report.
Refer to the following Articles to learn more about Custom Menus and Toolbars.
A Different Approach.
I recommend the above method rather than the second option given below.
Disable Print Command Buttons from the File Menu and from the Report Preview Menu while previewing this particular Report, and enable them again when the Preview is closed. To do this, add the following Code to the VBA Module of the Report:
```
Private Sub Report_Close()
   CommandBars("File").Controls("Print...").Enabled = True
   CommandBars("Print Preview").Controls("Print").Enabled = True
End Sub

Private Sub Report_Open(Cancel As Integer)
   CommandBars("File").Controls("Print...").Enabled = False
   CommandBars("Print Preview").Controls("Print").Enabled = False
End Sub
```
If you are using MS-Access 2000, use the same Control name, Print in both lines of Code. The above code is written for MS Access 2003.
To send the Report directly to the Printer, you may use either a Print Macro or the following line of code to run from a Command Button Click Event or from a Listbox-based Menu Option.
DoCmd.OpenReport "myReport", acViewNormal
To be on the safe side, use this method only on two-page reports.