Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Streamlining Form Module Code Part 14

All Frequently Used Controls on a Form.

We learned how to capture the Event fired from the Form or Report Controls and write the Event Subroutine VBA Code in the Control Wrapper Classes. The control Instances created in the Wrapper Class are assigned with the References of the Controls on the Form. Besides that, any other control on the Form is accessible from the Wrapper Class, to save or retrieve their values. Run Animations from Wrapper Class on the Form, using other controls or their content, mimic a digital clock on the Form, run a countdown before closing the Form and so on. 

For this kind of activities, we always include a Form Object Instance in all the Wrapper Classes and assign the active Form reference to the Form object Instance. For running a Form_Timer() Event or any other Form Event Procedure like MouseMove on the Detail Section of the Form the Form Instance declaration must be qualified with the keyword WithEvents in the Wrapper Class. Any of the above mentioned activities can be run from any Control's Wrapper Class, because the Form object Instance is included. You can declare it with or without the WithEvents keyword. But if you need to capture the Form Event then declare it with the keyword WithEvents.

The Form Detail Section declaration is as given below:

Private frm as Access.Form
Private WithEvents SecDetail As Access.Section

Set SecDetail = frm.Section(acDetail)
	SecDetail.OnMouseMove = "[Event Procedure]"

Report Detail Section Declaration and Reference assignments are like the following:

Private WithEvents Rpt as Access.Report
Private WithEvents RptSec as Access.[_SectionInReport]

Set RptSec = Rpt.Section(acDetail)

We could keep the Event Subroutine VBA Code away from the Form and Report Modules and work with the VBA Code independently in the standalone Class Module. The Collection object plays a major role in keeping several instances of the same type of object in memory after enabling them with required Events, like the TextBox on the Form, without interfering with the Form Design task. 

The earlier Episode Number Seven is a classic example that shows the power of this new approach of Form/Report Module Coding in the Standalone Class Module, which automates a major part of the manual Coding work with a single GotFocus() and LostFocus() pair for any number of TextBoxes added on the Form. Similarly, if there are ten TextBoxes on the Form that need the AfterUpdate() Event Subroutines with different Validation requirements for each of them, then all of them can be written in a single AfterUpdate() Event Subroutine in the stand-alone Class Module. This rule is applicable to all Types of Controls on the Form as well.

The Event Procedure Codes are better organised in Wrapper Classes, easily reachable for maintenance, debugging without interfering with the Form design. 

Private Sub Txt_AfterUpdate()
  Select Case Txt.Name
  	 Case "Quantity"
     	'Code
     Case "UnitPrice"
     	'Code
     Case "TaxRate"
     	'Code
     Case . . .
     
  End Select
End Sub

If any of the TextBox's AfterUpdate Events need any change to the Code or any other new TextBox needs to implement the AfterUpdate Event Subroutine then no need to open the Form in Design View, select the TextBox Control, display the Property Sheet, look for the Event Property and click on the build . . . Button to open the specific Event Subroutine to write/modify the Code. All we need to do is to open the Class Module directly and make changes. Imagine how much time is wasted in the traditional way of Coding on each Control Type in the Form, attending more than once for the same control Event Subroutine in this way.

I am sure you will know the difference once you set your mind to pursue the new way of Coding technique, even though it takes a bit of time to get oriented with the new concept if you are a beginner in VBA Coding.

The Form Image given below is designed with some of the Controls designed in the earlier Episodes and the Wrapper Class Modules are Imported for running in this Form's Control Event Procedures.  Animations of Text, a Digital Clock, and a Count-down display before closing the Form are also included in the Class Module to run on the Form.  

You can do anything from the stand-alone Class Module as you do normally in the Form Module. Once you set your mind to do things differently, effortlessly, and better organized with an outlook to reuse the VBA Code with or without minor changes then you will know the difference.

Anything for the first time is difficult, that's how we all started learning VBA Coding, spending many minutes or hours on trial and error basis for solving a problem. I retired from service 13 years ago and still trying to learn new things and share with others. 

New technology emerges every day and even kids aged 10 to 15 years of age (lucky kids) started to do Coding practices and App developments! 

I was exposed to some form of Computer-related automation only at the age of 27. The Key Punch Machine's Panel Wiring task for automatically skipping or duplicating some columns of 80 column Punch Card, the input medium of Programs and Data at that time, in the Indian Navy Defence Establishment. The Main-Frame Computer was an ICL 1901 Machine with Disk Drives, the Programming Languages were COBOL and FORTRAN, and I learned a bit of COBOL Language at that time (1975) too. 

