August 2023 ~ LEARN MS-ACCESS TIPS AND TRICKS

Streamlining Form Module Code - Part Eleven

a.p.r. pillaiAugust 31, 2023 5 comments

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.

- The highest Freight Sales Value of the Employee.
- The Lowest Freight Sales Value.
- 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, remove 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 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 come 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

Streamline11.zip

Streamlining Form Module Code in Standalone Class Module.

Streamlining Form Module Code - Part Ten

a.p.r. pillaiAugust 19, 2023 2 comments

Creating Access Menu using Tabcontrol, ListBox, and Command Buttons.

We will take up the TabControl-based Menu design task after a few minutes.

Organizing the Wrapper Classes for Different Forms.

Let us see how we can organize the Wrapper Classes when there are several Forms and several Wrapper Classes for each Form in a database. They must be organized properly to avoid mix-ups like the wrapper class containing the Event Subroutines of Form A is added in the WrapObject_Init Class of Form B.

Wrapper Class Templates.

Create one set of Wrapper Class Templates for all frequently used controls like TextBoxes, CommandButtons, ComboBoxes, and others. Make Copies of Wrapper Classes and change their names, with some short name prefixes to the Wrapper Class Names like EmpTextBox (Emp for Employees Form) to relate the Wrapper Classes to the Employee's Form. All other controls ComboBoxes, Command Buttons and others for the Employees Form will have the same name Prefix Emp_TextBox format, or EmpTextBox format, Emp_CmdButton, Emp_ComboBox, and so on.

After going through the earlier nine Articles on the Streamlining of Form Module VBA Code topic you are now familiar with this new VBA Coding method and know how to create a new Wrapper Class if it is not available among the existing Templates when a new Control is added on the Form.

Forms With SubForms.

Form with SubForms don't need any separate Classes, need only one Wrapper Class for the Main Form. You can see an example of the Employees Main Form with the Orders Subform in episode nine. The TextBox and ComboBox controls are scanned in the EmpObject_Init Class in a separate For . . . Next Loop with the SubForm Reference and how the SubForm Control references are being mapped and added to the Collection Object. Their Event Subroutines can be written using the main Form Property like EmpTextBox wrapper Class Property name Txt for the TextBox on the Order SubForm like:

'Data Update Reconfirm to Save the Change
Private Sub txt_BeforeUpdate(Cancel As Integer)
Dim msg As String

msg = "Field Name: " & Txt.Name & vbCr & _
        "Original Value '" & UCase(Txt.OldValue) & "'" & _
        vbCr & "Change to: '" & UCase(Txt.Value) & "'"
    
    If MsgBox(msg, vbYesNo + vbQuestion, _
        Txt.Name & "_BeforeUpdate()") = vbNo Then
        Cancel = True
    End If


End Sub

The Txt Object will have the correct reference of the SubForm TextBox and will execute the code for the TextBox in the Orders SubForm.

Let us see an example in the Sub Txt_GotFocus() Event Subroutine for not highlighting the 'OrderDate' TextBox on the Orders SubForm in the Employees Main Form.

Private Sub txt_GotFocus()
    If Txt.Name = "OrderDate" Then
         'No highlight
    Else
          GFColor frm, Txt
    End If
End Sub

We can address a control like any other control on the Main Form. The OrderDate field is not highlighted when it becomes current.

The next sample Code skips highlighting all TextBoxes on the Orders SubForm.

Private Sub txt_GotFocus()
'https://learn.microsoft.com/en-us/office/vba/api/access.subform.parent
   Select Case Txt.Parent.Name         
    	Case "Orders"   'Orders SubForm has the parent Property set
          'Do Nothing
    	Case Else
           GFColor frm, Txt 'Field Highlight
   End Select
End Sub

Some examples of Wrapper Class Names are given below.

Wrapper Classes for the Employees Form.

EmpObject_Init
EmpTextBox
EmpCmdButton
EmpComboBox
EmpOptionGrp

Wrapper Classes for Orders Main Form.

Order_Object_Init
Order_TextBox
Order_CmdButton
Order_ListBox
Order_TabCtl

Reusing Streamlined Coding Procedures in Other Projects.

Now the Question of how to Reuse the Classes in another Database?

Different Projects and different requirements. Most of the Subroutines written for a particular Form, based on its specific requirements, cannot be used without changes in another Project. But there are Event Subroutine Codes mentioned earlier (TextBox highlight, OnDirty, OnBeforeUpdate) or similar tasks you find can be used without change in other Projects.

