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

Introduction.

Report Module VBA Code in Standalone Class Module.

After going through the earlier Episodes of this Topic: Streamlining Form Module Code in the Standalone Class Module, we could write the Event Subroutines, for frequently used Access Controls on the Form, in the Standalone Class Module without interfering with the Form design task. If you landed on this page directly and have not yet gone through the earlier sessions of this tutorial I suggest you start with the first page of this series Streamlining/Reusing Form Module Code for new Projects to understand this new concept and implementation methods fully. All the links to this Series of Articles are available 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 you start trying to go forward with this idea it is very important that you organize the existing Code of the Controls Category-wise together in the Form Module.
Make a Copy of the Form, you wish to try implementing the new method, with a new name. Plan to transfer this Form's all TextBox's Event Subroutine Code into a TextBox Wrapper Class first.
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 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 then find and correct them and see that 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 started learning the BASIC Language from there, and Writing Programs and saving them in Audio Cassette Tapes at that time, the cheapest mode of storage.

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, Quick BASIC, Visual Basic, and VBA.

There is a very interesting Computer Buying Guide from 1986 with me, published by the Consumer Guide Magazine, for Computers with a Price Range of $299 (Commodore 128) to $4550 (Kaypro 286i, with 512K RAM, 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 very interesting to read and compare with the present-day machines.

Coming back to the topic the following two links of 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.

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 List with their Final Examination Result published in the form of a Report shows that the minimum Pass Marks required is 60% (360 Marks) out of 600 Total marks. The unsuccessful students' cases are marked with a Red ellipse around the obtained marks percentage. The Legend Label at the Footer of the Report is also drawn with a Circle within the dimension of the Label.

To try the procedure with different Pass Mark Percentages there is a Form with a TextBox to input the Pass Mark Percentage before launching the Exam Result Report for Print Previewing. The Pass Mark Percentage is passed in the Open-Argument parameter to the Report opening Command. The Form Image is given below for reference.

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.

There are two Event Subroutines, the Detail_Print() and the ReportFooter_Print() Subroutines. Then the DrawCircle() Subroutine that is called from both Detail_Print() and ReportFooter_Print() Event Subroutines to draw the ellipse around the data in the TextBox on the Report where the Marks Percentage doesn't meet the Criteria, passed as Report Open Argument from the TextBox on the Form.

The Print_Event Subroutines Code is written on the Report StudentHighLight_Normal Module as we normally do. This demo Report can be run directly, from the Demo Database given at the end of this Page for download, by Clicking on the Command Button, on the Form shown above, with the Caption Normal Report.

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 execute the Standalone Class Module Code, when the Report is open, we must get the reference of the Report and assign it to the Report object Instance Rpt in our Standalone Class Module. Let us review the Code from the beginning 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, below the Option Explicit line, declare a Report Object Instance with the object name Rpt and qualified with the keyword WithEvents to capture the Report Events. Similarly, two Report Sections Access.[_SectionInReport] are declared, followed by two Report Control objects, and an Integer Variable.

Next the Set/Get Property Procedures for the Report Object. After the Report object is assigned to the Rpt object instance variable, the Set mRpt() Property Procedure Calls the Class_Init() Subroutine.

The strEvent Constant Variable is assigned with the string value "[Event Procedure]". The statement Pass = Nz(Rpt.OpenArgs, 0) reads the Pass Mark percentage passed as Report Open Argument through the DoCmd.OpenReport from the Form is saved 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 the Print 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 Open 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

There are two major Events that take place when the Report is Open, the Report Format Event and the Report Print Event. The Report Format Event lays the data line by line and calculates how many lines can go into one Page based on the type of Paper Size, the design height of the Detail Section, and based on Page Orientation. We will highlight the Exam Failed cases in the Report Print Event execution time only.

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 the Percentage TextBox object as parameters, to draw an oval shape in Red around the Percentage TextBox on the Report indicating that the student is not successful in his/her 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, the essential Variables are declared. The first parameter bool value is TRUE then the local Variable bolPrintCircle Variable is Set to True to draw the ellipse..

The sngAspect is initialized with the value 0.25 which gives an oval shape around the TextBox. This value can be increased or decreased to keep the shape within the control's dimensions without overflowing the boundaries of the Percentage TextBox. This depends on the Width and Height of the TextBox, and these values are taken and stored in the intShapeHeight and intShapeWidth Variables.

The next two statements calculate the center point (X, and Y coordinates) of the ellipse (oval shape) 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