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

Continued from last two weeks' topics.

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.

We have learned how to hide Report items conditionally and calculate Sub-Totals and Report Footer Totals in Code by excluding the 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 in the Report by hiding specific records. But, here our aim is to hide all the records, of a particular Customer Group, including the Customer Group Header, Group Footer, and Detail Sections.

Hiding Report Group Entries.

If you understood the method we have used in the last two examples, then you will have no difficulty understanding it here. The only difference here is to compare the Group Value (CustomerID) in all the three Sections (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.

Make a copy of the earlier Report (Order_Details) and save it with the name Order_Details2.
Open the Report in Design View. We will make a small change in the Group Footer section.
Click on the Label with the Caption Sub-Total to select it.
Select the Change To option 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.
Display the Property Sheet (View- - >Properties) of the Text Box.
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.
Implement Code Changes
Display the Code Module of the Report (View - - > Code).
Delete the existing Code from the Module.

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

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 in the Detail Section. Because of this, the Group Summary Totals for these two Customer Codes are shown 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 the design view, display the Property Sheet (View - - > Properties), set Event Procedure on the Format or Print Event Property, and 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 the 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.

Preview Report after Code Changes.

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

We are checking for the CustomerlD Code BSBEV and CENTC in all 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 is 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 is 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 the 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.