In either case, the Backbone of this new streamlined Coding Procedure with less Code in the Wrapper Classes can be Exported into other Projects. Working with the Wrapper Classes for Coding independently and not mixing the Coding work with the Form design task will make the way for faster completion of Projects.

You can create a set of Wrapper Class Templates for frequently used Form Controls like:

Access.TextBox
Access.CommandButton
Access.ComboBox and others

with reusable Event Subroutines, like TextBox, ComboBox, ListBox highlighting OnGotFocus, OnLostFocus Event Subroutines, TextBox OnDirty, OnBeforeUpdate Event Subroutines, for safeguarding against inadvertent changes, and similar reusable code you find can be included in the Wrapper Class Templates. Any Standard Module-based Common Functions in use in the Event Subroutines, like the GFColor, and LFColor Functions we used for highlighting the TextBox Controls must accompany the Wrapper Classes.

All Wrapper Classes we create will have a Form Property along with the Control Object Property like TextBox and the SET/GET Property Procedures for both the above objects as their body. The Wrapper Class Templates can be saved in a separate database. The Classes saved in this database can have the prefix .cls like:

clsObject_Init
clsTextBox
clsCmdButton and others.

How to use the Code from the Template Database.

Attach this database to your New Project as a Library database.
Create a new Class Module in the new Database.
Change its name to match the first Wrapper Class in the attached database.
Right-click on the Wrapper Class Module of the attached Database and select View Code from the displayed shortcut Menu. The Code will be displayed in the VBA Window.
Copy the Code and Paste it into the new Class Module and save it.
Repeat this procedure to transfer all the Wrapper Class Templates to the New Database.
Then remove the attached database from the Reference Library.

Alternative Method.

Even better, you can Export the Wrapper Class Templates from the VBA Window into a dedicated Folder in the Disk-Drive as separate Class Files with the file extension .cls, and you can Import them one by one into your new Projects.

Access Menu with TabControl.

The finished Menu View Image is given below:

There are three layers of Menu Options (Tables, Forms, and Reports) that will appear in the same place when selected by clicking on the Command Buttons given on the left side.

The Menu Design Image is given below.

There are three Pages in the Tab Control, with Page Names: Tables, Forms, and Reports.

Each Tab Page has a ListBox Control with the same dimension and is positioned on the same left and top values. The idea is to display all the Menu Pages: Tables, Forms, and Reports one after the other in the same position on the TabControl.

The following Property settings will change the display style of the Tab Control to the style shown in the first Image given above:

Tab Style: None
Back Style: Transparent
Border Style: Transparent

When the Tab Pages are hidden the Command Button Click will change the Pages and the Page-Change Event will Fire. The Click Event of the Command Button will highlight the Border of the Command Button to indicate the current selection. Besides that, the current Menu selection is announced by a female Voice.

Double-clicking on the List Item will display the Table or Form or Report on the screen. This action also will announce the type of object that is currently on display.

The ListBox Menu Source Items Employees, Orders, and Customers are added in the Value List in the format:

1;"Employees";2;"Orders";3;"Customers"

for all three Menus Tables, Forms & Reports.

The TabLst_Object_Init Class

The TabLst_Objectr_Init Class Module Code is given below:

Option Compare Database
Option Explicit

Private iFrm As Access.Form
Private Coll As New Collection

Private tbc  As TabLst_TabCtl
Private wcmd As TabLst_CmdButton
Private lst  As TabLst_ListBox

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Tab Control Class_Init Class
'Author: a.p.r. pillai
'Date  : 16/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
	Call Class_Init
End Property

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

Const EP = "[Event Procedure]"

'Scan for Tab Control, CommandButton & ListBox Controls
'and Enable the required Event Procedures
For Each ctl In iFrm.Controls

    Select Case ctl.ControlType
        Case acTabCtl
            Set tbc = New TabLst_TabCtl
            Set tbc.tb_Frm = iFrm
            Set tbc.tb_tab = ctl
                tbc.tb_tab.OnChange = EP
                
                Coll.Add tbc
            Set tbc = Nothing
    
        Case acListBox
            Set lst = New TabLst_ListBox
            Set lst.lst_Frm = iFrm
            Set lst.m_lst = ctl
            
            lst.m_lst.OnDblClick = EP
           
            Coll.Add lst
            Set lst = Nothing

        Case acCommandButton
            Select Case ctl.Name
                Case "cmdTables", "cmdForms", "cmdReports", "cmdExit"
                    Set wcmd = New TabLst_CmdButton
                    Set wcmd.cmd_Frm = iFrm
                    Set wcmd.c_cmd = ctl
                    
                    wcmd.c_cmd.OnClick = EP
                    Coll.Add wcmd
                    Set wcmd = Nothing
            End Select
            
   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

