Streamlining Report Module in Class Module-2 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Hiding Report Lines Conditionally.

Last week, we demonstrated that the code for highlighting Report lines in the Report Print Event can be moved into a standalone Class Module and executed from there whenever the Report is PrintPreviewed or printed.

To visually emphasize specific Report line records that do not meet the required marks criteria, a red ellipse is drawn around the corresponding TextBox. Traditionally, this procedure is implemented directly in the Detail_Print Event Subroutine within the Report’s Class Module. The Report Detail_Format, Report_Page Events.

The Report Detail Section Format Event runs before the Print Event. During this phase, the Report Detail Format Event arranges the data records line by line within the Detail section.

Other events, such as the Report Header, Report Footer, Page Header, Page Footer, and Report Page Events, also occur during the formatting phase. When these Section events are enabled, code can be written within them to control or manipulate the layout and appearance of the Report.

In this example, we will explore some new techniques in the Detail_Format Event of the Report. Instead of highlighting the failed cases with a red ellipse around the obtained marks, as we did in the previous episode, we will use this method to print the Report in two separate categories from the same data set.

Exam Passed Students Listing.
Exam Failed Students Listing.

Both listings will be generated from the same Report, based on one of the option selections mentioned earlier.

Of course, this could also be achieved more easily by other means, such as passing a value via OpenArgs to filter the Report records, or by setting the Query criteria based on a Form-based TextBox value when launching the Report. However, using the Detail Section Format Event to accomplish this allows you to see an alternative approach. Exploring different methods is both educational and practical—you’ll learn that there’s often more than one way to achieve the desired result.

The Report Listing image for the Exam Passed Cases Option above is given below:

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

The Exam Failed Cases Listing.

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

The Report Launching Form Image.

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

The left-side Command Button launches the Report using the traditional coding within the Report Module itself. In this mode, the Report page is drawn with a double-lined border.

Since there are three Command Buttons on the Main Form, we created a Wrapper Class ClsCmdButton to handle the launching of Reports. The VBA code for the ClsCmdButton class is shown below:

Option Compare Database
Option Explicit

Private cmdfrm As Form
Private WithEvents cmdNor As CommandButton 'For Normal Report Module Coded Report launching
Private WithEvents cmdCls As CommandButton 'Report Module Code in Class Module
Private WithEvents cmdQuit As CommandButton'Close the Main Form
Dim Opt As Variant
Dim param As String

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Command Button Events
'Author: a.p.r. pillai
'Date  : 22/09/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

'Form's Property GET/SET Procedures
Public Property Get cmd_Frm() As Form
    Set cmd_Frm = cmdfrm
End Property

Public Property Set cmd_Frm(ByRef cfrm As Form)
    Set cmdfrm = cfrm
    Call class_Init
End Property

Private Sub class_Init()
Const EP = "[Event Procedure]"
    Set cmdNor = cmdfrm.cmdNormal
        cmdNor.OnClick = EP
        
    Set cmdCls = cmdfrm.cmdClass
        cmdCls.OnClick = EP
        
    Set cmdQuit = cmdfrm.cmdClose
        cmdQuit.OnClick = EP
End Sub

Private Sub cmdNor_Click()
Dim RptOpt As Integer

RptOpt = ReportOption()

    param = cmdfrm!Pass 'PassPercentage
    param = param & "," & RptOpt ' and report option
    
    If Nz(cmdfrm.Pass, 0) = 0 Then
        MsgBox "Enter pass-Mark Percentage?" & vbCr & "e.g.: default 60"
    Else
        DoCmd.OpenReport "StudentsPassFail_Normal", acViewPreview, , , , param
    End If
End Sub

Private Sub cmdCls_Click()
Dim RptOpt As Integer

RptOpt = ReportOption()
    param = cmdfrm!Pass 'PassPercentage
    param = param & "," & RptOpt ' and report option
    
    If Nz(cmdfrm.Pass, 0) = 0 Then
        MsgBox "Enter pass-Mark Percentage?" & vbCr & "e.g.: default 60"
    Else
        DoCmd.OpenReport "StudentsPassFail_Normal", acViewPreview, , , , param
    End If
End sub

'Event Subroutines
Private Sub cmdQuit_Click()
    If MsgBox("Close " & cmdfrm.Name & " Form?", vbYesNo + vbQuestion, "cmd_Click") = vbYes Then
        DoCmd.Close acForm, cmdfrm.Name
        Exit Sub
    End If
End Sub