If you need some Class Module beginner lessons they are available starting with this Link: MS-Access Class Modules and VBA series of few Articles.

The Demo Database With All Frequently Used Controls.

The attached sample Demo Database contains the following Form, the download link is available at the end of this page, and the main Form (frmControls_All) Image is given below.

The top left side TabControl-based Menu has three layers of options on three Tab Pages. The Tab Page Style is hidden by selecting its Property setting to None. The Menu Pages are selected with separate Command Button Clicks. The TabPagee change Event will be fired on the Command Button Clicks and will display a message with the Page Index Number. 

The three Menus are Tables, Forms, and Reports. The TabControl along with the Menu selection Command Buttons were Copied from the earlier episode Form and Pasted on this Form and made some changes to the Menu Options to replace with some other Forms and Reports presented in the earlier episodes. The related Class Modules are Imported to run them in this Database. 

Imported Class Modules of TabControl and the Menu changing Command Buttons.

The following Class Modules were Imported into this Database, from an earlier episode, to run the Menu options with a few changes:

TabLst_Object_Init
TabLst_CmdButton
TabLst_ListBox
TabLst_TabCtl

The Tab Page with the name TABLES has three options Employees, Orders, and Customer Tables. Double-click on an Item to open it. Before opening the Table a Female Voice will announce the Table Name before it appears on the Screen.

Employees and Orders Form.

On the FORMS TabPage, the first option is the Employees Form with Orders SubForm. It has a Search and Find TextBox in the Footer Section of the Employees Form. The EmployeeID number is the Key for Search operations. If the Search was successful/or failed then a Label will flash a few seconds announcing the search operations result. Try the EmployeeID number above 9 to test the Failed result.

Note: You may try to implement the Female Voice to announce the success or failure of the search operation.

The above Form and its VBA Codes are running from the following Class Modules, with the Emp Prefixes:

EmpObject_Init
EmpCmdButton
EmpTextBox
EmpCombo

The Employee-wise Orders Freight Sales Analysis.

The second option on the Forms Menu is the Employee-wise Orders Freight-Sales Analysis Form (frm_OptionGroup2) with a Graph Chart. The Form has an Option Group Control with three Options; 1. Highest Freight Sales Value, 2. Lowest Freight Sales Value, and 3. Total Freight Sales Value. The result is displayed in a TextBox with an Animated Label Caption; moving Text from Right-to-Left.

The above form is driven by the two standalone Class Modules given below. Both the Form and Class Modules are imported from one of the earlier Episodes:

Opt_Object_Init
Opt_Frame2

The Option Group Subroutine VBA Code runs from the Opt_Frame2 Class Module. The Employee ComboBox, the Freight Value display TextBox, and the Command Button Click Event Subroutines are run from the Opt_Object_Init Class Module only. Separate Wrapper Classes are not created for them because the Control has only one Instance on the Form.

The Graph Chart on the Form.

The Graph Chart's Source Data is from OrderSummaryQ2 Query. The Source Data of this Query is taken from the OrderDetailQ2 Query.  The OrderDetailQ2 data is filtered from the Orders Table using the EmployeeID selection in the ComboBox with the name cboEmp.  There is a hidden TextBox on the Form with the name EID containing the expression: =[cboEmp] to copy the EmployeeID selected in the cboEmp ComboBox. This Value is used in the Link Master Field Property of the Graph Chart to reflect the change of Chart Value on EmployeeID selection in the cboEmp ComboBox.

The TabControl Page number three has three Report Options; 1. Employee Records, 2. Students Exam Failed cases highlighting, 3. Students Passed/Failed Listing from the same Report. 

The last two Reports and their Class Modules are Imported into this Database from earlier Episodes. The following Stand-alone Class Modules run the Event Subroutine VBA Code:

ClsStudentHighlight0
ClsStudentsList

The ClsStudentHighlight0 Class Module runs the Detail_Print Event Subroutine Code for the Report StudentsHighlight_Class0. 

The Report Image is given below:

Streamline Report Image

The ClsStudentsList Class Module-based Detail_Format() and Report_Page() Event Subroutines are run and the PageBorder() Function is called from the Standard Module to draw two Report Page Border lines on each page of the Report. The ellipse is drawn around the TextBox in the Detail_Print() Event of the Report.