In the Global Declaration area, the Form and Collection Property declarations are made, followed by the Tab Control, Command Button, and ListBox Wrapper Class Property Declarations.

Next, the Set/Get Property Procedures for the Form. The Class_Init() Subroutine is Called From the Set Property Procedure of the Form object. In the For . . . Next Loop we look for our TabCtl, ListBox, and Command Button Controls.

The TabCtl control Wrapper Class TabLst_TabCtl Instance Properties tb_Frm and tb_Tab are assigned with the iFrm, ctl References. The TabCtl Control's Tab Page Change Event is enabled and then the TabLst_TabCtl Wrapper Class Instance is added to the Collection Object.

All three ListBox Properties are enabled with the DblClick() Event in the TabLst_ListBox Wrapper Class Instances and added to the Collection Object. Similarly, the Command Button Wrapper Class TabLst_CmdButton Instances are enabled with the Click Event and added to the Collection Object.

The TabLst_TabCtl Class

The TabLst_TabCtl Wrapper Class Module Code is given below.

Option Compare Database
Option Explicit

Private tbfrm As Access.Form
Private WithEvents Tb As Access.TabControl

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

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

Public Property Set tb_Frm(ByRef tabfrm As Form)
    Set tbfrm = tabfrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get tb_tab() As Access.TabControl
    Set tb_tab = Tb
End Property

Public Property Set tb_tab(ByRef ptab As Access.TabControl)
    Set Tb = ptab
End Property

'Event Subroutines Code
Private Sub tb_Change()
   Select Case Tb.Value
        Case 0
            MsgBox "Change Event: Page(0)"
        Case 1
            MsgBox "Change Event: Page(1)"
        Case 2
            MsgBox "Change Event: Page(2)"
    End Select
End Sub

When the TabControl TabPage Change Event is fired will be captured here and display the Page Index Number in the MsgBox.

The Command Button Wrapper Class.

 
Option Compare Database
Option Explicit

Private WithEvents cmdfrm As Form
Private WithEvents cmd As CommandButton 'CommandButton object
Dim L As Integer
Dim ForeColor As Long

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Command Button Events
'Author: a.p.r. pillai
'Date  : 16/08/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
End Property

'TextBox Property GET/SET Procedures
Public Property Get c_cmd() As CommandButton
    Set c_cmd = cmd
End Property

Public Property Set c_cmd(ByRef pcmd As CommandButton)
    Set cmd = pcmd
End Property

'Event Subroutines
Private Sub cmd_Click()
Select Case cmd.Name
  Case "cmdExit"
  Announce "Close the Form Now?"
    If MsgBox("Close the Form Now?", vbOKCancel + vbQuestion, "cmd_Click") = vbOK Then
        DoCmd.Close acForm, cmdfrm.Name
        Exit Sub
    End If
    
  Case "cmdTables"
    'Command Button Border Highlight
      cmd.BorderWidth = 2
      cmdfrm.cmdForms.BorderWidth = 0
      cmdfrm.cmdReports.BorderWidth = 0
      
    cmdfrm.TabCtl0.Pages(0).SetFocus
    Announce "Tables Menu Active."
    
  Case "cmdForms"
    'Command Button Border Highlight
    cmd.BorderWidth = 2
    cmdfrm.cmdTables.BorderWidth = 0
    cmdfrm.cmdReports.BorderWidth = 0
    
    cmdfrm.TabCtl0.Pages(1).SetFocus
        Announce "Forms Menu Active."

  Case "cmdReports"
  'Command Button Border Highlight
    cmd.BorderWidth = 2
    cmdfrm.cmdForms.BorderWidth = 0
    cmdfrm.cmdTables.BorderWidth = 0
    
    cmdfrm.TabCtl0.Pages(2).SetFocus
        Announce "Reports Menu Active."
End Select
End Sub

