Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

WithEvents and Report Line Hiding


If you have tried out last week's Report OnPrint() Event trapping in Access Class Module then it will be easy for you here.  What we did there was Report's Detail Section Print  Event when triggered, is captured in Class Module, validated the Field value, and highlighted the Text Box and value of Exam passed students.  We are going to do something similar here too.

If we only need to highlight TextBoxes on the Report with some color, change font-style Bold, Italic, or Underline then we don't need the Print Event capturing in Class Module to do it through VBA Code. 

The Conditional Formatting method will highlight the text, with different Colors, Font Bold Italic, or Underline.  It only needs an expression like the following in the conditional formatting dialogue box:  

Expression is [Total]/[MaxMarks]*100 >= [Percentage]

But, we can do many other things, which are not available in conditional formattings,  like changing the font or font size and highlighting the border of the Text Box or any other styling through the Event handling in the Class Module.

Last week we captured the Detail Section OnPrint() Event, but now we will try out the OnFormat() built-in Event to hide certain report lines, leaving only the required ones (either passed or failed students list) on the Report.

We will use the same Report from last week's post, to prepare the Passed or 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) is given below.  Remember, on the Report View the Formatting and Printing actions take place but those built-in Events are not fired on the Report View.  They are triggered only in the Print Preview mode.

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

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

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))

  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
            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

Exit Sub

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

In the global declaration area of the Class Module the Access.Report Property Rpt and Report Section Property secRpt are declared.

The three Text Box Control Properties txt, max, and pct are declared and will be assigned with references of the TextBox controls on the Report.  After that, we can read Students Marks (txt), the Maximum Marks (max - of all Subjects), and the Percentage (pct) of Pass Marks to calculate and categorize the Students as Passed or Failed.

The Integer Type Property i will hold the Report Option 1 or 2 (i.e. 1. Passed Students List. 2. Failed Students List.), entered by the User when the Report is Run.

The Property Set mRpt() Procedure assigns the current Report Object through the Report_Open() Event Procedure and assigns the RptNewVal Parameter value to the Rpt Object.

Immediately after assigning the Report to Rpt Object the Report Detail Section is assigned to the secRpt Object.  The Report Detail Section Format Event triggering mechanism is also enabled within the following Code segment in the Set mRpt() Property Procedure.

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

We can't use last week's OnPrint() Event here, to hide the required Report lines, the Print Event is the final phase of other actions like the Formatting phase. Once the Formatting phase is complete and Printing starts we cannot hide individual report lines.  If the statement secRpt.Visible = False is executed at this point. The entire Detail Section will not be visible.

Report Events before Viewing/Printing.

  1. In the first pass of Report Formatting the Format Event fires and determines which data belongs to the Report Page.  This is where our programmed activities take place.
  2. In the second formatting pass, the Report lines are formatted for printing/previewing.  
  3. The Print Event fires immediately after the second formatting pass, but before the Report lines are printed on Detail Section.

Within the Do While . . . Loop we have inserted an Inputbox() statement to get the user response (1 or 2) for report option: 1. Passed Students List, 2. Failed Students List.

The User is forced to enter the value 1 or 2. Any value outside this range is not accepted and the Do While . . . Loop code keeps repeating itself till a valid value is received in Variable i.

The next three statements assign the TextBox controls to their respective declared Properties.

When the Report is open in the Print Preview Mode the Detail Section Format Event is fired and getting captured in the secRpt_Format() sub-routine in the Class Module: ClsStudentLine. Few local variables are declared at the beginning of the sub-routine.

The Maximum Marks and Pass Percentage TextBox values are read into max and pct. The curval = txt.value statement reads the students obtained Total marks and assigns them to the Variable curval.

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

yn = (pf >= pp) - compares the obtained percentage of marks with the Pass Percentage and checks whether the obtained percentage is greater than or equal to Pass Percentage, if so, the result in Variable yn = TRUE (Passed) otherwise yn = FALSE (Failed).

The lbl Control is set with the Label control reference, of the Label to the right of the Total Text Box control.

Next Line the Report Detail Section is first kept in a hidden state. When the Student is found in the Passed category (yn = TRUE) then the Total TextBox is formatted to highlight it and the Label Control's Caption is set with the text Passed. If yn = FALSE, then the Formatting is reset to normal and the label control is set with the text Failed, depending on what type of Report you have opted for.

Based on the Report Option selected by the user in response to the InputBox() Function.

The statement i=Inputbox() within the Do While . . . Loop gives the user two choices to select: 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 pass the Exam then the Detail Section is made visible.  That particular report line appears in the Report.  This check is done for each line of the Report and passed students only are made visible on the Report. 

Option 2: The Detail Section is made visible for Failed student data lines, hiding others.

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

On the Report Code Module the Class Module ClsStudentList is instantiated in Object R.  In the Form_Open() Event Procedure the current Report Object is passed to the Class Object's R.mRpt() Set Property Procedure. These are the only Code required on the Report's Class Module.

All the other actions are happening behind the curtain in the Class Module ClsStudentsList.

Note: Always open the Report in Print Preview Mode (not in Report View) to trigger the Format Event in the Detail Section of the report.

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

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



  1. "Next three statement assigns the Text Box controls to their respective declared Properties."
    It seems to me that Rpt.Controls("Total") isn't a property, it's a control named Total being part of the Controls collection

  2. Property Declarations:

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

    Assignment Statements:
    Set txt = Rpt.Controls("Total")
    Set max = Rpt.Controls("MaxMarks")
    Set pct = Rpt.Controls("Percentage")

    As you can see from the above Property declarations and assignment statement ‘Total’ Textbox control is assigned to the txt Property.

    There are other Text Box controls on the Report but we need only the three specific ones we picked up.

  3. It is part of the Controls Collection. We are picking it up with specific name ‘Total’ from the Controls Collection and assigns it to the declared Property. The Controls Collection includes all type of controls Text Boxes, Labels and others, if any.

  4. Thanks.
    "The Property Set mRpt() Procedure assigns the current Report Object through the Report_Load() Event Procedure"
    but the StudentsList report doesn't have code for the Load event.

  5. Yes, your are right and thank you for pointing out the mistake. It is an oversight. Even though both Open/Load Events works in this case, the preferred Event is Report_Load(). Corrected the text to Form_Open() Event. The Report on Demo Database uses Form_Open() Event.


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