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 write the Form or Report Module Event Subroutine VBA Code in the Stand-alone Class Module. Besides that, we can directly access any control, from the Wrapper Class, on the running  Form to save or retrieve their values. Run Animations on the Form using other controls or their content, and mimic a digital clock on the Form, run a countdown before closing the Form.  

For this kind of operation, we always include a Form Object Instance in all the Wrapper Classes we create and assign the active Form reference to the Form 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 operations can be run from any Control's Wrapper Classes.

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 learned that we can 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 a 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 one AfterUpdate() Event Subroutine in the stand-alone Class Module. This rule is applicable to all Types of Controls on the Form.

All the TextBox AfterUpdate() Events can go into this single Event Subroutine, rather than all of them scattered in the Form Module, mixed up with the other Controls' Event Subroutines.

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 this way on each Control Type in the Form, attending more than once for the same control Event Subroutine.

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 Clolor' 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 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:


  1. Re-using 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-Eleven.
  12. Streamlining Report Module Code in Class Module.
  13. Streamlining Report Module Code in Class Module-2.
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 options selection.

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

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

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

  1. Re-using 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-Eleven.
  12. Streamlining Report Module Code in Class Module.
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 inbuilt in your Project, and ensure that all the Subroutines of all the TextBoxes on the Form functions 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 on those days is 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 for highlighting 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, declares 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 only 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 Color 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, Y coordinates) of the ellipse (oval shape) shape.

The Rpt.Circle() statement Draws the Circle using the value intShapeWidth\2 as the Radius Value of the Circle with the Red Color.

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.

  1. Re-using Form Module VBA Coding for New Projects.
  2. Streamlining Form Module Coding Part-Two.
  3. Streamlining Form Module Coding Part-Three.
  4. Streamlining Form Module Coding Part-Four.
  5. Streamlining Form Module Coding Part-Five.
  6. Streamlining Form Module Coding Part-Six.
  7. Streamlining Form Module Coding Part-Seven.
  8. Streamlining Form Module Coding Part-Eight.
  9. Streamlining Form Module Coding Part-Nine.
  10. Streamlining Form Module Coding Part-Ten.
  11. Streamlining Form Module Coding Part-Eleven.
Share:

Streamlining Form Module Code - Part Eleven

Introduction.

In this Episode of Streamlining Form Module VBA Code, we will create Wrapper Classes for ComboBox and OptionGroup Controls. Having gone through the previous Episodes, you are now acquainted with the process of creating Wrapper Class Modules for other controls, such as TextBox, ListBox, CommandButton, and TabControl. You have also learned to write Event Subroutines within these modules instead of placing them in the Form Module.

ComboBox and OptionGroup Control.

This time we will learn the usage of ComboBox and OptionGroup Controls as shown in the form image given below.

The Order Detail data in the ListBox are filtered in OrderDetailQ1 based on the Employee ID selected in the ComboBox with the name cboEmp above the ListBox Control. The SQL of the Query is given below:

OrderDetailQ1 SQL.

SELECT Orders.EmployeeID, Employees.LastName, Orders.OrderID, Val(Format([OrderDate],"yyyy")) AS [Year], Orders.Freight
FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
WHERE (((Orders.EmployeeID)=[Forms]![Frm_OptionGroup]![cboEmp]));

The Freight Value in OrderDetailQ1 is summarised Year-wise in OrderSummaryQ1.  The OrderSummaryQ1 Query has the year-wise Freight Value Source Data for the Graph Chart on the Form. OrderSummary SQL is given below.

OrderSummaryQ1 SQL.

SELECT [OrderDetailQ1].EmployeeID, [OrderDetailQ1].LastName, [OrderDetailQ1].Year, Sum([OrderDetailQ1].freight) AS Freight
FROM OrderDetailQ1
GROUP BY [OrderDetailQ1].EmployeeID, [OrderDetailQ1].LastName, [OrderDetailQ1].Year;

Freight Summary Data for the Chart.

Employee	Last Name	Year	Freight
Davolio, Nancy	Davolio	        1996	₹ 1,871.09
Davolio, Nancy	Davolio	        1997	₹ 4,584.47
Davolio, Nancy	Davolio	        1998	₹ 2,381.13

How ComboBox, ListBox, and the Chart linked Together?

When the Employee Code is selected in the ComboBox the AfterUpdate Event is fired and the statement cbofrm.List0.Requery updates the ListBox contents.