The three Command Buttons to the left side of the TabControl is the replacement of TabControl Page Buttons to hide the identity features of the TabControl. This is the secret of several layers of different Menus appearing one at a time in the same location. More layers of Menu Pages can be added to the TabControl with ListBoxes and Command Button.

The following Properties of the TabControl are set to hide the features of the TabControl:

Style: None
Back Style: Transparent
Border Style: Transparent

The ListBox Wrapper Class.

Option Compare Database
Option Explicit

Private lstfrm As Access.Form
Private WithEvents lst As Access.ListBox

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

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

Public Property Set lst_Frm(ByRef mFrm As Form)
    Set lstfrm = mFrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get m_lst() As ListBox
    Set m_lst = lst
End Property

Public Property Set m_lst(ByRef mLst As ListBox)
    Set lst = mLst
End Property

Private Sub lst_DblClick(Cancel As Integer)
Dim i As Integer
Dim Menu(1 To 3) As String
Dim Obj(1 To 3) As String

i = Nz(lst.Value, 0)

Const Opn = "Opening "

Menu(1) = "Table "
Menu(2) = "Form "
Menu(3) = "Report "

Obj(1) = "Employees"
Obj(2) = "Orders"
Obj(3) = "Customers"

Select Case lst.Name
    Case "LstTables"
        Select Case i
            Case 1, 2, 3
                Announce Opn & Menu(1) & Obj(i) 'Speak
                DoCmd.OpenTable Obj(i), acViewNormal
        End Select
        
    Case "LstForms"
        Select Case i
            Case 1, 2, 3
                Announce Opn & Menu(2) & Obj(i) 'Speak
                DoCmd.OpenForm Obj(i), acViewNormal
        End Select
        
    Case "LstReports"
        Select Case i
            Case 1, 2, 3
                Announce Opn & Menu(3) & Obj(i) 'Speak
                DoCmd.OpenReport Obj(i), acViewReport
        End Select
End Select
End Sub

At the beginning of the Listbox's Double-Click Event Subroutine, a few Array Variables are Initialised with the Menu Names and menu item names to compose the Speech Text to Announce the Menu item selection. This method also reduces the File opening statements from six to two lines each (excluding the array lines) for Tables, Forms, and Reports.

The normal Coding will look like the following:

Select Case lst.Name
    Case "LstTables"
        Select Case i
            Case 1
                Announce "Opening Table Employees" 'Speak
                DoCmd.OpenTable "Employees", acViewNormal
            Case 2
                Announce "Opening Table Orders" 'Speak
                DoCmd.OpenTable "Orders", acViewNormal
            Case 3
                Announce "Opening Table Customers" 'speak
                DoCmd.OpenTable "Customers", acViewNormal
        End Select
        
    Case "LstForms"
        Select Case i
            Case 1
                Announce "Opening Form Employees" 'Speak
                DoCmd.OpenForm "Employees", acViewNormal
            Case 2
                Announce "Opening Form Orders" 'Speak
                DoCmd.OpenForm "Orders", acViewNormal
            Case 3
                Announce "Opening Form Customers" 'speak
                DoCmd.OpenForm "Customers", acViewNormal
        End Select
        
    Case "LstReports"
        Select Case i
            Case 1
                Announce "Opening Report Employees" 'Speak
                DoCmd.OpenReport "Employees", acViewReport
            Case 2
                Announce "Opening Report Orders" 'Speak
                DoCmd.OpenReport "Orders", acViewReport
            Case 3
                Announce "Opening Report Customers" 'speak
                DoCmd.OpenReport "Customers", acViewReport

        End Select
End Select

Microsoft Speech-Service VBA Code is given below. The Subroutine Code is in the Standard Module.

Sub Announce(ByVal Txt As String, Optional Gender As String = "Female")
'https://learn.microsoft.com/en-us/azure/ai-services/speech-service/
    Dim obj As Object
    Set obj = CreateObject("SAPI.SpVoice")
    Set obj.Voice = obj.GetVoices("Gender = " & Gender).Item(0)
    obj.Speak Txt
End Sub

The TabLst_ListBox Wrapper Class DblClick() Event Subroutines Runs the Menu Options Tables, Forms, and Reports opening activities.

Streamline10.zip

Streamlining Form Module Code in Standalone Class Module.

Streamlining Form Module Code - Part Nine

