WithEvents and Report Line Hiding

Introduction

If you have already tried last week’s Report OnPrint() event trapping in the Access Class Module, this next step will be easier to follow. In that earlier example, we captured the Report’s Detail Section OnPrint event inside a Class Module, validated the field values, and highlighted the Text Box and marks of students who passed their exams. We will do something similar here as well.

However, if our goal is only to highlight Text Boxes on the Report with colors or apply styles like Bold, Italic, or Underline, then we don’t need to capture the Print event in a Class Module. This can be done more simply using Conditional Formatting.

For example, in the Conditional Formatting dialog box, we can use an expression like:

Expression Is: [Total]/[MaxMarks]*100 >= [Percentage]

This will highlight the qualifying students’ marks automatically, based on the percentage threshold.

But Conditional Formatting has its limitations. It cannot perform tasks like changing the font type, adjusting the font size, or highlighting the border of the Text Box. These advanced styling changes can be done through event handling in the Class Module.

In the previous example, we captured the Detail Section OnPrint() event. This time, we will try out the OnFormat() built-in event to hide certain report lines, leaving only the required ones (either the passed or failed students) visible on the Report.

We will reuse the same Report from last week’s example to generate either a Passed-Students-List or a Failed-Students-List from the same report.

Sample Images of Report View, Print Previews

1. Full List of Students in Report View (not in Print Preview)

The complete list of students is shown below in Report View.

Keep in mind that while the formatting and printing processes do occur internally in Report View, the built-in Report events (such as OnFormat and OnPrint) are not triggered in this mode. These events are fired only when the report is opened in Print Preview mode.

Right-click to open Large Image in New Tab/Window.

2. The Passed Students' List in Print Preview is achieved by preventing the failed students' report lines from appearing on the Report.

Right-click to open Large Image in New Tab/Window.

3.  Failed Students' List in Print Preview and Passed Students report lines will not appear on the Report.

Right-click to open Large Image in New Tab/Window.

Report options 2 and 3 are prepared without applying any filtering condition directly on the source data,  but showing or hiding the report lines in the Detail Section's Format Event does the job.

Class Module: ClsStudentsList VBA Code

Private WithEvents Rpt As Access.Report
Private WithEvents secRpt As Access.[_SectionInReport]

Private txt As Access.TextBox
Private max As Access.TextBox
Private pct As Access.TextBox
Private i As Integer

Public Property Get mRpt() As Access.Report
   Set mRpt = Rpt
End Property

Public Property Set mRpt(RptNewVal As Access.Report)
Dim msg As String
Const strEvent = "[Event Procedure]"

  Set Rpt = RptNewVal

  With Rpt
     Set secRpt = .Section(acDetail)
     secRpt.OnFormat = strEvent
  End With
  
  msg = "1. Passed List" & vbCr & "2. Failed List"
  i = 0
  Do While i < 1 Or i > 2
    i = Val(InputBox(msg, "Report Options", 1))
  Loop

  Set txt = Rpt.Controls("Total")
  Set max = Rpt.Controls("MaxMarks")
  Set pct = Rpt.Controls("Percentage")
End Property

Private Sub secRpt_Format(Cancel As Integer, FormatCount As Integer)
Dim curval As Double
Dim m_Max As Double
Dim m_pass As Double
Dim mk As Double
Dim pf As Double
Dim yn As Boolean
Dim lbl As Access.Label

On Error GoTo secRpt_Print_Err

m_Max = max.Value
pp = pct.Value
curval = txt.Value

pf = curval / m_Max * 100

yn = (pf >= pp)

Set lbl = Rpt.Controls("lblpass")

secRpt.Visible = False
       
       If yn Then
            txt.FontBold = True
            txt.FontSize = 12
            txt.BorderStyle = 1
            lbl.Caption = "Passed"
            lbl.ForeColor = RGB(0, FF, 0)
            lbl.FontBold = True
                If i = 1 Then
                    secRpt.Visible = True
                End If
        Else
            txt.FontBold = False
            txt.FontSize = 9
            txt.BorderStyle = 0
            lbl.Caption = "Failed"
            lbl.FontBold = False
                If i = 2 Then
                    secRpt.Visible = True
                End If
        End If

secRpt_Print_Exit:
Exit Sub

secRpt_Print_Err:
MsgBox Err.Description, , "secRpt_Print()"
Resume secRpt_Print_Exit
End Sub

In the global declaration area of the Class Module, two key properties are declared: the Report property Rpt and the Report Section property secRpt.

Next, three Text Box control properties—txt, max, and pct—are declared. These will later be assigned references to their corresponding Text Box controls on the Report. Through these references, we can read the student’s marks (txt), the maximum marks (max, representing the total marks for all subjects), and the pass percentage (pct). These values are used to calculate each student’s percentage score and categorize them as either Passed or Failed.

An Integer-type property i is also declared to hold the Report option chosen by the user at runtime:

