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

Continued from Last Week's Topic.

Last week, we started the topic of hiding Report Detail Section data lines and worked through a simple example as well. But, we have not yet seen how this action affects the normal calculations on the Report, like calculating Group-wise Sub-Totals or Report Footer Totals.

We have seen that we can do this with a simple VBA Code if we know how MS-Access works on the data Formatting and Printing actions on the Report before it shows the results to us.

Handling Summary Information.

This week we will work with a sample report to see how Group-level Sub-Totals are calculated by MS-Access when some of the group-level records or data lines are conditionally suppressed from showing on the Report with VBA Code.

For this example, we need three data Tables from 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 Footer Total easily.

Click on the Order_DetailsQ query to select it and select Report from 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 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 put a Check Mark under the Sum option in 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 will look like the sample image given below. If you have access to a Printer then take a printout, it is only three pages long so that we can compare the results when we hide the report lines with the program.
Sample Report Image.
We are going to hide data lines of OrderID Numbers 10280 (three records) and 10297 (two records) from Customer Groups BERGS and BLONP respectively. Let us see what happens to the Sub-Totals and Report Totals when these five lines are prevented from showing up on the Report.
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.
Check the Sub-Totals of CustomerIDs BERGS and BLONP with the Print out you have taken 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 the report from showing the data lines on the Report but MS-Access did not exclude these entries from Summary Totals. The Label appearing above the Summary Totals with the text Summary for CustomerID = BERGS (7 records) is also misleading. First thing first, we will eliminate this label before we take care of the Summary Totals. We don't want to add more lines in the Program to update this Label value also.
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 further with corrective actions for showing Sub-Totals and Report Total I want to draw your attention to the above code that we have used for this example.

If you have tried last week's example, you will see some differences in both VBA Codes. In last week's method that we used, based on the selected OrderID criteria we kept the Detail Section hidden, with the statement Report.Section(acDetail).Visible = False, during the execution of the Report line Format Event.

In this example, we have asked MS-Access to Cancel the Report Line Format action, rather than hiding the Detail Section. As far as the output is concerned both these actions have the same result.

You can run another experiment by running the same Code under the Print Event Procedure and see what difference it makes. I will give the code below. You may Copy and Paste it 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 inspect the area of the records of OrderIDs we have used in the criterion. Even though the data lines are suppressed from appearing on the Report the empty space of those Report Lines is left of the Report.

We will now proceed to the Sub-Total and Report Footer Total calculation part. That's right; we have to Calculate the Quantity Totals excluding the Values of Orders 10280 and 10297 and write them directly in the Sub-Totals and Report Footer Total TextBoxes. Let us see how we do that.

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, open it in Print Preview, and check whether the Sub-Totals and Report Footer Totals are appearing now correctly or not.

In the above code, we have prevented the Report from showing the record lines in the Format Event and the Calculation is done in the Print Event of the Report. We are checking whether the PrintCount parameter Value is 1 or not, to correctly calculate the Totals. The Print Event can take place more than once if you move from a Page to an earlier Page in the Retreat Event.

The Report Summary Calculation Issues.

So far everything went on well, but there is a major problem with this method and it is important that the User must be aware of this to avoid undesirable results.

If you jump directly to a page (say from page 2 to the Last Page by typing the Page Number in the Report Page control below in Preview) then our line by line Calculation on every Page fails and the Report Footer Totals will be wrong. So, if the user is not aware of this issue and jumps through the Report Pages in Print Preview, before printing the Report, then we are in the soup.

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

Use Custom Menus and Tool Bars 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 one given below.
Disable Print Command Buttons from File Menu and from Report Preview Menu while previewing this particular Report and enable them again when 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-Access2000 then use the same Control name Print in both lines of Code. The above code is written for MSAccess2003.
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 on Reports having only 2 pages.