In the global declaration area, the Main Form and Command Button instances are declared, along with two additional variables. The Opt variable stores the Report type option selected, which is returned from the ReportOption() function in a Standard Module. The param string variable is used to hold the OpenArgs input values passed to the Report.

The Report option selection menu is generated by the ReportOption() function in the Standard Module.

Within the Form object Set Property procedure, the Class_Init() subroutine is called. Inside Class_Init(), the Click Events for the Command Buttons are enabled, allowing the wrapper class to handle Report launching.

In the CmdNor_Click() subroutine, the ReportOption() function is called, and the returned value is stored in the RptOpt variable. The PassPercentage value is retrieved from the Form and stored in the param variable. The Report option selection is also appended to param, with both values separated by a comma.

The Report is then opened in Print Preview, with the combined parameter values passed through OpenArgs.

The CmdCls_Click() subroutine follows the same steps to open the StudentsPassFail_Class Report.

Finally, the CmdQuit_Click() event subroutine simply closes the Form.

ReportOption Function VBA Code.

Option Compare Database
Option Explicit

'Report Option Selection
Public Function ReportOption() As Integer
Dim msg As String

  msg = "1. Passed List" & vbCr & "2. Failed List"
  
  Opt = 0
  Do While Opt < 1 Or Opt > 2
    Opt = InputBox(msg, "Report Options", 1)
    If Opt = "" Then Opt = 0
  Loop
  
ReportOption = Opt
End Function

The Report StudentsPassFail_Normal Module VBA Code.

The following VBA code in the Report Module demonstrates how this functionality is implemented using the traditional coding approach. It is provided here for reference.

Option Compare Database
Option Explicit

Dim Opt As Variant

'---------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'---------------------------------------------------------
'Sreamlining Report Module Code in Standalone Class Module
'Author: a.p.r. pillai
'Date  : 22/09/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'---------------------------------------------------------
Private Sub Report_Load()
Dim x As Variant
x = Split(OpenArgs, ",")

Me!PassPercentage = Val(x(0))
Opt = Val(x(1))

End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Report Format Pass Calls this Subroutine
Dim Curval As Double
Dim pf As Double
Dim yn As Boolean

On Error GoTo Detail_Format_Err

pf = Me!PassPercentage
Curval = Me!Percentage 'Student's Marks percentage
yn = (Curval >= pf) 'check for pass/fail case

'Detail Section Detault Setting hidden
'During the Report Formatting phase the Items which meets
'the criteria only visible on the Report Detail Section when PrintPreviewed

Detail.Visible = False
If FormatCount = 1 Then 'The Report's Formatting Count=1 
       If yn Then ' yn=True - student Passed
          With Me
            .lblRemarks.Caption = "PASSED"
            .lblRemarks.ForeColor = RGB(0, 255, 0) 'Green Color
                If Opt = 1 Then 'Report Option Passed Students List
                    Detail.Visible = True 'Make the Detail Section Visible
                End If
          End With
        Else 'yn=False an Option=2 Cases
          With Me
            .lblRemarks.Caption = "FAILED"
            .lblRemarks.ForeColor = RGB(255, 0, 0) 'Red Color
                If Opt = 2 Then
                    Detail.Visible = True
                End If
          End With
        End If
End If


Detail_Format_Exit:
Exit Sub

Detail_Format_Err:
MsgBox Err.Description, , "Detail_Format()"
Resume Detail_Format_Exit
End Sub

Private Sub Report_Page()
    PageBorder Me.Name
End Sub

The StudentPassFail_Normal Report Module Segmentwise Code Review.

In the global declaration area, the Opt variable of type Variant is declared to hold the option selected for printing the Exam Result: the Passed or Failed list will be displayed in Print Preview.

The Report_Load Event Subroutine.

 
Option Compare Database
Option Explicit

Dim Opt As Variant

'---------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'---------------------------------------------------------
'Sreamlining Report Module Code in Standalone Class Module
'Author: a.p.r. pillai
'Date  : 22/09/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'---------------------------------------------------------
Private Sub Report_Load()
Dim x As Variant
x = Split(OpenArgs, ",")

Me!PassPercentage = Val(x(0))
Opt = Val(x(1))

End Sub

In the Form_Load() event procedure, the PassPercentage and the Report selection option—both passed as a single OpenArgs string separated by a comma—are split into a two-element array. The first element, x(0), containing the PassPercentage value, is assigned to the passpercentage TextBox on the Report. The second element, x(1), representing the Report type option, is stored in the Opt variable.

