Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Hiding Report Lines Conditionally-2

Continued from Last Week's Topic.

Last week, we started the topic of hiding Report Detail Section data lines and worked through a simple example as well. But, we have not yet seen how this action affects the normal calculations on the Report, like calculating Group-wise Sub-Totals or Report Footer Totals.

We have seen that we can do this with a simple VBA Code if we know how MS-Access works on the data Formatting and Printing actions on the Report before it shows the results to us.

Handling Summary Information.

This week we will work with a sample report to see how Group-level Sub-Totals are calculated by MS-Access when some of the group-level records or data lines are conditionally suppressed from showing on the Report with VBA Code.

For this example, we need three data Tables from C:\Program Files\Microsoft Office\Office11\Samples\NorthWind.mdb sample Database.

  1. Import the following Tables into your database:

    • Orders
    • Order Details
    • Products
  2. Open a new Query and display its SQL Window.

  3. Copy and Paste the following SQL String into the SQL Editing Window and Save the Query with the Name Order_DetailsQ:

    SELECT Orders.CustomerID,
       [Order Details].OrderID,
     [Order Details].Quantity
    FROM Orders INNER JOIN ([Order Details] INNER JOIN Products ON [Order Details].ProductID = Products.ProductID) ON Orders.OrderID = [Order Details].OrderID
    WHERE ((([Order Details].OrderID) Between 10248 And 10300) AND ((Left([CustomerID],1)) Between "A" And "L"));

    We have used conditions in the Query to select only a few records, enough to limit the number of Pages on the Report to three so that we can check the accuracy of values appearing in Group-wise Sub-Totals and Report Footer Total easily.

  4. Click on the Order_DetailsQ query to select it and select Report from Insert Menu.

  5. Select Report Wizard from the displayed list of options and click OK.

  6. Select all fields from the Available Fields list and move them to the Selected Fields list and click Next Command Button.

  7. The CustomerID field is already selected for Grouping Levels. If any other Field is appearing on the Report sample View then click the < Button to remove it and select CustomerID Field from the Field List and click > Button to use it as Group Level and click Next.

  8. Click on the Summary Options Command Button and put a Check Mark under the Sum option in Quantity Field, click OK, and then click Finish to create the Report.

  9. Save the Report with the name Order_Details.

    Open the Report in Print Preview and check the Sub-Totals and Report Footer Total. The Report will look like the sample image given below. If you have access to a Printer then take a printout, it is only three pages long so that we can compare the results when we hide the report lines with the program.

    Sample Report Image.

    We are going to hide data lines of OrderID Numbers 10280 (three records) and 10297 (two records) from Customer Groups BERGS and BLONP respectively. Let us see what happens to the Sub-Totals and Report Totals when these five lines are prevented from showing up on the Report.

  10. Open the Report in Design View.

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

  12. Copy and Paste the following Code into the Module, Save and close the Report.

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If [OrderID] = 10280 Or [OrderID] = 10297 Then
          Cancel = True
          Cancel = False
    End If
    End Sub

    Read the earlier Article with the Title: hiding-report-lines-Conditionally to learn how to use a parameter table to add several OrderIDs to filter, instead of Constant values in the If...Then statement.

    Print Preview of Report.

  13. Open the Report in Print Preview.

  14. Check the Sub-Totals of CustomerIDs BERGS and BLONP with the Print out you have taken earlier.

    Even though we have removed three entries from the first Customer and two from the second there is no change in the Group Sub-Totals or Report Footer Total. The Code only prevents the report from showing the data lines on the Report but MS-Access did not exclude these entries from Summary Totals. The Label appearing above the Summary Totals with the text Summary for CustomerID = BERGS (7 records) is also misleading. First thing first, we will eliminate this label before we take care of the Summary Totals. We don't want to add more lines in the Program to update this Label value also.

  15. Open the Report in Design View and delete the Text Box with the expression that shows the above Summary message.

Hiding Report Detail Section or Cancel Report Line Formatting

Before we proceed further with corrective actions for showing Sub-Totals and Report Total I want to draw your attention to the above code that we have used for this example.

If you have tried last week's example, you will see some differences in both VBA Codes. In last week's method that we used, based on the selected OrderID criteria we kept the Detail Section hidden, with the statement Report.Section(acDetail).Visible = False, during the execution of the Report line Format Event.