The Students Exam Passed List Image is given below.

The Exam Failed Cases Listing is taken from the same Report. Both these Report listings are prepared, by hiding the Report Detail Section for those records that don't meet the Criteria, in the Detail_Format() Event. 

The SubForm on the Main Form.

Next, there is a SubForm with three TextBoxes. The Quantity TextBox accepts a value range of 1 to 10 only.  The Unit Price Value should be non-zero. The entered values in these TextBoxes are validated in the OnExit() Event Subroutine in the ClsText Class Module before accepting them into the TextBoxes. The OnGotFocus() Event Subroutine of the TotalPrice TextBox calculates the Total Price value (Quantity * UnitPrice) and inserts it into the TotalPrice TextBox. The result value is displayed in the Label Control above the SubForm too.  

The SubForm is introduced here to demonstrate how to scan for the SubForm Controls and enable their required Events in the Class_Init() Subroutine (in the ClsControls_All Class Module). There are two For . . . Next Loop, one starts with the statement:

For Each ctl In fom.mySubForm.Form.Controls

for the SubForm scanning and the other starts with:

For Each ctl In fom.Controls

to scan for the required controls and enable their Event Procedures. 

Note: If you place any TextBox on the TabControl Pages they will be treated as controls on the Form only and see that their names do not conflict with the other TextBox Names on the Form.

The ComboBox on the Form has a list of Country names. When a Country Name is selected in the ComboBox its list index number is used for selecting the Capital of that Country from the ListBox automatically. If you Click on an item in the ListBox it will display the selected value in a MsgBox to indicate that the Click Event is captured in the Class Module.

The Option Group Control.

Next, the Option Group Control is not connected with any specific function except when one of the Radio Buttons is clicked it will display the index number of the option as an indication that the selected Event is fired and captured in the Option Group Class Module ClsOption

The Command Button (with the Caption Employee Orders) opens the Form with the Graph Chart (included in the TabControl Menu) directly. The next Command Button with the caption 'Heading Color' changes the Form Heading top Label Control's ForeColor.

The Close Command Button Click runs a 10 Seconds Count-Down on the Label Control above the SubForm. When it is zero the Form frmControls_All will close.

The Main Form-based control's Class Module names start with the prefix Cls...

You can see the List of these Class Modules in the declaration area of the ClsControlls_All Class module.

Private tx As ClsText
Private cmd As ClsCmdButton
Private cbo As Clscombo
Private Lst As ClsListBox
Private Opt As ClsOption

Demo Database Download Link:


Streamlining Form Module Code in Standalone Class Module.

  1. Reusing Form Module VBA Code for New Projects.
  2. Streamlining Form Module Code - Part Two.
  3. Streamlining Form Module Code - Part Three
  4. Streamlining Form Module Code - Part Four
  5. Streamlining Form Module Code - Part Five
  6. Streamlining Form Module Code - Part Six
  7. Streamlining Form Module Code - Part Seven
  8. Streamlining Form Module Code - Part Eight
  9. Streamlining Form Module Code - Part Nine
  10. Streamlining Form Module Code - Part Ten
  11. Streamlining Form Module Code - Part Elevan
  12. Streamlining Report Module Code in Class Module
  13. Streamlining Module Code Report Line Hiding-13.
  14. Streamlining Form Module Code Part-14.
  15. Streamlining Custom Made Form Wizard-15.
  16. Streamlining VBA Custom Made Report Wizard-16.
  17. Streamlining VBA External Files List in Hyperlinks-17
  18. Streamlining Events VBA 3D Text Wizard-18
  19. Streamlining Events VBA RGB Color Wizard-19
  20. Streamlining Events Numbers to Words-20
  21. Access Users Group(Europe) Presentation-21
  22. The Event Firing Mechanism of MS Access-22
  23. One TextBox and Three Wrapper Class Instances-23
  24. Streamlining Code Synchronized Floating Popup Form-24
  25. Streamlining Code Compacting/Repair Database-25
  26. Streamlining Code Remainder Popup Form-26
  27. Streamlining Code Editing Data in Zoom-in Control-27
  28. Streamlining Code Filter By Character and Sort-28
  29. Table Query Records in Collection Object-29
  30. Class for All Data Entry Editing Forms-30
  31. Wrapper Class Module Creation Wizard-31
  32. wrapper-class-template-wizard-v2