The PassPercentage value is first copied into the local variable pf. The next two lines retrieve the first student’s Obtained Marks Percentage from the Percentage TextBox and store it in the variable CurVal. The student’s score is then compared with the pass mark, and the result of this comparison is stored in the Boolean variable yn.

The following code segment validates whether the current student is in the Passed category within the Detail section of the Report.

Detail.Visible = False 'Hide the Detail Section
If FormatCount = 1 Then 'The first Format Pass on this Page.
       If yn Then
            lblRemarks.Caption = "PASSED"
            lblRemarks.ForeColor = RGB(0, 255, 0)
                If Opt = 1 Then 'Passed Students Listing
                    Detail.Visible = True
                End If
        Else
            lblRemarks.Caption = "FAILED"
            lblRemarks.ForeColor = RGB(255, 0, 0)
                If Opt = 2 Then
                    Detail.Visible = True
                End If
        End If
End If

The statement


Detail.Visible = False

initially hides the Detail Section of the Report. The next step ensures that the OnFormat event is executed for the Detail Section, for the first time, to lay out the student record. Note that the Report may go through multiple formatting passes per page in preparation for printing or Print Preview.

If the student is in the Passed category and the selected Report option is 1, then the Detail Section is made visible for that record, allowing it to appear in the Report.

Otherwise, if the student’s Obtained Marks Percentage is below the pass threshold and the selected option equals 2, those student records in the Detail Section are made visible. In both scenarios, the Boolean variable yn and the option selection must align correctly—TRUE & 1 for the Passed list, or FALSE & 2 for the Failed list—to ensure that only the relevant records appear on the Report.

Next, the Report_Page() subroutine calls the PageBorder() function from the Standard Module to draw the page border on the Report.

Private Sub Report_Page()
    PageBorder Me.Name
End Sub

The PageBorder() Function Code In the Standard Module.

Public Function PageBorder(ByVal strName As String)
Dim Rpt As Report, lngColor As Long
Dim sngTop As Single
Dim sngLeft As Single
Dim sngwidth As Single
Dim sngheight As Single

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Draw Report PageBorder
'Author: a.p.r. pillai
'Date  : 18/09/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

lngColor = RGB(0, 0, 255)
On Error GoTo DrawBox_Err

    Set Rpt = Reports(strName)
    ' Set scale to pixels.
    Rpt.ScaleMode = 3
    
    'outer Border
       sngTop = Rpt.ScaleTop        'Top Value After Margin
      sngLeft = Rpt.ScaleLeft       'Left Value After Margin
     sngwidth = Rpt.ScaleWidth - 7  ' Right Margin -7 pixels
    sngheight = Rpt.ScaleHeight - 7 'Bottom Margin -7 pixels
       
    ' Draw line as a box.
Rpt.Line (sngTop, sngLeft)-(sngwidth, sngheight), lngColor, B

  'Draw Box inside the outer Box
   sngTop = Rpt.ScaleTop + 5
  sngLeft = Rpt.ScaleLeft + 5
 sngwidth = Rpt.ScaleWidth - 13
sngheight = Rpt.ScaleHeight - 13

'Draw second Box within the Borders of the First Box
Rpt.Line (sngTop, sngLeft)-(sngwidth, sngheight), lngColor, B

DrawBox_Exit:
Exit Function

DrawBox_Err:
MsgBox Err.Description, , "DrawBox"
Resume DrawBox_Exit
End Function

The PageBorder() function accepts the open Report’s name as a parameter. It draws two concentric border lines inside the four sides of the Report’s margin area.

The function uses the LINE command with the X and Y coordinates of the top-left corner, offset from the left and top margins, and the bottom-right corner, positioned just before the right and bottom margins, to draw a diagonal line. The Color parameter, specified as an RGB value, determines the color of the line. The final parameter, B, instructs Access to draw a box using the diagonal coordinates, effectively creating the border around the page.

The Rpt.ScaleTop and Rpt.ScaleLeft properties determine the coordinates of the top-left corner of the page, adjusted to account for the Left and Top margin areas. Additional offset values are applied to ensure that when the box is drawn, it does not overlap the outer border or create an unintended second border inside it.

The Streamlining Procedure of the Report VBA Code.

Let us create a standalone Wrapper Class Module to transfer the Report Module code into it. The code for the ClsStudentsList Wrapper Class is shown below:

Option Compare Database
Option Explicit

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

Private RequiredMarks As Access.TextBox
Private Obtained As Access.TextBox
Private lblRem As Access.Label

Private Opt As Variant