a.p.r. pillaiAugust 07, 2023 2 comments

Introduction.

After going through the last eight episodes of the Title Topic I assume that you are familiar with this new coding procedure and realized its advantages. By implementing this new stand-alone Class Module-based coding approach, we can achieve more functionality with significantly less VBA code.

Episode number Seven demonstrated how we could highlight the entire Array of TextBoxes on the Form, by running the OnGotFocus() Subroutine and resetting the highlight to the earlier state in the LostFocus() Event Subroutines, with only the following six lines of VBA Code in the WrapTextBox Class. The GFColor() and LFColor() public functions in the Standard Module are called from the GotFocus() and LostFocus() TextBox Event Subroutines in the WrapTextBox Class respectively.

Private Sub txt_GotFocus()
    GFColor frm, Txt 'Field Highlight
End Sub

Private Sub txt_LostFocus()
    LFColor frm, Txt 'Field Highlight
End Sub

If you add more TextBoxes on the Form or delete existing ones and not necessary to make any changes to the VBA Code.

Similarly, the following small code snippet monitors all the TextBox controls on the Employees Form to monitor any attempt to modify the value in any one of the TextBoxes on the Form. This applies to all TextBoxes on the main form Employees and the SubForm Orders as well.

The OnDirty Event Subroutine.

Private Sub txt_Dirty(Cancel As Integer)
    If MsgBox("Editing the " & UCase(Txt.Name) _
    & ": Value? " & Txt.Value, vbYesNo + vbQuestion, _
    Txt.Name & " DIRTY()") = vbNo Then
    
        Cancel = True
    End If
End Sub

The code snippet above effectively monitors all TextBoxes within the Main Form "Employees" and the Sub-Form "Orders" to prevent inadvertent changes. When the user attempts to edit a field, a warning message is displayed, and the user must confirm his/her intention to change the field value before they can proceed with editing. If the user realizes that it was a mistake then they have the option to cancel the Event and revert the field to its original value. This implementation helps to prevent accidental modifications and ensures data integrity.

The TextBoxes on the Orders SubForm are also under the surveillance of this Code.

The BeforeUpdate Event Subroutine.

When you modify data and press the Enter key, the BeforeUpdate event procedure, responsible for safeguarding the entire array of TextBoxes on the forms, will be triggered. All these events are then captured within the same Sub txt_BeforeUpdate() event subroutine, as shown in the code provided below.

'Data Update Reconfirm to Save the Change
Private Sub txt_BeforeUpdate(Cancel As Integer)

    If MsgBox("Field Name: " & Txt.Name & vbCr & _
        "Original Value '" & UCase(Txt.OldValue) & "'" & _
        vbCr & "Change to: '" _
        & UCase(Txt.Value) & "'", vbYesNo + vbQuestion, _
        UCase(Txt.Name) & " BeforeUpdate()?") = vbNo Then
    
        Cancel = True
    End If


End Sub

Again, the User has to reconfirm to save the data in the Field.

Reusability of Streamlined VBA Code Writing.

The key advantage of this Streamlined VBA Code writing is its reusability. Instead of writing the event handling code for each TextBox individually in the Form Module, you can write it just once in a TextBox Wrapper Class Module. This Class Module acts as a template for handling BeforeUpdate() events for all TextBox controls.

Here's how it works:

Write the BeforeUpdate event handling code in the TextBox Wrapper Class Module.
Instantiate the Wrapper Class Module for each TextBox in the Main Form and Subform.
Assign the references of the Form and TextBox controls to their corresponding properties in the Wrapper Class Module.
Enable the required event procedures (e.g., BeforeUpdate) in the Wrapper Class EmpObject_Init Module.
Store the instantiated Wrapper Class EmpTextBox in memory through the Collection Object.

By following this approach, you can efficiently manage and handle events for multiple TextBoxes on different forms without duplicating code. It promotes code organization, reduces redundancy, and makes it easier to maintain and update the event-handling logic in the future.

So far we have not tried any example with the Main Form with a SubForm setting, how to reference the Controls on the Subform, enable their Events, and how to streamline the Code for both Forms.

In this episode, we will use the Employees Table as the record source for the Employees' Main Form. The sub-Form Orders has the Orders Table as a Record Source and is designed as a Tabular Form.

We will learn how to address the Subform Controls (TextBox and ComboBox) in the EmpObject_Init Wrapper Class. Enable their required Events and save the Wrapper Class instances into the Collection Object, as we did earlier.

