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

Introduction.

Hiding Report Lines Conditionally.

Last week we saw that the highlighting of Report lines in the Report Print Event Subroutines can be moved into the standalone Class Module and execute the Code from there when the Report is PrintPreviewed or Printed. To highlight certain Report Line records that don't meet specific Marks criteria marked by drawing a red-colored ellipse around the TextBox. The traditional way of this procedure is Coded in the Detail_Print Event Subroutine in the Report Class Module.

The Report Detail_Format, Report_Page Events.

The Report Detail Section Format Event runs before the Print Event. The Report Detail Format Event places the Data Records line-by-line in the Report Detail Section. The Report Header, Footer, Page Header, Footer, and Report Page Events also take place in the Report Formatting phase and if these Section Events are enabled we can write the Code in those Sections too.

Here, we will try some tricks in the Detail_Format Event of the Report and use the earlier Students' Exam Result listing differently, rather than mark the failed cases with an ellipse around the obtained mark percentage as we did in the earlier episode. With this new method, the Report will be printed in two different categories from the same Report.

Exam Passed Students Listing.
Exam Failed Students Listing.

Both listings will be taken from the same Report based on one of the above option selections.

I know we can do this very easily with other means, like Passing the OpenArgs Value and filtering the Report Records. Or setting the Query Criteria to the Form-based TextBox Value on the Report launching Form. Then we will not know how we can do it through the Detail Section Format Event VBA Code. Learning to do something differently is fun and you will know you can do it more than one way when the need arises.

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

The Exam Failed Cases Listing.

The Report Launching Form Image.

The left-side Command Button launches the Report with the Normal Coding on the Report Module itself. 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 with the name ClsCmdButton to launch the Reports from there. The ClsCmdButton Class VBA Code is listed 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 simple Variable declarations. The Opt Variable will hold the Report Type Option selection Value returned from a Standard Module Public Function ReportOption(). The param String Variable will be assigned with the Report OpenArgs input Values.

The report Option selection Menu is displayed from the ReportOption() Function in the Standard Module.

In the Form Object Set Property Procedure, the Class_Init() Subroutine is called. In the Class_Init() Subroutine the CommandButton Click Events are enabled.

In the CmdNor_Click() Subroutine the ReportOption() Function is called and the returned value is stored in the RptOpt Variable. The PassPercentage Marks value is retrieved from the Form is saved in the param Variable and the Report Option selection is also added into the Param variable both separated with a comma.

The Report is open in PrintPreview and the value in the param is passed as the Report OpenArgs.

The same steps are run for the CmdCls_Click() Subroutine that opens the StudentsPassFail_Class Report also.

The CmdQuit_Click() Event Subroutine 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 VBA Code in the Report Module, showing how it is done in the traditional way of Coding, is given below 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.

At the Global declaration area, the Variant Variable Opt is declared for holding the Option selected for Exam Result Print previewing of Passed or Failed Students' List.

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 Report selection Option received as Report OpenArgs, both values separated with a comma are split into an Array of two elements. The x(0) element with the value PassPercentage marks is saved into the Report TextBox passpercentage. The Report Type option selection value x(1) is saved in the Opt variable

The PassPercentage value is copied into the Local Variable pf. The next two lines read the first student's Obtained Marks Percentage from Percentage TextBox into Variable CurVal. The Student's Mark is compared with the Passmark Percentage pf and the result is obtained in the yn Boolean Variable.

The next Code segment, given below, tests to see if the current student's record is qualified to be placed in the Passed category in the Detail Section.

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 makes the Detail Section of the Report hidden first. The next statement checks the OnFormat Event action is passing through the Report Detail Section for the first time to lay the student record on the Report. The Report may undergo more Formatting passes on a Report Page in preparation for Printing/PrintPreviewing. If the Student is qualified to be placed in the Passed Category and the Report Option selected also matches to 1 then the Detail Section of the Report is made visible for such records on the Report.

Otherwise, if the Student's Obtained Mark Percentage is less and the option selected is 2 then those Students' Cases in the Detail Section are made visible. In either case, the TRUE/FALSE value in the Variable yn and the Option selection should both match (TRUE & 1 = Passed List otherwise FALSE & 2 = Failed List) to display the Passed cases or failed cases to appear on the Report.

Next, the Report_Page() Subroutine calls the PageBorder() Function in the Standard Module to draw the PageBorder on the Report.

