Hiding Report Lines Conditionally-2
Last week, we have 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 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.
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:
- Order Details
- 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 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 for 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 print out, it is only three pages long, so that we can compare the results when we hide the report lines with program.
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.
- 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.
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 difference in both VBA Codes. Last Week we have asked MS-Access to hide the Detail Section with the statement Report.Section(acDetail).Visible = False during the Report Format Event when the OrderIDs in the criterion are encountered.
In this example we have asked MS-Access to Cancel the Format action. 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 in 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 are left on the Report.
We will now proceed with 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 it directly in the Sub-Totals and Report Footer Total Text Boxes. Let us see how we do that.
- 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.
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 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 the following Articles to learn more about Custom Menus and Toolbars.
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 into the 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.
DoCmd.OpenReport "myReport", acViewNormal
- To be on the safest side use this method on Reports having only 2 pages.
Next we will examine how to hide Group level information like Group-Header, Detail and Group-Footer Sections with Code.