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 msaccess forms Animations msaccess animation 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 DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter 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 Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users 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

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts