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

Introduction.

Report Module VBA Code in Standalone Class Module.

In the earlier episodes of this series, Streamlining Form Module Code in the Standalone Class Module, we explored how to handle Event Subroutines for frequently used Access controls within a standalone Class Module. This approach allows for managing event handling in Standalone Class Module(s), without interfering with the design task of the Form, resulting in cleaner, more maintainable code.

If you’re new to handling Form/Report Module Event Procedures in standalone Class Modules, I suggest starting with the earlier episodes in this series. These articles provide the necessary background and step-by-step explanations to help you understand and apply this approach effectively. Links to all previous articles in the series are provided at the end of this page.

The Report Section Print Event Subroutines.

This is a repeat of the two Blog Posts published earlier on Highlighting Report Lines conditionally, as part of the new concept of Streamlining Form/Report Module Code in the Standalone Class Module. This demonstrates how to transfer the existing Event Subroutine Codes from the Report Module to the Standalone Class Module. Generally speaking, this is double work, and nobody would like to go into the Code again, which was written for a particular task after incorporating several refinements and final touches.

But, I think if you would like to do some trial runs and try to convert a few existing Form/Report Module Codes to the new way of coding, you will know how much extra work you put into the traditional method, and it will give you more insights into the new way of coding, in comparison with the traditional way.

Organize the Existing Form Module Code for the Standalone Class Module.

Before moving forward with this approach, it is essential to first organize the existing code in the Form Module Code by grouping it by the Control types.
Create a copy of the Form you want to experiment with and give it a new name. Then, plan to transfer all of its TextBox Event Subroutine code into a dedicated TextBox Wrapper Class as the first step.
In preparation, move the TextBox Event Subroutines next to each other on the Form Module itself first. If a single TextBox has more than one Event Subroutine, then arrange them next to each other too.
Create a TextBox Wrapper Class.
Create the Intermediate Class Module and enable the Events of the TextBoxes in the Class_Init() Subroutine.
Copy and paste the TextBox Event Subroutines from the Form Module to the Wrapper TextBox Class and modify the Event Subroutine Names with the correct TextBox Object Instance name as Subroutine name Prefix. Use the Select Case . . . End Select structure inside each category of Event to position the Code that belongs to a particular TextBox on the Form.
Delete the Copied Subroutines from the Form Module.
At the Form Module global declaration area, define the Intermediate Class Object Instance and in the Form_Load() Event Procedure, pass the Me Form object to the Set Frm() Property Procedure.
Save the Form after changes.
Before we try this new Form with the Wrapper Class-based Event Subroutines in place, rename the original Form with a new name with a suffix of X or Y.
Rename the new Form to the Original name.
Open the Form as you do from your Main Menu, or whatever method is built into your Project, and ensure that all the Subroutines of all the TextBoxes on the Form function as before. If any errors, track, find, and correct them, and ensure everything works normally as before.
Repeat this process for all other controls on the Form.

The Traditional Way of Coding.

The traditional method of Coding is the best way for beginners to learn VBA, and that is how we all started learning VBA. My first PC was the Spectravideo Model SV-328 with built-in Microsoft Extended Basic, and I started learning the BASIC Language from there, writing programs and saving them to the audio Cassette Tapes, the cheapest mode of storage at that time.

It is interesting now to look at the Bootup Screen Image of this Computer, given below (courtesy of Wikipedia.org):

Then came the GW-Basic, QuickBASIC, Visual Basic, and VBA.

There is an interesting Computer Buying Guide that I have from 1986, published by the Consumer Guide Magazine, for Computers with a Price Range of $299 (Commodore 128) to $4550 (Kaypro 286i, with 512K RAM, Speed 6 MHz, 80286 microprocessor, compatible with IBM PC AT).

The configuration of the machines, their speed, and the Prices of the machines in those days are interesting to read and compare with the present-day machines.

The following two links to earlier Blog Posts on Report Module Coding highlight some Report lines on certain conditions during the Printing cycle of the Report.

1. Highlighting Reports. - August 16, 2007

2. WithEvents and Report Line Highlighting - June 21, 2019

Let us see how we can run the Report Print Event Subroutines Code in the Standalone Class Module.

The Students' Examination Result Report Image.

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

Note: If you attempt to run the Report (StudentHighlight_Normal) directly by double-clicking on the Report, the Report may open in the ReportPreview Mode and not in PrintPreview Mode. In that case, you will not find the display as shown above. Right-click on the Report, and select the PrintPreview option from the displayed Menu to view the Report correctly.

The Students’ Examination Report displays the final results. The minimum pass requirement is set at 60% (360 marks out of 600). For students who fall short of this threshold, their percentage scores are visually highlighted with a red ellipse around the marks. Additionally, a legend label is included in the report footer, drawn within a circle to indicate the meaning of the highlight.

To experiment with different pass mark percentages, a Form is provided with a TextBox where you can enter the desired pass percentage before launching the Exam Result Report in Print Preview mode. The specified percentage is passed to the Report through the OpenArgs parameter when the Report is opened. The sample Form image is shown below for reference.

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

The VBA Code run earlier in the Report Class Module is listed below for reference. This Code will be transferred to the Standalone Class Module to run the procedure in the new Coding method.

Option Compare Database
Option Explicit

Private Curval As Double
Private MinTarget As Double

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim intPrintCircle As Boolean

On Error GoTo secRpt_Print_Err

MinTarget = Me!PassPercentage
Curval = Me!Percentage

If (MinTarget > 0) And (Curval < MinTarget) Then
  intPrintCircle = True
Else
  intPrintCircle = False
End If

DrawCircle intPrintCircle, Me.Percentage

secRpt_Print_Exit:
Exit Sub

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

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
Dim yn As Boolean, lbl As Control

On Error GoTo secFutr_Print_Err

yn = True 'set the flag false to draw oval shape
Set lbl = Me.Legend 'pass label control in Page Footer

DrawCircle yn, lbl 'draw circle in legend label

secFutr_Print_Exit:
Exit Sub

secFutr_Print_Err:
MsgBox Err.Description, , "secFutr_Print"
Resume secFutr_Print_Exit

End Sub

Private Sub DrawCircle(ByVal bool As Boolean, ovlCtl As Control)
Dim ctl As Control
Dim bolPrintCircle As Boolean
Dim sngAspect As Single
Dim intShapeHeight As Integer
Dim intShapeWidth As Integer
Dim sngXCoord As Single
Dim sngYCoord As Single

On Error GoTo DrawCircle_Err

If bool Then 'if pass no highlighting, change logic for pass cases
    bolPrintCircle = True
Else 'highlight failed cases
    bolPrintCircle = False
End If

Set ctl = ovlCtl
        
    If Not IsNull(ctl) Then
        If bolPrintCircle Then
           ' change this value to adjust the oval shape of the circle.
            sngAspect = 0.25
   
            ' Determine coordinates of ctl and to draw ellipse.
            ' Determine height and width of ellipse.
            intShapeHeight = ctl.Height
            intShapeWidth = ctl.Width
    
            'calculate circle vertical Y coordinate
            sngYCoord = ctl.Top + (intShapeHeight \ 2)

            'calculate horizontal X coordinate of circile
            sngXCoord = ctl.Left + (intShapeWidth \ 2)
            
            'draw an ellipse around the Total TextBox
            Report.Circle (sngXCoord, sngYCoord), intShapeWidth \ 2, RGB(255, 0, 0), , , sngAspect
            bolPrintCircle = False
        End If
    End If

DrawCircle_Exit:
Exit Sub

DrawCircle_Err:
MsgBox Err.Description, , "DrawCircle()"
Resume DrawCircle_Exit
End Sub

Review of the VBA Code in the Report Class Module.

The Report uses two Event Subroutines: Detail_Print() and ReportFooter_Print(). In both cases, the DrawCircle() Subroutine is invoked to highlight specific areas. Within the Detail_Print() event, it draws an ellipse around the percentage marks in the TextBox when the student’s score falls below the pass criteria. The threshold value is supplied through the OpenArgs parameter passed from the Form’s TextBox at the time the Report is opened. Similarly, in the ReportFooter_Print() event, the same Subroutine is used to draw the legend circle around the Label in the Report Footer.

The Print Event Subroutines are written directly in the Report Module StudentHighLight_Normal, as is normally done in Access. This demonstration Report can be executed directly from the sample database provided at the end of this page. Simply open the Form shown above and click the Command Button labeled “Normal Report” to view the result.

The Changed VBA Code in the Standalone Class Module.

The same Event Subroutines and the DrawCircle() Subroutine Codes are removed from the Report Class Module and placed in the Standalone Wrapper Class Module as given below:

Option Compare Database
Option Explicit

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

Private pct As Access.TextBox  'Percentage
Private lgnd As Access.Label
Private Pass As Integer 'OpenArgs Value
'------------------------------------------------------
'Streamlining Report Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Streamlining Report Module Code
'Author: a.p.r. pillai
'Date  : 12/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 mRpt_Err
Const strEvent = "[Event Procedure]"

'Pass Percentage value
  Pass = Nz(Rpt.OpenArgs, 0) 'OpenArgument
  Rpt.PassPercentage.Value = Pass
  
  With Rpt
     Set secRpt = .Section(acDetail)
     Set secFutr = .Section(acFooter)
     
     secRpt.OnPrint = strEvent  'Enable Event
     secFutr.OnPrint = strEvent '    "
  End With
  
  Set pct = Rpt!Percentage
  Set lgnd = Rpt.Legend
 
mRpt_Exit:
Exit Sub

mRpt_Err:
MsgBox Err.Description, , "mRpt()"
Resume mRpt_Exit

End Sub

'Draw ellipse around controls that meet specified criteria.
Private Sub secRpt_Print(Cancel As Integer, PrintCount As Integer)

Dim obtPct As Double
Dim yn As Boolean

On Error GoTo secRpt_Print_Err

    obtPct = pct.Value         'read Pass Percentage TextBox value
    yn = (obtPct < Pass)      'Passed or Not (TRUE/FALSE)

'call the DrawCircle Subroutine with Fail flag
'and the Control as parameters
    Call DrawCircle(yn, pct)

secRpt_Print_Exit:
Exit Sub

secRpt_Print_Err:
MsgBox Err.Description, , "secRpt_Print"
Resume secRpt_Print_Exit

End Sub

'Draw oval shape around Label in Report_Footer Section
Private Sub secFutr_Print(Cancel As Integer, PrintCount As Integer)
Dim yn As Boolean, lbl As Control

On Error GoTo secFutr_Print_Err

Set lbl = lgnd 'Fail label control in Page Footer
yn = True 'set the flag True to draw oval shape

Call DrawCircle(yn, lbl) 'draw circle in legend label

secFutr_Print_Exit:
Exit Sub

secFutr_Print_Err:
MsgBox Err.Description, , "secFutr_Print"
Resume secFutr_Print_Exit

End Sub

Private Sub DrawCircle(ByVal bool As Boolean, ovlCtl As Control)
Dim ctl As Control
Dim bolPrintCircle As Boolean
Dim sngAspect As Single
Dim intShapeHeight As Integer
Dim intShapeWidth As Integer
Dim sngXCoord As Single
Dim sngYCoord As Single

On Error GoTo DrawCircle_Err

If bool Then 'Highlight Failed Cases
    bolPrintCircle = True
Else
    bolPrintCircle = False
End If

Set ctl = ovlCtl
    If Not IsNull(ctl) Then
        If bolPrintCircle Then
        ' change this value to adjust the oval shape of the circle.
            sngAspect = 0.25
   
        ' Determine coordinates of ctl and to draw ellipse.
        ' Get height and width of Control.
            intShapeHeight = ctl.Height
            intShapeWidth = ctl.Width
    
        'calculate circle vertical Y coordinate
            sngYCoord = ctl.Top + (intShapeHeight \ 2)

        'calculate horizontal X coordinate of circile
            sngXCoord = ctl.Left + (intShapeWidth \ 2)
            
        'Draw an ellipse within the Percentage TextBox Boundaries
        'in Red Color
            Rpt.Circle (sngXCoord, sngYCoord), intShapeWidth \ 2, RGB(255, 0, 0), , , sngAspect
            bolPrintCircle = False
        End If
    End If

DrawCircle_Exit:
Exit Sub

DrawCircle_Err:
MsgBox Err.Description, , "DrawCircle()"
Resume DrawCircle_Exit

End Sub

Review of the Report Wrapper Class VBA Code.

To run the Standalone Class Module Code with the Report, we first need to obtain a reference to the open Report and assign it to the Report object instance Rpt within our Standalone Class Module. Once this is set, the Event procedures can be captured and executed outside the Report’s own module. Let us now walk through the code from the beginning up to the Class_Init() segment.

Option Compare Database
Option Explicit

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

Private pct As Access.TextBox  'Percentage
Private lgnd As Access.Label
Private Pass As Integer 'OpenArgs Value
'------------------------------------------------------
'Streamlining Report Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Streamlining Report Module Code
'Author: a.p.r. pillai
'Date  : 12/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 mRpt_Err
Const strEvent = "[Event Procedure]"

'Pass Percentage value
  Pass = Nz(Rpt.OpenArgs, 0) 'OpenArgument
  Rpt.PassPercentage.Value = Pass
  
  With Rpt
     Set secRpt = .Section(acDetail)
     Set secFutr = .Section(acFooter)
     
     secRpt.OnPrint = strEvent  'Enable Event
     secFutr.OnPrint = strEvent '    "
  End With
  
  Set pct = Rpt.Percentage
  Set lgnd = Rpt.Legend
 
mRpt_Exit:
Exit Sub

mRpt_Err:
MsgBox Err.Description, , "mRpt()"
Resume mRpt_Exit

End Sub

The first three lines, immediately following Option Explicit, declare a Report object instance named Rpt, qualified with the WithEvents keyword to capture the Report’s events. In addition, two Report Section objects (Access.[_SectionInReport]) are declared, followed by two Report Control objects and an Integer variable.

Next, the Set and Get Property Procedures for the Report object are defined. Once the Report object is assigned to the Rpt instance variable, the Set mRpt() Property Procedure calls the Class_Init() Subroutine to initialize the class.

The strEvent A constant variable is assigned the string value "[Event Procedure]". The statement


Pass = Nz(Rpt.OpenArgs, 0)

reads the Pass Mark percentage passed through the DoCmd.OpenReport call from the Form and stores it in the Integer variable Pass. The next line assigns this value to the PassPercentage TextBox on the Report.

Next, the Report Detail Section and Footer Section References are assigned to the secRpt and secFutr Section Objects, respectively. The next two lines of Code enable printing events.

The statement Set pct = 'Rpt.Percentage' assigns the Obtained Marks Percentage TextBox reference, and the 'lgnd' Label Object is assigned with the Reference of the Legend label control at the Report Footer.

These initialisation steps run in the standalone Class Module when the Report is opened from the cmdClass Click Event Subroutine.

Private Sub cmdClass_Click()
    If Nz(Me.Pass, 0) = 0 Then
        MsgBox "Enter pass-Mark Percentage?" & vbCr & "e.g.: 60"
    Else
        'Me.Pass - Pass Mark Percentage value passed as OpenArgs
        DoCmd.OpenReport "StudentsHighlight_Class", acViewPreview, , , , Me.Pass
    End If
End Sub

Report Class Module VBA Code.

In the Report Class Module, declare an Instance of the Standalone Class Module Object ClsStudentHighlight with the name Marks in the global area. The New keyword in Private Marks As New ClsStudentHighlight statement creates an instance of the Standalone Class Module in memory.

In the Report_Load() Event Subroutine, the current Report Object Me is passed to the Set mRpt() Property Procedure in the ClsStudentHighlight Class Module Object.

Option Compare Database
Option Explicit

Private Marks As New ClsStudentHighlight

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

The Report Detail_Print() Event.

When the Report is open, the ClsStudentHighlight standalone Class Module Instance is also open in memory by the Report Module global declaration statement. The current Report Reference is passed to the Set mRpt() Property Procedure, and the standalone Class Module Code starts executing.

'Draw ellipse around controls that meet specified criteria.
Private Sub secRpt_Print(Cancel As Integer, PrintCount As Integer)

Dim obtPct As Double
Dim yn As Boolean

On Error GoTo secRpt_Print_Err

    obtPct = pct.Value         'read Pass Percentage TextBox value
    yn = (obtPct < Pass)      'Passed or Not (TRUE/FALSE)

'call the DrawCircle Subroutine with Fail flag
'and the Control as parameters
    Call DrawCircle(yn, pct)

secRpt_Print_Exit:
Exit Sub

secRpt_Print_Err:
MsgBox Err.Description, , "secRpt_Print"
Resume secRpt_Print_Exit