There is a hidden Unbound TextBox on the Form with an expression to copy the changed Employee ID Value from the cboEmp ComboBox. This is used for the "Link Master Field" Property in the Graph Chart to update the Freight Year-wise Summary Data in the Graph Chart.

So, by changing the Employee ID (Combobox displays the Employee Name only, and the first column EmployeeID width Property value is set to zero) the ComboBox refreshes the ListBox OrderDetail and the Freight Year-wise Values on the Graph Chart instantly.

The OptionGroup Control.

The OptionsGroup Control can have a group of Radio Buttons or Check Boxes or Toggle Buttons and all of them are placed within a Frame. Here we are using the Radio Buttons within the OptionGroup Frame with the name Frame7. The Frame7 has three Radio Buttons with their Labels and is placed below the ListBox and Graph Chart objects.  

There are three options to display the Employee's Freight Sales Values in three different categories.

  1. - The highest Freight Sales Value of the Employee.
  2. - The Lowest Freight Sales Value.
  3. - The Total Fright Sales Value.

The Unbound TextBox at the left side of the OptionGroup Control will display the Value when the option Radio Button is selected. The selected option description will appear in a label control, at the left side of the Unbound TextBox in an animated style, by moving the Text from right to left.

The Command Button Click will close the Form.

We already created wrapper classes for the ComboBox and ListBox in the earlier episodes. The OptionGroup Control is new in this Series of Tutorials and needs a Wrapper Class. When we place an OptionGroup Control on the Form the default name used by Microsoft Access is something like Frame7. So we will create a Wrapper Class for the OptionGroup Control with the name OptFrame

The OptFrame Wrapper Class VBA Code.

Option Compare Database
Option Explicit

Private WithEvents Opt As Access.OptionGroup
Private frm As Access.Form

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'OptionGroup Wrapper Class
'Author: a.p.r. pillai
'Date  : 31/08/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

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

Public Property Set opt_Frm(ByRef ofrm As Form)
    Set frm = ofrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get o_opt() As OptionGroup
    Set o_opt = Opt
End Property

Public Property Set o_opt(ByRef mopt As OptionGroup)
    Set Opt = mopt
End Property

'Event Subroutines Code
Private Sub opt_Click()
Dim Rslt As Variant
Dim Cap(1 To 3) As String
Static strText As String

Cap(1) = "Highest Freight Value:"
Cap(2) = " Lowest Freight Value:"
Cap(3) = "  Total Freight Value:"

Select Case Opt.Name
    Case "Frame7"
        Select Case Opt.Value
            Case 1
            'Repeated Clicks on the same option is ignored.
            If strText = Cap(1) Then Exit Sub
                Rslt = DMax("Freight", "OrderDetailQ1")
            Case 2
                If strText = Cap(2) Then Exit Sub
                Rslt = DMin("Freight", "OrderDetailQ1")
                
            Case 3
                If strText = Cap(3) Then Exit Sub
                Rslt = DSum("Freight", "OrderDetailQ1")
        End Select
End Select
       frm!Result = Rslt
       strText = Cap(Opt)
       
       Call Animate(strText) 'Label Animation
End Sub

Private Sub Animate(ByVal txt As String)
'Label Animation
Dim L As Double
Dim n As String
Dim T As Double
Dim j As Integer

L = Len(txt)
txt = Space(L) & txt

For j = 1 To Len(txt) - L
 n = Left(txt, 1)
 txt = Right(txt, Len(txt) - 1)
 txt = txt & n
 frm.lblResult.Caption = Left(txt, L)

    Delay 0.02 'delay 20 milliseconds

Next
End Sub

The OptionGroup Control object Opt Property is qualified with the Keyword WithEvents is declared in the global area of the Class Module. The Wrapper Class Module name is OptFrame. There is a Form object Property with the name frm also declared in the Global Area followed by the Property Procedures for the Global Properties.

The Opt_Click() Event Subroutine.

Private Sub opt_Click()
Dim Rslt As Variant
Dim Cap(1 To 3) As String
Static strText As String

Cap(1) = "Highest Freight Value:"
Cap(2) = " Lowest Freight Value:"
Cap(3) = "  Total Freight Value:"