Private Sub Report_Page()
    PageBorder Me.Name
End Sub

The PageBorder() Function Code In 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. The Function draws two Border Lines, one inside the other, inside the Report 4 sides of the Margin area. It uses the LINE Command with the X, and Y coordinates of the Left Top corner, after the left and top margin areas, and the right bottom corner value before the margin area to draw a diagonal Line. The Color parameter is given as an RGB Value and is used to draw the Line diagonally. The next Parameter value B draws a Box using the Diagonal Value.

The Rpt.ScaleTop and Rpt.ScaleLeft value calculates the left top corner point, after the Left and Top Margin area, and some plus-minus Offset values ensure that when the Box is drawn it doesn't overlap, or draw a second Border inside the outer Border.

The Streamlining Procedure of the Report VBA Code.

Let us create the Wrapper Standalone Class Module to move the Report Module Code into it. The Wrapper Class Module ClsStudentsList Code is listed 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 with the name Rpt is declared and qualified with the keyword WithEvents to capture the Report-level Events (like Report_Page()) and execute the Event Subroutines. Another Report Section object SecDetail is also qualified with the keyword WithEvents declared to capture the Report Detail Section Format Events.

Two TextBox Controls and a Label Control are declared. The first TextBox RequiredMarks will hold the Pass Marks Percentage entered on the Report launching Form TextBox control and passed as Report Open Argument. The Obtained TextBox object instance in the Class module will read the Obtained Marks Percentage of the Student from the Report. The lblRem Label Control holds the reference of the Label control under the Remarks Column on the Report and will display the PASSED/FAILED Text depending on the Report Type Option selected. The Opt Variant Variable will receive the Report type option selected in the ReportOption() Function.

Next, the Report Object Property Get/Set Procedure assigns the Report Reference to the Rpt object in the ClsStudentsList Wrapper Class. From the Property Set m_Rpt() Procedure calls the Class_Int() Subroutine. The Class_Init() Subroutine Code segment is given 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, one local variable msg and a Constant Variable strEvent assigned with the Text "[Event Procedure]" are declared.

The next VBA Code Segment initializes the declared object variables with the References of the Controls from 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 x = Split(Rpt.OpenArgs,",") statement Splits the values into two parts received in the OpenArgs String Variable. The first part x(0) array element is assigned to the PassPercentage Marks TextBox on the Report. The second part, Report category Option x(1) is assigned to the variable Opt.

The Student's Obtained Marks Percentage reference is assigned to the Obtained TextBox instance in the Class Module. The lblRem Label object instance is assigned with the lblRemarks Label control reference under the Remarks Column. The SecDetail Section Object is assigned with the Report Detail Section Reference.

Note: What does the meaning of the word Reference in the context of a Form, Control, or Object? When an Object/Control is loaded into memory it occupies a certain area of the computer's memory with an addressable reference or memory Pointer. Even though this memory location number is not known to us it is directly related to the object name like Text0, or Quantity.

When this reference address is assigned to an Instance of the same Object Type, like Set Txt = myForm.Quantity We can work with visible/physical objects, like Form, TextBox, or Report indirectly with an instance of the same Object, as we do in the Wrapper Class, like Txt.Value = 20 will store the Value 20 in the Quantity TextBox on the Form.

When we call a Function with the Form or TextBox as a Parameter actually we are passing the Reference of those Objects. This is the central concept of Streamlining the Form/Report Module Code in the Standalone Class Module is based on.

Next, the Report Detail Section Format and Report_Page() Events are enabled. After that, the Report type option selection Menu is displayed to select.

Once the Report Type Option is selected the Report Formatting starts and we have specific requirements as far as the Detail Section Formatting is concerned. We have already written the Code in the Detail_Section Formatting Event Procedure and explained it earlier.

Next, the Report Page level Event Subroutine calls the PageBorder() Function from the Standard Module to draw the Report Page Border.

To execute the Code in the ClsStudentsList Class Module we must load this Module into memory through the Report_Load() Event Subroutine. It cannot load itself into memory. The Report Module VBA Code 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 help to bring the Standalone Class Module into Memory and to run the entire action in the Standalone Class Module.

Hope you enjoy VBA Coding in the Standalone Class Module and are aware of its potential to save time by separating the task of Report Design and VBA Coding so that it is easy to transport reusable Code into other Projects and use the Code with/without change as the case may be.