Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Hiding Report Lines Conditionally

Introduction.

Hiding Report lines or Report Sections conditionally at run time may not be an everyday requirement. But, if it becomes necessary, it is interesting to know the method to do it, with the help of Programs. One thing I like very much about MS-Access Report is its ability to write programs on the Report itself, especially for highlighting very critical information on the Report so that the attention of Users can be drawn to specific information on it. You can see an interesting and useful example in one of the earlier Posts with the Title: Highlighting Reports

To suppress something on the Report from printing on the Report we normally think about using a Query to filter out unwanted items from Source Data before Previewing or Printing the Report. Frankly, Query is the best method compared to what I am trying to show you here. To get the same result on the Report without filtering the source data we have to play around with a few tricks (after all, this is all about Tips & Tricks, right?). 

If you really enjoy working with MS-Access programs, then read on. As far as the program code is concerned, there is nothing complicated to deal with, all we need is a few lines of simple code and even a VBA beginner can easily understand them.

It is interesting to do something different, which not only kills the boredom of doing the same thing repeatedly but also gives you better insight into doing things better next time.

Design a Sample Report.

We will create a sample Report quickly to try out this Trick.

  1. Import Orders and Employees, Tables from C:\Program Files\Microsoft Office\Office11\samples\Northwind.mdb sample Database.

    We will not be using the Employees Table directly on the Report. Without the Employees Table in the Database, only Employee IDs will be displayed on the Report when the Report is designed with Orders Table. There is a reference to the Employees Table (Combo Box uses Employees Table) in the EmployeeID Field in Orders Table.

  2. Click on the Orders Table and select a Report from Insert Menu.
  3. Select Report Wizard from the displayed list and click OK.
  4. Select the following Data Fields from the Available Fields list and move them one by one to the Selected Fields List:
    • OrderID
    • CustomerID
    • EmployeeID
    • OrderDate
    • RequiredDate
  5. Click Next and select CustomerID as Grouping Level and move it to the right. Click Next Command Button.
  6. Select OrderID for Sorting in the first Sort Control and Click Finish to create the sample Report.

    An image of the sample Report is given below:

  7. Open the Report in Print Preview and check the Order Numbers appearing under the first three Customer Groups on the Report.

    We will attempt to hide three different Orders (Order IDs 10702, 10625 & 10573 from ALFKI, ANATR & ANTON Customer#39;s List respectively) from appearing on the Report with the following simple lines of Code:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If [OrderID] = 10702 Or [OrderID] = 10625 Or [OrderID] = 10573 Then
          Report.Section(acDetail).Visible = False
    Else
       Report.Section(acDetail).Visible = True
    End If
    End Sub

    The OnFormat() Event Procedure

    If you look at the above code you can see that the code runs under the On Format Event Procedure in the Detail Section of the Report.

  8. Open your Report in Design View and display the Code Module (View - - >Code).
  9. Copy and Paste the above Code into the Report Module.
  10. Select Close and Return to Microsoft Office Access from File Menu to close the VBA Window and return to the Design View of the Report.

    Or you can press Alt+F11 to Toggle, between Database Window and VBA Window. Visit the Page Keyboard Shortcuts to learn more about Keyboard Shortcuts.

  11. Click on the Detail Section of the Report and display the Property Sheet (View - - >Properties).
  12. Check the On Format Event Property and you can see that the entry [Event Procedure] is appearing there indicating that the Code that we have pasted Runs on the Format Event of the Report.

    There are two more Events associated with the Report's Printing or Previewing action; Print Event and Retreat Event.

    The Report Formatting Passes

    MS-Access makes two passes over the Report before it is actually displayed or Printed. First, the Format pass lays out each Report Section's contents doing calculations, if needed, for Summary information (we will deal with this in the forthcoming Articles). The Print action takes place in the second pass, which prints the contents of the Report before Previewing or sending it to the Printer.

    The Retreat Event takes place when you move the Page Preview to an earlier Page. At this time the Format action takes place a second time for the Retreated Page and the FormatCount Parameter value in the Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) is incremented by one. This has some negative impact if we are performing some calculations in the above Procedure. This aspect we will examine in the forthcoming Articles.

  13. Save the Report and open it in Print Preview.
  14. Check whether the Report Lines associated with OrderIDs 10702, 10625 & 10573 are still appearing on the Report or not.

The Parameter Table

We were using Constant values for OrderIDs in the Program to check and suppress the Report lines from appearing on the Report. We cannot expect the User to open the Report in Design View and change the Program every time to change the OrderIDs, if it becomes necessary, before printing the Report. Besides, there may be more than two or three Orders to hide this way. We must provide a much simpler method to the User to input the OrderIDs as parameters to the Program and Run the Report based on that.

We will create a parameter table to the key in OrderIDs, read it in the program and compare it to the Report Order IDs and skip those lines on the Report for matching cases.

  1. Create a Table with the name OrderParam with a single Field with the name OID with Data Type Number and Field Size Long Integer.
  2. Select Primary Key from Edit Menu to define this field as a Primary Key Field. This will prevent duplicate values from going into the Parameter Table and makes it easier to cross-check OrderIDs from Report.

    Save the Table and open it in Data Sheet View.

  3. Key in the OrderIDs 10702, 10625, and 10573 (or any other OrderIDs you would like to hide) in the OrderParam Table.

    You may even design a Continuous Form for the Table and create a Command Button at the Footer Section of the Form with HyperLinks to the Orders Report so that the User can launch the Report from the Parameter Screen itself.

    The VBA Code

  4. Copy and Paste the following Code in Report#39;s Code Module, replacing the earlier Code:
    'Global declarations
    Dim cdb As Database, rst As Recordset
    
    Private Sub Report_Open(Cancel As Integer)
         Set cdb = CurrentDb
        Set rst = cdb.OpenRecordset("OrderParam")
        rst.Index = "PrimaryKey"
    End Sub
    
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    rst.Seek "=", [OrderID]
    If Not rst.NoMatch Then
       Report.Section(acDetail).Visible = False
    Else
        Report.Section(acDetail).Visible = True
    End If
    End Sub
    
    Private Sub Report_Close()
       rst.Close
       Set rst = Nothing
       Set cdb = Nothing
    End Sub
  5. Save the Report with the new Code and open it in Print Preview.
  6. Look for the OrderIDs given in the OrderParam Table to check whether they are really suppressed from the Report or not.

Let us examine what we did in the above Code.

The VBA Code Review

  • We have declared the Database and Recordset Objects at the Global level of the Report Module so that we can refer to these objects in all other Sub-Routines.
  • Opening the OrderParam Table and activating the Primary Key Index on the Report's Open Event Procedure and keeping its memory.
  • We cross-check each OrderID with the OrderParam Table contents for the Report's Detail Section Format Event Procedure and hide the Report line for the Order Id that matches with the OrderParam value. This process continues till it reaches the end of the Report.
  • The OrderParam Recordset is closed in the Close Event Procedure of the Report.

Since the checking process needs referencing an external table for each record of the Report Source Data there will be a slight delay before the Previewing or Printing action takes place.

Next, we will explore how this method works with Customer Level Summary Totals in the Customer Footer Section of the Report.

Share:

1 comment:

  1. […] document.write(''); I think you need little more details and some coding to get things done on your report. Check this out and see whether this helps: Hiding Report Lines Conditionally | LEARN MS-ACCESS TIPS AND TRICKS. […]

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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