Hiding Report Lines Conditionally
Hiding Report lines or Report Sections conditionally at run time may not be an every day requirement. But, it is interesting to know how to do it, if it becomes necessary, 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 from appearing 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 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 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 again and again but also gives you better insight in doing things better next time.
We will create a sample Report quickly to try out this Trick.
- Import Orders and Employees Tables from C:\Program Files\Microsoft Office\Office11\samples\Northwind.mdb sample Database.
We will not be using Employees Table directly on the Report. Without the Employees Table in the Database only EmployeeIDs will be displayed on the Report when the Report is designed with Orders Table. There is a reference to the Employees Table (Combo Box using Employees Table) in the EmployeeID Field in Orders Table.
- Click on the Orders Table and select Report from Insert Menu.
- Select Report Wizard from the displayed list and click OK.
- Select the following Data Fields from the Available Fields List and move them one by one to the Selected Fields List:
- Click Next and select CustomerID as Grouping Level and move it to the right. Click Next Command Button.
- 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:
- 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 ( OrderIDs 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
If you look at the above Code you can see that the Code runs under the On Format Event Procedure on Detail Section of the Report.
- Open your Report in Design View and display the Code Module (View – – >Code).
- Copy and Paste the above Code into the Report Module.
- 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.
- Click on the Detail Section of the Report and display the Property Sheet (View – – >Properties).
- Check the On Format Event Property and you can see that the entry [EventProcedure] 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.
MS-Access makes two passes over the Report before it is actually displayed or Printed. First, the Format pass lays out each Report Section 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 on the Report before Previewing or sending to 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.
- Save the Report and open it in Print Preview.
- Check whether the Report Lines associated with OrderIDs 10702, 10625 & 10573 are still appearing on the Report or not.
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 become 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 key in OrderIDs, read them in the program and compare with the Report OrderIDs and skip those lines on the Report for matching cases.
- Create a Table with the name OrderParam with a single Field with the name OID with Data Type Number and Field Size Long Integer.
- Select Primary Key from Edit Menu to define this field as a Primary Key Field. This will prevent duplicate values 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.
- 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.
- Copy and Paste the following Code in the 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
- Save the Report with the new Code and open it in Print Preview.
- 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.
- We have declared the Database and Recordset Objects at Global level of the Report Module so that we can refer to these objects in all other Sub-Routines.
- Opening the OrderParam Table and activate the PrimaryKey Index on the Report’s Open Event Procedure and keep it memory.
- We cross-check each OrderID with the OrderParam Table contents on the Report’s Detail Section Format Event Procedure and hide the Report line for the OrderId 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 need referencing an external table for each record of the Report Source Data there will be 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.