The Control Wrapper Class (EmpTextBox) instances, enabled with their required Events stay in memory as the Collection Object Items and capture the Events fired from the TextBoxes on the Form and SubForm and execute the Event Subroutines.

In the last episode, we created a new Wrapper Class EmpCmdButton for the Command Button Class Object and added it as the Property of the EmpObject_Init Class.

This time we need a new Wrapper Class EmpCombo for the ComboBox Control to track the Events fired from the ComboBoxes from the Main Form and Subform.

On the Employee Form's Footer Area, there is a TextBox Control to Search and Find the Employee Record by using EmployeeID as the search Key.

After the search operation, it will flash a Label Control (Label Animation) with a message a few times to announce whether the Search was successful or not and then disappear.

The big question here is, where we will write the Code for the Data Search and Find operations and run the Label Animations.

If you recollect the last episode (Part Eight) we used a Form-Close Countdown display on the Form running in the WrapCmdButton Class, within the Sub cmd_Click() Event Subroutine. The Countdown display was in a Label control on the Form. The digital clock was running in a Label Control on the Form, but the Form TimerInterval settings Code was running from the WrapObject_Init Wrapper Class.

Similarly, there is another common task to run from the Form footer area. There is an EmployeeID Search and Find unbound TextBox at the Footer of the Form. The EmployeeID value will be entered into the FindIDTextBox control, to find the record on the Employees Form. The question is in which Wrapper Class we will write the VBA Code for the search and find operations?

Besides that, there is a Label Control below the FindID TextBox, to display the result of the search operation.

The EmployeeID will be entered into the FindID unbound TextBox and will press the Enter Key. This action should start searching for the EmployeeID in the Employees Form. The Code can run in the TextBox AfterUpdate() or LostFocus() Event Subroutine. However, the search for the record should be done on the RecordsetClone of the Form Object. Another option is to add a Command Button next to the Unbound TextBox to Click to start the search for the Record. In that case, we can write the Code in the Command Button Click Event Subroutine in Wrapper Class EmpCmdButton. We have a Label Control on the Footer of the Form running an animated display (On/Off) with the status of search success or failure.

Events that involve the Form Object.

Every Wrapper Class that we create, like EmpTextBox, EmpCmdButton, EmpCombo, and other upcoming ones will have a Form Class as Property, with or without the WithEvents declaration.

If it is with the WithEvents declaration (Private WithEvents frm as Form) then we can directly capture the Form Events in that Wrapper Class. Otherwise, it can be used for accessing any control on the Form to read/write values or to access the Control properties, when the need arises.

Whether we are using the Form Property for any purpose or not we are assigning the physical Form's reference to all the Wrapper Class Instance created in the intermediate WrapObject_Init or EmpObject_Init Class's Class_Init() Subroutine.

So, here we prefer to write the FindID AfterUpdate() Event Subroutine Code in the EmpTextBox Wrapper Class Module.

First, let us see how we can incorporate the FindID unbound TextBox's AfterUpdate() Event enabling Code in the EmpObject_Init Wrapper Class within the existing Code. The full VBA Code is given below. The Combobox wrapper Class Property with the name CBO and its Event enabling Code is also added in the Class_Init Subroutine. All new Code lines are highlighted with Red Color.

The WrapObject_Init Class was copied from the earlier Project, renamed as EmpObject_Init, and added with new Code lines marked with red. Similarly other Classes. This way you can create Wrapper Classes quickly and reuse existing Code for other Forms and if necessary with modifications.

Option Compare Database
Option Explicit

Private WithEvents iFrm As Access.Form
Private WithEvents oFrm As Form

Private iTxt As EmpTextBox
Private wcbo As EmpCombo
Private wcmd As EmpCmdButton
Private Coll As New Collection

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Main Form SubForm with Data
'Author: a.p.r. pillai
'Date  : 06/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
Call Class_Init
End Property

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