In this example, we have asked MS-Access to Cancel the Report Line Format action, rather than hiding the Detail Section. As far as the output is concerned both these actions have the same result.

You can run another experiment by running the same Code under the Print Event Procedure and see what difference it makes. I will give the code below. You may Copy and Paste it into the Report Module overwriting the earlier Code.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If [OrderID] = 10280 Or [OrderID] = 10297 Then
   Cancel = True
      Cancel = False
End If
End Sub

Open the Report in Print Preview and inspect the area of the records of OrderIDs we have used in the criterion. Even though the data lines are suppressed from appearing on the Report the empty space of those Report Lines is left of the Report.

We will now proceed to the Sub-Total and Report Footer Total calculation part. That's right; we have to Calculate the Quantity Totals excluding the Values of Orders 10280 and 10297 and write them directly in the Sub-Totals and Report Footer Total TextBoxes. Let us see how we do that.

Performing Summary Calculations.

  1. Open the Report in Design View.

  2. Remove the expression =Sum([Quantity]) from the Text Box Control Source Property in the CustomerID Group Footer and in the Report Footer Sections.

  3. Click on the Group Footer Sub-Total Text Box and display its Property Sheet (View --> Properties).

  4. Change the Name Property Value to SubTotal.

  5. Similarly, change the Name Property Value of Page Footer Text Box to GTotal.

  6. Copy and Paste the following Code into the Report Module, replacing the earlier Code.

    'global declarations
    Dim x_SubTotal As Long, x_GTotal As Long
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If [OrderID] = 10280 Or [OrderID] = 10297 Then
        Cancel = True
        Cancel = False
    End If
    End Sub
    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
        x_SubTotal = x_SubTotal + [Quantity]
        x_GTotal = x_GTotal + [Quantity]
    End If
    End Sub
    Private Sub GroupFooter0_print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
        [SubTotal] = x_SubTotal
        x_SubTotal = 0
    End If
    End Sub
    Private Sub ReportFooter_print(Cancel As Integer, PrintCount As Integer)
    If PrintCount = 1 Then
        [GTotal] = x_GTotal
    End If
    End Sub
  7. Save the Report, open it in Print Preview, and check whether the Sub-Totals and Report Footer Totals are appearing now correctly or not.

In the above code, we have prevented the Report from showing the record lines in the Format Event and the Calculation is done in the Print Event of the Report. We are checking whether the PrintCount parameter Value is 1 or not, to correctly calculate the Totals. The Print Event can take place more than once if you move from a Page to an earlier Page in the Retreat Event.

The Report Summary Calculation Issues.

So far everything went on well, but there is a major problem with this method and it is important that the User must be aware of this to avoid undesirable results.

If you jump directly to a page (say from page 2 to the Last Page by typing the Page Number in the Report Page control below in Preview) then our line by line Calculation on every Page fails and the Report Footer Totals will be wrong. So, if the user is not aware of this issue and jumps through the Report Pages in Print Preview, before printing the Report, then we are in the soup.

To work around this problem we can use any of the following methods:

  1. Use Custom Menus and Tool Bars in your application and use separate options for Printing and Previewing the Report.

    Refer to the following Articles, to learn more about Custom Menus and Toolbars.

    A Different Approach.

    I recommend the above method rather than the second one given below.

  2. Disable Print Command Buttons from File Menu and from Report Preview Menu while previewing this particular Report and enable them again when Preview is closed. To do this, add the following Code to the VBA Module of the Report:
    Private Sub Report_Close()
       CommandBars("File").Controls("Print...").Enabled = True
       CommandBars("Print Preview").Controls("Print").Enabled = True
    End Sub
    Private Sub Report_Open(Cancel As Integer)
       CommandBars("File").Controls("Print...").Enabled = False
       CommandBars("Print Preview").Controls("Print").Enabled = False
    End Sub

    If you are using MS-Access2000 then use the same Control name Print in both lines of Code. The above code is written for MSAccess2003.

    To send the Report directly to the Printer you may use either a Print Macro or the following line of code to run from a Command Button Click Event or from a ListBox-based Menu Option.

    DoCmd.OpenReport "myReport", acViewNormal

  3. To be on the safe side use this method on Reports having only 2 pages.

Next, we will examine how to hide Group level information like Group-Header, Detail, and Group-Footer Sections with Code.


No comments:

Post a Comment

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