Select Case Opt.Name
    Case "Frame7"
        Select Case Opt.Value
            Case 1
                If strText = Cap(1) Then Exit Sub
                Rslt = DMax("Freight", "OrderDetailQ1")
            Case 2
                If strText = Cap(2) Then Exit Sub
                Rslt = DMin("Freight", "OrderDetailQ1")
                
            Case 3
                If strText = Cap(3) Then Exit Sub
                Rslt = DSum("Freight", "OrderDetailQ1")
        End Select
End Select
       frm!Result = Rslt
       strText = Cap(Opt)
       
       Call Animate(strText) 'Label Animation
End Sub

There are three options in the OptionGroup Control on the Form to extract the Highest, Lowest, and Total Freight Values from the Order Sales transactions, for the selected Employee ID in the ComboBox cboEmp.

The OptionGroup Button Click will retrieve the values from OrderDetailQ1 using the DMax(), DMin(), and DSum() Functions from the filtered Order Details data and display it in the Rslt TextBox, on the left side of the OptionGroup Control.

The displayed freight value's category description, defined in the Cap() Array is picked using the selected OptionGroup Control's Radio Button Index Number. This text is passed over to the Animate() Subroutine as the parameter. This is displayed on the Label control Caption at the left side of the TextBox Rslt. The Text is displayed in an animated style, exposing the description character by character moving from right to left till the full text is exposed.

The statement If strText = Cap(n) Then Exit Sub ignores the Animation from repeating when Clicked on the Body of the OptionGroup Frame.

The Label Animation.

The Animate() Subroutine Code segment is given below:

Private Sub Animate(ByVal txt As String)
'Label Animation
Dim L As Double
Dim n As String
Dim T As Double
Dim j As Integer

L = Len(txt)
txt = Space(L) & txt 'Add spaces at the left side

For j = 1 To Len(txt) - L
 n = Left(txt, 1)
 txt = Right(txt, Len(txt) - 1)
 txt = txt & n
 frm.lblResult.Caption = Left(txt, L)
 
 Delay 0.02 ' Pause 20 Milliseconds
 
 Next
End Sub

The length of the Parameter value in the Variable txt is calculated and stored in variable L. The parameter variable txt content is modified by adding an equal number of spaces of its original length at the left side.

The Animation Sequence.

The For . . . Next Loop is set to run to the original length in Variable L. In the next three lines of Code, removes one character from the left side of the String and add it to the right end of the String.  Then the leftmost L length of characters is displayed in the Caption Property of the lblResult Label Control. 

The next batch of characters display is delayed by 20 milliseconds and the same action is repeated till the complete description is displayed on the Label Control.

The Delay() Function VBA Code in the Standard Module is given below for information:

Public Sub Delay(ByVal Sleep As Double)
Dim T As Double

T = Timer
Do While Timer < T + Sleep
    DoEvents
Loop
End Sub

The OptObject_Init Class Module VBA Code.

Option Compare Database
Option Explicit

Private iFrm As Access.Form

Private LstB As OptListBox
Private txt As OptTextBox
Private Fram As OptFrame
Private wcbo As optCombo
Private wcmd As OptCmdButton

Private Coll As New Collection

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Combo and Option Group Controls
'Author: a.p.r. pillai
'Date  : 31/08/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

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

Public Property Set m_Frm(ByRef mfrm As Form)
    Set iFrm = mfrm
    
    iFrm.cboEmp.DefaultValue = iFrm.cboEmp.Column(0, 1)
    iFrm.List0.Requery

Call Class_Init
End Property

'Events Enabling Subroutine
Private Sub Class_Init()
Dim ctl As Control

Const EP = "[Event Procedure]"