Const EP = "[Event Procedure]"
iFrm.OnTimer = EP  'Enable TimerInverval for Label Annimation

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

    Select Case TypeName(ctl)
            Case "TextBox"
              Select Case ctl.Name
                Case "FindID" 'Employee ID Search TextBox
                    Set iTxt = New EmpTextBox 'Create Instance
                    Set iTxt.tx_Frm = iFrm 'Assign Form Object
                    Set iTxt.t_Txt = ctl   'TextBox
                    
                        iTxt.t_Txt.OnGotFocus = EP
                        iTxt.t_Txt.OnLostFocus = EP
                        iTxt.t_Txt.AfterUpdate = EP 'For EmployeeID Search
                        
                    Coll.Add iTxt 'Save EmpTextBox Class
                    Set iTxt = Nothing 'Erase temp Instance
                  GoTo CmdButton
              End Select

                    Set iTxt = New EmpTextBox 'Create Instance
                    Set iTxt.tx_Frm = iFrm
                    Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form
 
                         iTxt.t_Txt.OnGotFocus = EP
                         iTxt.t_Txt.OnLostFocus = EP
                         iTxt.t_Txt.OnDirty = EP      'To warn against Data Change
                         iTxt.t_Txt.BeforeUpdate = EP 'Reconfirm Data Change to update
                        
                    Coll.Add iTxt 'Save EmpTextBox Class in Collection Object
                    Set iTxt = Nothing 'Erase temp Instance
CmdButton:
        Case "CommandButton"
            Select Case ctl.Name
                Case "cmdClose"
                    Set wcmd = New EmpCmdButton
                    Set wcmd.cmd_Frm = iFrm
                    Set wcmd.c_cmd = ctl
                    
                    wcmd.c_cmd.OnClick = EP
                    
                    Coll.Add wcmd
                    Set wcmd = Nothing
            End Select
        
        Case "ComboBox"
                    Set wcbo = New EmpCombo
                    Set wcbo.cbo_Frm = iFrm
                    Set wcbo.c_cbo = ctl
                    
                   wcbo.c_cbo.OnGotFocus = EP
                   wcbo.c_cbo.OnLostFocus = EP
                    
                    Coll.Add wcbo
                    Set wcbo = Nothing
    End Select
Next

'Order Sub-Form
  Set oFrm = iFrm.Orders.Form

For Each ctl In oFrm.Controls 'Scan for SubForm Control and enable Events
Select Case TypeName(ctl)
    Case "TextBox"

            Set iTxt = New EmpTextBox 'Create Instance
            Set iTxt.tx_Frm = oFrm
            Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form

               iTxt.t_Txt.OnGotFocus = EP 'To highlght TextBox
               iTxt.t_Txt.OnLostFocus = EP 'Reset Highlight
               
               iTxt.t_Txt.OnDirty = EP
               iTxt.t_Txt.BeforeUpdate = EP
               
                Coll.Add iTxt 'Save EmpTextBox Class
            Set iTxt = Nothing 'Erase temp Instance

        Case "ComboBox"
             Set wcbo = New EmpCombo
             Set wcbo.cbo_Frm = oFrm
             Set wcbo.c_cbo = ctl

                 wcbo.c_cbo.OnGotFocus = EP 'To highlght ComboBox
                 wcbo.c_cbo.OnLostFocus = EP 'Reset Highlight
                     
                   Coll.Add wcbo
                Set wcbo = 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
    Set oFrm = Nothing
End Sub

The EmpTextBox Wrapper Class EmployeeID Search VBA Code is given below:

Option Compare Database
Option Explicit

Private WithEvents frm As Form
Private subFrm As Form

Private WithEvents Txt As TextBox 'TextBox object
Dim L As Integer
Dim ForeColor As Long
'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Main Form SubForm with Data
'Author: a.p.r. pillai
'Date  : 06/08/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
'Form's Property GET/SET Procedures
Public Property Get tx_Frm() As Form
    Set tx_Frm = frm
End Property

Public Property Set tx_Frm(ByRef pfrm As Form)
    Set frm = pfrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get t_Txt() As TextBox
    Set t_Txt = Txt
End Property

Public Property Set t_Txt(ByRef tTxt As TextBox)
    Set Txt = tTxt
End Property

'Evbent Subroutines
'===================

Private Sub txt_GotFocus()
    GFColor frm, Txt 'Field Highlight
    
    If Txt.Name = "FindID" Then
        Txt.Value = Null
    End If
End Sub

Private Sub txt_LostFocus()
    LFColor frm, Txt 'Field Highlight
End Sub

Private Sub txt_Dirty(Cancel As Integer)
    If MsgBox("Editing the " & UCase(Txt.Name) _
    & ": Value? " & Txt.Value, vbYesNo + vbQuestion, _
    Txt.Name & " DIRTY()") = vbNo Then
    
        Cancel = True
    End If