Share:

Streamlining Report Module in Class Module-2

 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. 

  1. Exam Passed Students Listing.

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

Demo Database Download Link. Change Database.

Streamlining Form Module Code in Standalone Class Module.

  1. Reusing Form Module VBA Code for New Projects.
  2. Streamlining Form Module Code - Part Two.
  3. Streamlining Form Module Code - Part Three
  4. Streamlining Form Module Code - Part Four
  5. Streamlining Form Module Code - Part Five
  6. Streamlining Form Module Code - Part Six
  7. Streamlining Form Module Code - Part Seven
  8. Streamlining Form Module Code - Part Eight
  9. Streamlining Form Module Code - Part Nine
  10. Streamlining Form Module Code - Part Ten
  11. Streamlining Form Module Code - Part Elevan
  12. Streamlining Report Module Code in Class Module
  13. Streamlining Module Code Report Line Hiding-13.
  14. Streamlining Form Module Code Part-14.
  15. Streamlining Custom Made Form Wizard-15.
  16. Streamlining VBA Custom Made Report Wizard-16.
  17. Streamlining VBA External Files List in Hyperlinks-17
  18. Streamlining Events VBA 3D Text Wizard-18
  19. Streamlining Events VBA RGB Color Wizard-19
  20. Streamlining Events Numbers to Words-20
  21. Access Users Group(Europe) Presentation-21
  22. The Event Firing Mechanism of MS Access-22
  23. One TextBox and Three Wrapper Class Instances-23
  24. Streamlining Code Synchronized Floating Popup Form-24
  25. Streamlining Code Compacting/Repair Database-25
  26. Streamlining Code Remainder Popup Form-26
  27. Streamlining Code Editing Data in Zoom-in Control-27
  28. Streamlining Code Filter By Character and Sort-28
  29. Table Query Records in Collection Object-29
  30. Class for All Data Entry Editing Forms-30
  31. Wrapper Class Module Creation Wizard-31
  32. wrapper-class-template-wizard-v2
Share:

Streamlining Report Module Code in Class Module.

 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.

  1. 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.
  2. 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.

  3. 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.
  4. Create a TextBox Wrapper Class.

  5. Create the Intermediate Class Module and enable the Events of the TextBoxes in the Class_Init() Subroutine.
  6. 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.

  7. Delete the Copied Subroutines from the Form Module.
  8. 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.

  9. Save the Form after changes.
  10. 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.

  11. Rename the new Form to the Original name.
  12. 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.

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

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

Streamline Report Module

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.


Streamlining Form Module Code in Standalone Class Module.

  1. Reusing Form Module VBA Code for New Projects.
  2. Streamlining Form Module Code - Part Two.
  3. Streamlining Form Module Code - Part Three
  4. Streamlining Form Module Code - Part Four
  5. Streamlining Form Module Code - Part Five
  6. Streamlining Form Module Code - Part Six
  7. Streamlining Form Module Code - Part Seven
  8. Streamlining Form Module Code - Part Eight
  9. Streamlining Form Module Code - Part Nine
  10. Streamlining Form Module Code - Part Ten
  11. Streamlining Form Module Code - Part Elevan
  12. Streamlining Report Module Code in Class Module
  13. Streamlining Module Code Report Line Hiding-13.
  14. Streamlining Form Module Code Part-14.
  15. Streamlining Custom Made Form Wizard-15.
  16. Streamlining VBA Custom Made Report Wizard-16.
  17. Streamlining VBA External Files List in Hyperlinks-17
  18. Streamlining Events VBA 3D Text Wizard-18
  19. Streamlining Events VBA RGB Color Wizard-19
  20. Streamlining Events Numbers to Words-20
  21. Access Users Group(Europe) Presentation-21
  22. The Event Firing Mechanism of MS Access-22
  23. One TextBox and Three Wrapper Class Instances-23
  24. Streamlining Code Synchronized Floating Popup Form-24
  25. Streamlining Code Compacting/Repair Database-25
  26. Streamlining Code Remainder Popup Form-26
  27. Streamlining Code Editing Data in Zoom-in Control-27
  28. Streamlining Code Filter By Character and Sort-28
  29. Table Query Records in Collection Object-29
  30. Class for All Data Entry Editing Forms-30
  31. Wrapper Class Module Creation Wizard-31
  32. wrapper-class-template-wizard-v2
Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code