For Each ctl In iFrm.Controls 'Scan for Controls

    Select Case TypeName(ctl)
            Case "OptionGroup"
              Select Case ctl.Name
                Case "Frame7" 'Option Group Name
                    Set Fram = New OptFrame 'Create Instance
                    Set Fram.opt_Frm = iFrm 'Assign Form Object
                    Set Fram.o_opt = ctl   'TextBox
                    
                        Fram.o_opt.OnClick = EP
                        
                    Coll.Add Fram 'Save EmpTextBox Class
                    Set Fram = Nothing 'Erase temp Instance
              End Select


        Case "ComboBox"
                    Set wcbo = New optCombo
                    Set wcbo.cbo_Frm = iFrm
                    Set wcbo.c_cbo = ctl
                    
                   wcbo.c_cbo.AfterUpdate = EP
                   wcbo.c_cbo.OnGotFocus = EP
                   wcbo.c_cbo.OnLostFocus = EP
                    
                    Coll.Add wcbo
                    Set wcbo = Nothing

        Case "TextBox"
                    Set txt = New OptTextBox
                    Set txt.tx_Frm = iFrm
                    Set txt.t_Txt = ctl
                    
                        txt.t_Txt.OnGotFocus = EP
                        txt.t_Txt.OnLostFocus = EP
                    
                    Coll.Add txt
                    Set txt = Nothing
                    
        Case "ListBox"
                    Set LstB = New OptListBox
                    Set LstB.lst_Frm = iFrm
                    Set LstB.m_Lst = ctl
                    
                        LstB.m_Lst.OnGotFocus = EP
                        LstB.m_Lst.OnLostFocus = EP
                    
                    Coll.Add LstB
                    Set LstB = Nothing
                    
        Case "CommandButton"
                    Set wcmd = New OptCmdButton
                    Set wcmd.cmd_Frm = iFrm
                    Set wcmd.c_cmd = ctl
                    
                   wcmd.c_cmd.OnClick = EP
                    
                    Coll.Add wcmd
                    Set wcmd = Nothing
    
    End Select
Next

End Sub

Private Sub Class_Terminate()
'Delete Collection Object contents
    Do While Coll.Count > 0
        Coll.Remove 1
    Loop
    
    Set iFrm = Nothing
End Sub

As usual, all the Access Object Wrapper Classes are defined as Properties in the Global Area of the Module followed by the Property Procedures. After the Form Object is assigned to the iFrm object, in the Set m_frm() Property Procedure two statements are executed for refreshing the ComboBox and ListBox objects to initialize with default values in both these Form Controls. From there the Class_Init() Subroutine is called.

The procedures written there are explained in detail in earlier episodes and I am sure you are well versed in the procedure.

But, I would like to draw your attention to the controls on the Form. There is only one instance each of Combobox, ListBox, TextBox, Command Button, OptionGroup Control, and Chart Object on the Form. Even though Chart Object also has several Events and can have a Wrapper Class, if we plan to capture those Events in a standalone Class Module.

When to create a Wrapper Class?

There may be more than one instance of a particular object of the same type on the Form (like TextBox) but if there is only one instance of any Control, that needs one or more Event Subroutines, then it is not necessary to use the Wrapper Class for it. We can create a single Instance of such Objects in the Declaration area of the OptObject_Init Class Module (the intermediate Class Module), qualified with the WithEvents keyword.  Then assign the Control instances references from the Form, enable their Events, and write the Event Subroutines in the OptObject_Init Class Module. 

We need a Wrapper Class only when more than one instance of the Objects of the same Type needs to be enabled with the Event Procedures.  In this frm_OptionGroup Form, there is only one Instance of the TextBox, ComboBox, ListBox, and Command Button on the Form.

Since the OptionGroup Control (Frame7) is the new entry, in the Streamlining of Form Module Code Series of Articles, we created a Wrapper Class OptFrame for that control alone, and for others we will create a single instance of each control qualified with the WithEvents keyword, to capture the Events in the OptObject_Init Class Module. They will be enabled with their required Events and write their Event Subroutines in this Intermediate Class Module.

Even though we have already created and used Wrapper Classes for these Objects earlier, using them in this case involves more VBA Code than necessary for a single instance for those Objects.

So, I have created two Forms for Demo purposes:

frm_OptionGroup - All Control's Wrapper Classes are used in the OptObject_Init Class

frm_OptionGroup2 - Only Opt_Frame Wrapper Class is in the Opt_Object_Init2 Class.

The Opt_Object_Init2 Class Module Code.

The frm_OptionGroup2 Form's Intermediate Class Module (Opt_Object_Init2) Code is given below:

Option Compare Database
Option Explicit

Private WithEvents txt As Access.TextBox
Private WithEvents cmd As Access.CommandButton
Private WithEvents cbo As Access.ComboBox
Private WithEvents Lst As Access.ListBox

Private Fram As Opt_Frame2
Private iFrm As Access.Form
Private Coll As New Collection

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Combo and Option Group Controls
'Author: a.p.r. pillai
'Date  : 31/08/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

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

Public Property Set m_Frm(ByRef mfrm As Form)
    Set iFrm = mfrm
    
    iFrm.cboEmp.DefaultValue = iFrm.cboEmp.Column(0, 1)
    iFrm.List0.Requery
    
    Set txt = iFrm.Result
    Set cmd = iFrm.cmdClose
    Set cbo = iFrm.cboEmp
    Set Lst = iFrm.List0
    
