Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Hiding Report Lines Conditionally-3

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.

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

  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 in the Group Footer section.
  3. Click on the Label with the Caption Sub-Total to select it.
  4. 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.

  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.

    Implement Code Changes

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


1 comment:

  1. Great information! I’ve been looking for something like this for a while now. Thanks!


Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code