• 1 for Passed Students List

• 2 for Failed Students List

 Set mRpt(): The Property procedure assigns the active Report object to the Rpt property when called from the Report_Open() event. It also passes the user-selected report option (RptNewVal) into the Rpt object.

Immediately after assigning the Rpt object, the Report Detail Section is assigned to the secRpt property, and the Detail Section’s OnFormat event is enabled within the same Set mRpt() procedure. This allows the Class Module to capture the OnFormat event and apply formatting logic line by line during the report’s generation.

With Rpt
     Set secRpt = .Section(acDetail)
     secRpt.OnFormat = strEvent
  End With 

We cannot use last week’s OnPrint() event to hide specific report lines because the Print event occurs after the Formatting phase has been completed. Once the report enters the printing stage, it is too late to selectively hide individual lines. If we attempt to execute secRpt.Visible = False at this point, it will hide the entire Detail Section altogether, rather than just the specific lines we want to remove.

Report Events before Viewing/Printing.

1. During the first pass of report formatting, the Format event is triggered. At this stage, Access determines which data will appear on the report page, and our programmed logic and conditional actions are executed.

2. In the second formatting pass, the selected report lines are prepared and laid out for printing or previewing.

3. The Print event fires immediately after the second formatting pass, just before the formatted report lines are printed on the Detail Section.

Inside the Do While ... Loop, an InputBox() statement is used to obtain the user’s choice for the report option:

1 – Passed Students List
2 – Failed Students List

The user must enter either 1 or 2. Any other value is rejected, and the Do While...Loop continues prompting until a valid input is received and stored in the variable i.

After receiving a valid option, the next three statements assign the Report’s TextBox controls to their respective declared Properties in the Class Module.

When the report is opened in Print Preview mode, the Detail Section’s Format event is triggered and captured in the secRpt_Format() subroutine of the ClsStudentLine Class Module. A few local variables are declared at the start of this subroutine.

The Maximum Marks and Pass Percentage values are read from their respective TextBox controls and assigned to the max and pct properties.
The statement curval = txt.value retrieves each student’s total marks and assigns them to the curval variable.

Next, the statement pf = curval / m_Max * 100 calculates the percentage of marks obtained by the student.

The statement yn = (pf >= pp) compares the student’s obtained percentage (pf) with the Pass Percentage (pp).

• If the obtained percentage is greater than or equal to the pass percentage, yn = TRUE (student Passed).

• Otherwise, yn = FALSE (student failed).

The lbl property is assigned to the Label control that appears to the right of the Total Marks TextBox on the Report.

Initially, the Report Detail Section is kept hidden. When a student is found to be in the Passed category (yn = TRUE), the Total Marks TextBox is formatted (highlighted) and the Label control’s Caption is set to "Passed".

If yn = FALSE, the formatting is reset to normal and the Label Caption is set to "Failed", depending on which Report option the user has selected.

The user’s choice is obtained from the statement i = InputBox() within the Do While...Loop, which prompts for one of the two options:

1 – Passed Students List
2 – Failed Students List.

How it works.

Option 1:

If the student in the current line of the Detail Section is found to have passed the exam, the Detail Section is made visible, allowing that report line to appear on the Report. This check is performed for each line of the Report, and only the passed students’ lines are displayed.

Option 2:

If Option 2 is selected, the Detail Section is made visible only for failed students’ data lines, while all other lines are hidden from the Report.

Report Class Module Code.

The Report Class Module Code is given below:

Option Compare Database
Option Explicit

Private R As New ClsStudentsList

Private Sub Report_Open(Cancel As Integer)
  Set R.mRpt = Me
End Sub

In the Report’s Code Module, the ClsStudentList Class Module is instantiated as the object R. In the Report_Open() event procedure, the current Report object (Me) is passed to the class object through its R.mRpt() Set property procedure. These are the only lines of code required within the Report’s own Class Module.

All other operations are handled internally, behind the scenes, by the ClsStudentList Class Module.

Note: Always open the report in Print Preview mode (not in Report View) to ensure that the Format event in the Detail section is triggered.

Download the Demo Database from the Link given below and try out the Report and Code.

Download StudentsList2007.zip

Download StudentsList2003.zip

Links to WithEvents ...Tutorials.

1. WithEvents MS-Access Class Module Tutorial
2. WithEvents and Defining Your Own Events
3. withevents Button Combo List TextBox Tab
4. Access Form Control Arrays and Event Capturing
5. Access Form Control Arrays and Event-2
6. Access Form Control Arrays and Event-3
7. WithEvents in Class Module for Sub-Form TextBox Events
8. WithEvents in Class Module and Data Entry
9. WithEvents and Access Report Event Sink
10. WithEvents and Report Lines Hiding
11. WithEvents and Report Lines Highlighting
12. Withevents TextBox and Command Button Arrays
13. Withevents TextBox CommandButton Dictionary
14. Withevents and all Form Control Types