Call Class_Init
End Property

'Events Enabling Subroutine
Private Sub Class_Init()
Dim ctl As Control

Const EP = "[Event Procedure]"

'Scan for Form Controls
'and Enable the required Event Procedures
For Each ctl In iFrm.Controls 'Find TextBox, ComboBox & CommandButtons

    Select Case TypeName(ctl)
            Case "OptionGroup"
              Select Case ctl.Name
                Case "Frame7" 'Option Group Name
                    Set Fram = New Opt_Frame2 'Create Instance
                    Set Fram.opt_Frm = iFrm 'Assign Form Object
                    Set Fram.o_opt = ctl   'TextBox
                    
                        Fram.o_opt.OnClick = EP
                        
                    Coll.Add Fram 'Save EmpTextBox Class
                    Set Fram = Nothing 'Erase temp Instance
              End Select

        Case "CommandButton"
                   cmd.OnClick = EP
                    

        Case "ComboBox"
                   cbo.AfterUpdate = EP
                   cbo.OnGotFocus = EP
                   cbo.OnLostFocus = EP
          
        Case "TextBox"
            Select Case ctl.Name
                Case "Result"
                   txt.OnGotFocus = EP
                   txt.OnLostFocus = EP
            End Select
                    
        Case "ListBox"
                Lst.OnGotFocus = EP
                Lst.OnLostFocus = EP
                        
    End Select
Next

End Sub

Private Sub Class_Terminate()
'Delete Collection Object contents
    Do While Coll.Count > 0
        Coll.Remove 1
    Loop
    
    Set iFrm = Nothing
End Sub

'Event Subroutines
Private Sub txt_GotFocus()
        GFColor iFrm, txt 'Field Highlight
End Sub

Private Sub txt_LostFocus()
    LFColor iFrm, txt 'Field Highlight
End Sub

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

Private Sub cbo_GotFocus()
    GFColor iFrm, cbo 'ComboBox highlight
    
    'Reset OptionGroup to default settings
    iFrm.Frame7 = Null 'Reset earlier selection of OptionGroup option
    iFrm!lblResult.Caption = "Result"
    iFrm.Result.Value = 0
    
End Sub

Private Sub cbo_LostFocus()
    LFColor iFrm, cbo 'ComboBox highlight
End Sub

Private Sub cbo_AfterUpdate()
  iFrm.List0.Requery
  
End Sub

'Event Subroutines Code
Private Sub lst_GotFocus()
    GFColor iFrm, Lst 'ListBox highlight
End Sub

Private Sub lst_LostFocus()
    LFColor iFrm, Lst 'ListBox highlight
End Sub

Segmentwise Review of the VBA Code.

The Global Declaration Code segment is given below for review:

Option Compare Database
Option Explicit

Private WithEvents txt As Access.TextBox
Private WithEvents cmd As Access.CommandButton
Private WithEvents cbo As Access.ComboBox
Private WithEvents Lst As Access.ListBox

Private Fram As Opt_Frame2
Private iFrm As Access.Form
Private Coll As New Collection

All single object instance declarations are given at the global area of the Module, as we did at the beginning of this series of Tutorials.

Since OptionGroup Control is a new entry in this series its Wrapper Class is included here.  The Form and Collection Object declarations comes next. The Collection Object declaration is included only for the OptionGroup Control with the name Frame7.

The Property Procedure Segment.

Next, the iFrm's Property Procedure Code Segment is given below:

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

Public Property Set m_Frm(ByRef mfrm As Form)
    Set iFrm = mfrm
    
    'Set the ComboBox EmployeeID first item as default value
    iFrm.cboEmp.DefaultValue = iFrm.cboEmp.Column(0, 1)
    iFrm.List0.Requery 'Refresh the Order Details ListBox
    
    Set txt = iFrm.Result
    Set cmd = iFrm.cmdClose
    Set cbo = iFrm.cboEmp
    Set Lst = iFrm.List0
    
Call Class_Init
End Property

In the Set m_Frm() Property Procedure the ComboBox and ListBox default values are assigned when the Form is open.

Next, all the single object references on the Form are assigned to the Objects declared in the global area and then Called the Class_Init() Subroutine.

The Class_Init() Subroutine.