End Sub

'Data Update Reconfirm to Save the Change
Private Sub txt_BeforeUpdate(Cancel As Integer)
Dim msg As String

msg = "Field Name: " & Txt.Name & vbCr & _
        "Original Value '" & UCase(Txt.OldValue) & "'" & _
        vbCr & "Change to: '" & UCase(Txt.Value) & "'"
    
    If MsgBox(msg, vbYesNo + vbQuestion, _
        Txt.Name & "_BeforeUpdate()") = vbNo Then
        Cancel = True
    End If

End Sub

Private Sub txt_AfterUpdate()
Select Case Txt.Name
    Case "FindID"
        Dim rst As Recordset
        Dim ToFind As Integer
        Dim msg As String
        Dim max As Integer
        
        'max = DCount("*", "Employees")
        ToFind = Nz(frm!FindID, 0)
        If ToFind < 1 Then
            msg = "Employee ID: < 1 Invalid!"
            MsgBox msg, vbOK + vbCritical, Txt.Name & "_AfterUpdate()"
        Else
            Set rst = frm.RecordsetClone
            rst.FindFirst "EmployeeID=" & ToFind
            If Not rst.NoMatch Then
                frm.Bookmark = rst.Bookmark
        
                With frm.Result
                    .Caption = "*** Successful ***"
                    ForeColor = 16711680
                    .ForeColor = ForeColor
                End With
            Else
                With frm.Result
                    .Caption = "**Sorry, Not found!"
                    ForeColor = 255
                End With
            
            End If
            L = 0
            frm.TimerInterval = 250 'Enable Timer
        End If
    End Select
End Sub

'Label Animation Code.
Private Sub frm_Timer()
L = L + 1
Select Case L
    Case 1, 3, 5, 7, 9, 11, 13, 15, 17
        frm.Result.Visible = True
    Case 2, 4, 6, 8, 10, 12, 14, 16, 18
        frm.Result.Visible = False
    Case 19
       frm.Result.ForeColor = ForeColor
       frm.Result.Visible = False
       frm.TimerInterval = 0
End Select
End Sub

The EmpCombo is a new Wrapper Class of ComboBox and the GotFocus(), LostFocus() Event Subroutine VBA Code is given below:

Option Compare Database
Option Explicit

Private cbofrm As Access.Form
Private WithEvents cbo As Access.ComboBox 'ComboBox object

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

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

Public Property Set cbo_Frm(ByRef cfrm As Form)
    Set cbofrm = cfrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get c_cbo() As ComboBox
    Set c_cbo = cbo
End Property

Public Property Set c_cbo(ByRef pcbo As ComboBox)
    Set cbo = pcbo
End Property

'Event Subroutines Code
Private Sub cbo_GotFocus()
    GFColor cbofrm, cbo 'ComboBox highlight
End Sub

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

Your Assessment of the Streamlined Coding Procedure in Standalone Class Module.

If you are an experienced Access VBA Programmer, then you may be able to make an assessment of the following Points:

The amount of work and time required to write the Code for the OnDirty() Event Subroutines Code for each TextBox and ComboBox Controls on the Employees & Orders Subform in their Form Modules through the existing manual VBA Code writing procedure.
Similarly, the amount of Work and Time required for the AfterUpdate() Event Subroutines on Both Forms.
Normally we don't write these codes in the Form Module for all the TextBoxes, only for essential cases. How do you feel about this new method and the ease of its implementation time saving compared to existing Coding practices? Not necessary to quantify, the emphasis is on the reusability of part of the written Code in the Standalone Class Module and saving of development time for future Projects.
Even though the new concept, of Streamlining the Form Module Coding in Standalone Class Modules, it is difficult for beginners to make an assessment, what do you think about it as an Expert VBA Programmer?
All the Episodes are written in the style of Tutoring the Title-related examples so that non-experts can also attempt to try and learn. How easy/difficult to understand the concept.

Your feedback may be left in the Comment Section. If you have a Gmail ID you can log in and Comment. Thanks.

The forthcoming Demonstrations on the Title Topic: Implementation of other Access Class Objects like Tab-Control, ListBox, Option Group, and the Report Module Code streamlining examples will follow.

The Demo Database is attached for Download.

Streamline9.zip