'---------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'---------------------------------------------------------
'Sreamlining Report Module Code in Standalone Class Module
'Author: a.p.r. pillai
'Date  : 22/09/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'---------------------------------------------------------

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

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

Call class_Init
End Property

Private Sub class_Init()
On Error GoTo Class_Init_Err:
Dim msg As String
Dim x As Variant

Const strEvent = "[Event Procedure]"
x = Split(Rpt.OpenArgs,",")

  With Rpt
    !PassPercentage = Val(x(0)) ' Pass Mark% save on Report
    Set RequiredMarks = .PassPercentage 'Assign this Control Reference to pct
    Opt = Val(x(1))
    Set Obtained = .Percentage     'Student's Obtained Percentage
    Set lblRem = .lblRemarks 'Passed/Failed Display Label
    Set SecDetail = .Section(acDetail) 'Detail Section Reference
    
     SecDetail.OnFormat = strEvent 'Enable Detail Section Format Event
     .OnPage = strEvent 'Enable Report_Page Event
  End With
  
Class_Init_Exit:
Exit Sub

Class_Init_Err:
MsgBox Err & ": " & Err.Description, vbCritical + vbOK, "Class_Init()"

Resume Class_Init_Exit
End Sub

Private Sub secDetail_Format(Cancel As Integer, FormatCount As Integer)
'Report Format Pass Calls this Subroutine
Dim Curval As Double
Dim pf As Double    'pass/fail
Dim yn As Boolean

On Error GoTo secDetail_Format_Err

Curval = Nz(Obtained.Value, 0)
pf = Nz(RequiredMarks.Value, 0)
yn = (Curval >= pf)

'Start Laying the Detail Section items from Format Count 1 onwards.
'All the Records are placed on the Detail Section, but the
'Lines which meets the Criteria only made visible on the Report.

SecDetail.Visible = False 'Hide the Detail Section
If FormatCount = 1 Then 'The first Format Pass on this Page.
       If yn Then
            lblRem.Caption = "PASSED"
            lblRem.ForeColor = RGB(0, 255, 0)
                If Opt = 1 Then 'Passed Students Listing
                    SecDetail.Visible = True
                End If
        Else
            lblRem.Caption = "FAILED"
            lblRem.ForeColor = RGB(255, 0, 0)
                If Opt = 2 Then
                    SecDetail.Visible = True
                End If
        End If
End If

secDetail_Format_Exit:
Exit Sub

secDetail_Format_Err:
MsgBox Err & ": " & Err.Description, , "secDetail()"
Resume secDetail_Format_Exit
End Sub

Private Sub Rpt_Page()
    PageBorder Rpt.Name
End Sub

In the global declaration area, a Report object named Rpt is declared and qualified with the WithEvents keyword, allowing the class to capture Report-level events (such as Report_Page()) and execute their corresponding event subroutines. Additionally, a Report Section object, SecDetail, is also declared with WithEvents to capture Detail Section Format events of the Report.

Two TextBox controls and a Label control are declared. The first TextBox, RequiredMarks, stores the pass mark percentage entered on the Form used to launch the Report and passed via OpenArgs. The second TextBox, Obtained, reads the student’s obtained marks percentage directly from the Report.

The lblRem Label control corresponds to the Remarks column on the Report and will display “PASSED” or “FAILED” based on the Report type option selected. The Opt variable of type Variant stores the Report type option returned from the ReportOption() function.

Next, the Report object property Get, Set procedures assign the Report reference to the Rpt object in the ClsStudentsList Wrapper Class. Within the Set m_Rpt() property procedure, the Class_Init() subroutine is called to initialize the class. The relevant Class_Init() code segment is shown below:

Private Sub class_Init()
On Error GoTo Class_Init_Err:
Dim msg As String
Dim x As Variant

Const strEvent = "[Event Procedure]"
x = Split(Rpt.OpenArgs,",")

  With Rpt
    !PassPercentage = Val(x(0)) ' Pass Mark% save on Report
    Set RequiredMarks = .PassPercentage 'Assign this Control Reference to pct
    Opt = Val(x(1))
    Set Obtained = .Percentage     'Student's Obtained Percentage
    Set lblRem = .lblRemarks 'Passed/Failed Display Label
    Set SecDetail = .Section(acDetail) 'Detail Section Reference
    
     SecDetail.OnFormat = strEvent 'Enable Detail Section Format Event
     .OnPage = strEvent 'Enable Report_Page Event
  End With
  
Class_Init_Exit:
Exit Sub

Class_Init_Err:
MsgBox Err & ": " & Err.Description, vbCritical + vbOK, "Class_Init()"

