This is the continuation of two Articles published earlier on this Subject. You may go through them by following the links given below before continuing from here.

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 values of those records we have removed from the Report Detail Section. We have updated the calculated values in Group Footer and Report Footer Section Controls.

We did this by checking for specific values in the Report Source Data and by Canceling the Format Event of the Report.

These exercises we have done on the Detail Section alone earlier on the Report by hiding specific record. But, here our focus is on hiding all records of a particular Customer Group including the Customer Group Header, Group Footer and Detail Sections.

If you understood the method we have used in the last two examples then you will have no difficulty in understanding it here. The only difference here is to compare the Group Value (CustomerID) in all the three Section’s (i.e. Group Header, Detail and Group Footer Sections) Format Event Procedure to hide or show them rather than Report Detail Section alone as we did in the earlier example.

1. Make a copy of the earlier Report (Order_Details) and save it with the name Order_Details2.

 

2. Open the Report in Design View. We will make a small change on the Group Footer Section.

 

3. Click on the Label with the Caption Sub-Total to select it.

 

4. Select Change To from Format Menu and select Text Box from the displayed options.

    

   The Label Control we have now changed to a Text Box to write an expression to display the CustomerID value of each Customer Group along with the earlier caption Sub-Total.

    

5. Display the Property Sheet (View- – >Properties) of the Text Box.

 

6. Write the following Expression in the Control Source Property of the Text Box.

    

   =[CustomerID] & " Sub-Total: "

    

   The result of this change will appear on the Report something like BSBEV Sub-Total: 123.

    

   CustomerID code will be enough for our example here but when you design some Reports in your Projects then it should be something descriptive like the Customer Name rather than Customer Code.

    

   We will be using this Report for some other trick later, besides what we are going to do with it here, so take it seriously and you will know why it is so important.

 

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

 

8. Delete the existing Code from the Module.

 

9. 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
   

    

10. 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.

 

From the VBA Code above we have purposely removed the Code for Group Footer Format Event Procedure so that the Group Footer alone for the CustomerID BSBEV and CENTC will appear. We can now see the position of these Group items appearing on the Report and from where the Group Header and Detail Sections are hidden from our view.

Another point to note is that since we have Canceled the Format Event of the Detail Section of these Customer Groups the Print Event has not taken place for Detail Section. Because of this the Group Summary Totals for these two Customer Codes are showing as zeroes.

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 are wondering how to name the Sub-Routine and Parameters related to each Section of the Report correctly then stop worrying. This (the Subroutine header line and the End Sub lines) will be written by MS-Access. Click on the Section Header/Footer of the Report in design view, display the Property Sheet (View – - > Properties), set Event Procedure on the Format or Print Event Property, Click on the build (. . .) Button to the right edge of the Property Sheet. Both the Sub-Routine Name (pointing to the correct Section of the Report) with appropriate Parameter list and the End Sub lines will open up in the Code Module of the Report. You only need to write other lines between them manually.

Open the Report again in Print Preview and this time you will find the Group Footer of these Customers are also hidden.

We are checking for the CustomerlD Code BSBEV and CENTC in all the three Sections of the Report in the Format Event Procedure to hide these Sections. Once the Format Action is prevented from happening; the Print Event doesn’t take place and the Quantity Value of those items are not added to the Total.

If you look at the Code structure carefully you can see that we are using the Format Event as well as Print Event Procedures for both Detail Section and Group Footer Sections. Format Action used for hiding or displaying the Report contents based on the criterion and Print Action for Calculations or Updating the Group Total Controls. We have used only Print Event Procedure for Report Footer Section.

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.