Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Hiding Report Lines Conditionally-3

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.

Share:

1 comment:

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

    ReplyDelete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

DIR Getting File Names From Folder

We all know Dir() Function from the time of Windows DOS Operating System.  This is the first Command introduced to those who sit on a Perso...

Labels

Blog Archive

Recent Posts