Hiding Report Lines Conditionally-3

Continued from the last two weeks' topics.

This article is a continuation of two earlier posts on the same subject. You may visit them by following the links below, before proceeding further.

1. Hiding Report Lines Conditionally
2. Hiding Report Lines Conditionally-2.

We have learned how to hide report items conditionally and calculate sub-totals and report footer totals in code by excluding the values of records removed from the report’s Detail section. We then updated the derived values in the Group Footer and Report Footer section controls.

We accomplished this by checking for specific values in the report’s source data and by canceling the Format event of the report.

Previously, we applied these techniques only to the Detail section by hiding specific records. However, our current goal is to hide all data for a particular customer group—including the Customer Group Header, Group Footer, and Detail sections.

Hiding Report Group Entries.

If you understood the methods used in the last two examples, you should have no difficulty following this one. The only difference here is that we will compare the group value (CustomerID) across all three sections—the Group Header, Detail, and Group Footer—within their Format event procedures, to hide or show them as needed, rather than applying the logic only to the Detail section as we did earlier.

Make a copy of the previous report (Order_Details) and save it as Order_Details2.
Open the report in Design View, and we’ll make a small change in the Group Footer section:

1. Click the label with the caption Sub-Total to select it.

2. From the Format menu, choose Change To → Text Box.
   This converts the label control into a text box, allowing us to display an expression displaying the CustomerID value of each customer group, along with the previous “Sub-Total” caption.

3. Display the Property Sheet (View → Properties) for the new text box.

4. In the Control Source property, enter the following expression:

   =[CustomerID] & " Sub-Total: "
   

After this change, the report output will display something like:
BSBEV Sub-Total: 123

For our example, the CustomerID code will be sufficient. However, when designing reports for real projects, you should use something more descriptive, such as the Customer Name, instead of the code.

We’ll be using this report for another trick later on, in addition to what we’re doing here. So take this exercise seriously—you’ll soon see why it’s important.

Implement Code Changes

1. Display the Code Module of the Report (View ->  Code).

2. Delete the existing Code from the Module.

3. Copy and Paste the following Code into the Report Code Module and Save the Report.

   Option Compare Database
   Dim x_subtot As Long, x_gtot As Long
   
   Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
   If [CustomerID] = "BSBEV" Or [CustomerID] = "CENTC" Then
     Cancel = True
   Else
      Cancel = False
   End If
   
   End Sub
   
   Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   If [CustomerID] = "BSBEV" Or [CustomerID] = "CENTC" 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_subtot = x_subtot + [Quantity]
           x_gtot = x_gtot + [Quantity]
       End If
   End Sub
   
   Private Sub GroupFooter0_print(Cancel As Integer, PrintCount As Integer)
       If PrintCount = 1 Then
           [subtot] = x_subtot
           x_subtot = 0
       End If
   End Sub
   
   Private Sub ReportFooter_print(Cancel As Integer, PrintCount As Integer)
   If PrintCount = 1 Then
       [gtot] = x_gtot
   End If
   End Sub
   

4. Open the Report in Print Preview and check whether these Customer Group Header, Detail, and Group Footer Sections are hidden from the Report or not. Check the sample image given below.

Right-click to open Large Image in New Tab/Window.

In the VBA code above, we have intentionally removed the code for the Group Footer Format event procedure so that only the Group Footer for the CustomerID values BSBEV and CENTC will appear. This allows us to observe the position of these group items on the report and see where the Group Header and Detail sections are hidden from view.

Another important point to note is that, since we canceled the Format event for the Detail section of these customer groups, the Print event did not occur in the Detail section. As a result, the group summary totals for these two customer codes are displayed as zeros.

Copy the following code and paste it at the bottom of the Code Module and save the Report.

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
     If [CustomerID] = "BSBEV" Or [CustomerID] = "CENTC" Then
           Cancel = True
     Else
           Cancel = False
     End If
End Sub

Note: If you’re unsure how to correctly name the subroutine and parameters for each section of the report, don’t worry—MS Access will handle that for you. In Design View, click on the section header or footer of the report, open the Property Sheet (View → Properties), set the Event Procedure for either the Format or Print event, and then click the Build ( … ) button on the right edge of the property sheet.

Access will automatically create the subroutine header (with the correct section reference and parameter list) and the corresponding End Sub line in the report’s code module. You only need to manually write the lines of code between them.

Preview Report after Code Changes.

Open the report again in Print Preview, and this time you’ll notice that the Group Footer for these customers is also hidden.

In this example, we’re checking for the customer codes BSBEV and CENTC in all three sections of the report within the Format event procedure to hide those sections. Once the Format action is prevented from running, the Print event does not occur, and therefore, the quantity values for those items are not added to the totals.

If you examine the code structure closely, you’ll see that we’re using both the Format and Print event procedures for the Detail and Group Footer sections. The Format event is used to hide or display report content based on specific criteria, while the Print event is used for calculations or updating the group total controls. For the Report Footer section, we’ve used only the Print event procedure.

Next, we will learn how to open the same Report in two different Modes, i.e., as a Detail Report and as a Summary Report.

• Change Form Modes On User Profile
• Positioning POPUP Forms
• Synchronized Floating Popup Form
• Forms and Custom Properties
• Control Tip Text and Time Delay