Resume Class_Init_Exit
End Sub

At the beginning of the Class_Init() subroutine, a local variable msg and a constant variable strEvent, assigned the text "[Event Procedure]", are declared.

The next segment of VBA code initializes the previously declared object variables by assigning them references to the corresponding controls on the Report.

x = Split(Rpt.OpenArgs,",")

  With Rpt
    !PassPercentage = Val(x(0)) ' Pass Mark% save on Report
    Set RequiredMarks = .PassPercentage 'Assign this Control Reference to pct
    Opt = Val(x(1))
    Set Obtained = .Percentage     'Student's Obtained Percentage
    Set lblRem = .lblRemarks 'Passed/Failed Display Label
    Set SecDetail = .Section(acDetail) 'Detail Section Reference
    
     SecDetail.OnFormat = strEvent 'Enable Detail Section Format Event
     .OnPage = strEvent 'Enable Report_Page Event
  End With

The statement


x = Split(Rpt.OpenArgs, ",")

splits the OpenArgs string into two parts. The first element, x(0), containing the Pass Percentage Marks, is assigned to the corresponding TextBox on the Report. The second element, x(1), representing the Report category option, is stored in the Opt variable.

The student’s Obtained Marks Percentage is assigned to the Obtained TextBox instance in the class module. The lblRem Label object is assigned the reference of the lblRemarks Label control under the Remarks column on the Report. Finally, the SecDetail object is assigned the reference of the Report’s Detail Section.

Note: What does “reference” mean in the context of a Form, Control, or Object?

When a Form or Control is loaded into memory, it occupies a specific area of the computer’s memory with an addressable reference (or memory pointer). Although we don’t know the exact memory address, it is directly associated with the object’s name, such as Text0 or Quantity.

When this reference is assigned to an instance of the same object type—for example:


Set Txt = myForm.Quantity

—we can manipulate the physical object indirectly through the instance. For example, setting


Txt.Value = 20

stores the value 20 in the Quantity TextBox on the Form.

Similarly, when we pass a Form or TextBox to a function, actually we are passing the reference to that object, not a copy of the object itself.

This concept of working with object references is the foundation of streamlining Form and Report module code using standalone class modules.

Here’s a simple conceptual diagram (by ChatGPT) to illustrate how object references work in VBA:


+----------------------+          +----------------------+
|  Form: myForm        |          |  Memory (Objects)    |
|----------------------|          |----------------------|
| TextBox: Quantity    |  --->    | [Memory Address 101] |  <- Physical TextBox in memory
| TextBox: Name        |          | [Memory Address 102] |
| CommandButton: OK    |          | [Memory Address 103] |
+----------------------+          +----------------------+
         |
         | Set Txt = myForm.Quantity
         v
+----------------------+
|  Txt (Variable)      |
|----------------------|
| Reference -> 101     |  <- Points to the Quantity TextBox in memory
+----------------------+

Usage Example:
Txt.Value = 20  ' Updates the actual Quantity TextBox on the Form

Explanation:

Each control on the Form occupies memory when the Form is loaded.
Assigning a control to a variable with Set does not create a new control; it creates a reference pointing to the same memory location.
Any changes made via the reference (Txt.Value = 20) directly affect the original control.
Functions that accept a Form or Control as a parameter actually receive the reference, allowing the function to work directly with the original object.

Diagram provided by ChatGPT

@@@Next, the Report Detail Section Format and Report_Page() Events are enabled. Once these are active, the Report Type Option Menu is displayed, allowing the user to select the required report.

After the Report Type Option is chosen, the report formatting process begins, with special attention to the requirements of the Detail Section. We have already covered the code written in the Detail Section’s Format Event Procedure and explained its functionality earlier.

At the Page level, the Report_Page Event Procedure calls the PageBorder() Function in the Standard Module to draw the border around each Report Page.

Finally, to execute the code in the ClsStudentsList Class Module, we must explicitly load this module into memory through the Report_Load() Event Procedure. A Class Module cannot load itself into memory. The streamlined VBA code for the Report Module is given below.

Option Compare Database
Option Explicit

Private Marks As New ClsStudentsList

Private Sub Report_Load()
  Set Marks.mRpt = Me
End Sub

The above four lines of code load the Standalone Class Module into memory and start the entire process to run from within the Class Module.

I hope you enjoy working with VBA in Standalone Class Modules and recognize their potential to streamline your development process. By separating Report Design from VBA coding, you gain the flexibility to easily reuse and transport code across projects, saving valuable time while ensuring cleaner, more maintainable solutions.