'Events Enabling Subroutine
Private Sub Class_Init()
Dim ctl As Control

Const EP = "[Event Procedure]"

'Scan for Form Controls
'and Enable the required Event Procedures
For Each ctl In iFrm.Controls 'Find TextBox, ComboBox & CommandButtons

    Select Case TypeName(ctl)
            Case "OptionGroup"
              Select Case ctl.Name
                Case "Frame7" 'Option Group Name
                    Set Fram = New Opt_Frame2 'Create Instance
                    Set Fram.opt_Frm = iFrm 'Assign Form Object
                    Set Fram.o_opt = ctl   'TextBox
                    
                        Fram.o_opt.OnClick = EP
                        
                    Coll.Add Fram 'Save EmpTextBox Class
                    Set Fram = Nothing 'Erase temp Instance
              End Select

        Case "CommandButton"
                   cmd.OnClick = EP
                    

        Case "ComboBox"
                   cbo.AfterUpdate = EP
                   cbo.OnGotFocus = EP
                   cbo.OnLostFocus = EP
          
        Case "TextBox"
            Select Case ctl.Name
                Case "Result"
                   txt.OnGotFocus = EP
                   txt.OnLostFocus = EP
            End Select
                   
        Case "ListBox"
                Lst.OnGotFocus = EP
                Lst.OnLostFocus = EP
                        
    End Select
Next

End Sub

The Class_Init() Subroutine starts with the usual way of scanning for the Controls and checks for the OptionGroup control with the name Frame7, enables the Click Event, and then adds it to the Collection Object.

Next, other Form Controls' references are already assigned in the Set m_frm() Property Procedure and are enabled with the Events. We will be writing the Event Subroutine Code in this Class Module itself.

Next, in the TextBox's case, we have another TextBox (EID) that is kept hidden on the Form. Even though it is hidden it will appear in the scanning cycle and it will be enabled with the Events. Since there is no Event Procedure Code for that TextBox in this Module it will not have any impact. But, we would like to check for that specific TextBox (Resul) for clarity and enable its Events. This will ignore the EID TextBox. Next, the ListBox is also enabled with the required Events.

The Event Subroutines of Single Control Instance Cases.

Next, the Event Subroutines Segment Code which runs in the Opt_Object_Init2 Class.

'Event Runs automatically when the Form is Closed.
Private Sub Class_Terminate()
'Delete Collection Object contents
    Do While Coll.Count > 0
        Coll.Remove 1
    Loop
    
    Set iFrm = Nothing
End Sub

'TextBox Event Subroutines for highlighting the control
Private Sub txt_GotFocus()
        GFColor iFrm, txt 'Field Highlight
End Sub

Private Sub txt_LostFocus()
    LFColor iFrm, txt 'Field Highlight
End Sub

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

'ComboBox Subroutines
Private Sub cbo_GotFocus()
    GFColor iFrm, cbo 'ComboBox highlight
    
    'Reset OptionGroup to default settings
    iFrm.Frame7 = Null 'Reset earlier selection of OptionGroup option
    iFrm!lblResult.Caption = "Result"
    iFrm.Result.Value = 0
    
End Sub


Private Sub cbo_LostFocus()
    LFColor iFrm, cbo 'ComboBox highlight
End Sub

Private Sub cbo_AfterUpdate()
  iFrm.List0.Requery
  
End Sub

'ListBox Event Subroutines Code
Private Sub lst_GotFocus()
    GFColor iFrm, Lst 'ListBox highlight
End Sub

Private Sub lst_LostFocus()
    LFColor iFrm, Lst 'ListBox highlight
End Sub

All Event Subroutines are written with the Object Name declared in the Global Declaration Area in the Opt_Object_Init2 Class Module. 

Demo Database Download

Streamlining Form Module Code in Standalone Class Module.

Earlier Pages.

  1. Re-using Form Module VBA Coding for New Projects.
  2. Streamlining Form Module Coding Part-Two.
  3. Streamlining Form Module Coding Part-Three.
  4. Streamlining Form Module Coding Part-Four.
  5. Streamlining Form Module Coding Part-Five.
  6. Streamlining Form Module Coding Part-Six.
  7. Streamlining Form Module Coding Part-Seven.
  8. Streamlining Form Module Coding Part-Eight.
  9. Streamlining Form Module Coding Part-Nine.
  10. Streamlining Form Module Coding Part-Ten.


Share:

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