End Sub

When the Report is open, two major events occur: the Report Format Event and the Report Print Event. The Report Format Event organizes the data line by line and determines how many lines of data can fit on a single page, taking into account the paper size, the design height of the Detail section, and the page orientation.

In contrast, the highlighting of Exam Failed cases is performed exclusively during the Report Print Event, ensuring that the visual emphasis is applied at the time of printing or print-previewing the Report. There are two Variables declared for reading the Obtained Percentage of Marks from the Percentage TextBox on the Report into the Variable obtPct. If the obtained mark is less than the Pass Mark percentage (received in the OpenArgs), then the Boolean Variable yn value is set as TRUE.

Next, the DrawCircle() Subroutine is called, with the yn Test Result and pct Percentage TextBox object as parameters, to draw a red oval shape around the Percentage TextBox on the Report, indicating that the student is not successful in their examination.

The DrawCircle() Subroutine Code.

Private Sub DrawCircle(ByVal bool As Boolean, ovlCtl As Control)
Dim ctl As Control
Dim bolPrintCircle As Boolean
Dim sngAspect As Single
Dim intShapeHeight As Integer
Dim intShapeWidth As Integer
Dim sngXCoord As Single
Dim sngYCoord As Single

On Error GoTo DrawCircle_Err

If bool Then 'Highlight Failed Cases
    bolPrintCircle = True
Else
    bolPrintCircle = False
End If

Set ctl = ovlCtl
    If Not IsNull(ctl) Then
        If bolPrintCircle Then
        ' Increase/Decrease this value to adjust the oval shape of the circle.
            sngAspect = 0.25
   
        ' Calculate the coordinates of the ctl to draw the ellipse.
        ' Get height and width of Control.
            intShapeHeight = ctl.Height
            intShapeWidth = ctl.Width
    
        'calculate circle vertical Y coordinate
            sngYCoord = ctl.Top + (intShapeHeight \ 2)

        'calculate horizontal X coordinate of circle
            sngXCoord = ctl.Left + (intShapeWidth \ 2)
            
        'Draw an ellipse within the Percentage TextBox Boundaries
        'in Red Color
            Rpt.Circle (sngXCoord, sngYCoord), intShapeWidth \ 2, RGB(255, 0, 0), , , sngAspect
            bolPrintCircle = False
        End If
    End If

DrawCircle_Exit:
Exit Sub

DrawCircle_Err:
MsgBox Err.Description, , "DrawCircle()"
Resume DrawCircle_Exit

End Sub

The DrawCircle() Subroutine VBA Code Review.

At the beginning of the routine, the essential variables are declared. If the first parameter bool is TRUE, the local variable bolPrintCircle is set TRUE to indicate that an ellipse should be drawn.

The sngAspect variable is initialized with the value 0.25, which creates an oval shape around the TextBox. This value can be adjusted—either increased or decreased—to ensure that the ellipse fits neatly within the TextBox’s dimensions without overflowing. The actual Width and Height of the TextBox are captured and stored in the variables intShapeWidth , and intShapeHeight respectively, to guide the drawing of the ellipse.

The next two statements calculate the center point (X and Y coordinates) of the ellipse (oval shape).

The Rpt.Circle() statement Draws the Circle in red using the value intShapeWidth\2 as the Radius Value of the Circle.

The ReportFooter_Print() Event Subroutine.

'Draw oval shape around Label in Report_Footer Section
Private Sub secFutr_Print(Cancel As Integer, PrintCount As Integer)
Dim yn As Boolean, lbl As Control

On Error GoTo secFutr_Print_Err

Set lbl = lgnd 'Fail label control in Page Footer
yn = True 'set the flag True to draw oval shape

Call DrawCircle(yn, lbl) 'draw circle in legend label

secFutr_Print_Exit:
Exit Sub

secFutr_Print_Err:
MsgBox Err.Description, , "secFutr_Print"
Resume secFutr_Print_Exit

End Sub

The ReportFooter_Print() Subroutine Calls the DrawCircle() Subroutine to draw the ellipse within the Legend Label Control.

Demo Database Download Link.

MarksHighlight.zip

LEARN MS-ACCESS TIPS AND TRICKS

Tuesday, September 12, 2023