One TextBox and Three Wrapper Class Instances. ~ LEARN MS-ACCESS TIPS AND TRICKS

Streamlining Event Subroutines in Standalone Class Module.

The Standalone Class Module Coding Rules overview.

One Wrapper Class Module for several objects of the same Type (E.g.: TextBox) on the Form. Example: ClsTextBox.

One Event Subroutine per Event for several Objects of the same type on the Form.

Example:

Private Sub txt_AfterUpdate()
    Select Case TypeName(Ctl)
       Case "TextBox"
       
          Select Case Ctl.Name 
              Case "Text0"
                  'Code
                  
              Case "Text2","Text4","Text6"
                  'Code
                  
              Case "Text8"
                  'Code
          End Select
          
   End Select
End Sub

Wrapper Class instances are established, with an individual Instance designated for each TextBox on the form. Subsequently, each Instance is assigned its respective TextBox Reference.

Let us look at the Class_Init() Subroutine VBA Code and see how this process is initiated in the Intermediary (or Interface) Class Module - ClsObj_Init.

 Private Sub Class_Init()
 Dim Ctl As Control
 
 For Each Ctl In Frm.Controls
   Select Case TypeName(Ctl)
     Case "TextBox"
       Set txt = New ClsTextBox
       Set txt.m_frm = Frm
       Set txt.m_txt = Ctl
       
           txt.m_txt.AfterUpdate = "[Event Procedure]"
           
           Coll.Add txt
       Set txt = Nothing
   End Select
Next

The above Subroutine modifies the AfterUpdate Event Property Values with the text [Event Procedure] option at run-time.
The Enter Key press, after typing something in the TextBox, fires the AfterUpdate Event.
If the AfterUpdate Property is assigned with a Macro or Public Function Name then the Event will call the Macro or Function directly. The Form doesn't need a Class Module to run these two options.
As we are all aware, typically, we write one Subroutine per Event and create one Wrapper Class Instance per TextBox. However, what happens if we deviate from this convention and create three Wrapper Class Instances, with AfterUpdate Event Subroutine, for a single TextBox (e.g., Text0) and assign all three Instances with the same Text0 Reference (the Memory Address of Text0 TextBox)?

How does the AfterUpdate Event fire from all three Instances? All of them together or one after the other?
What will happen if three different Macro Names or Function Names are assigned to the AfterUpdate Property, for all three Wrapper Class Instances, like the example Code segment shown below:
```
	For j = 1 To 3 'To create three Instances
             Set ctxt = New ClsTextBox
             Set ctxt.txt = ctl 'Text0 Reference
                 ctxt.param = j
                    
                'Macro1, Macro2, Macro3
    		 ctxt.txt.AfterUpdate = "Macro" & CStr(j) 

                 coll.Add ctxt
             Set ctxt = Nothing
        Next
```
To conduct this experiment and explore the outcomes, we require a TextBox on a Form named Text0, along with the TextBox Wrapper Class Module and the Intermediary (or Interface) Class Module to set up the necessary elements.

The Image of the Form is given below:

Wrapper Class Module: ClsTextBox VBA Code.

Option Compare Database
Option Explicit

Public WithEvents txt As Access.TextBox
Public param As Integer

Private Sub txt_AfterUpdate() 
Dim x As Variant
Dim msg As String

msg = "INSTANCE OF " & UCase(txt.Name)
Select Case param
    Case 1
        'DoCmd.RunMacro "Macro1"
        'x = DisplayText1()

        MsgBox "1st " & msg
    Case 2
        'DoCmd.RunMacro "Macro2"
        'x = DisplayText2()

        MsgBox "2nd " & msg
    Case 3
        'DoCmd.RunMacro "Macro3"
        'x = DisplayText3()

        MsgBox "3rd " & msg
End Select
End Sub

Review of Wrapper Class Code.

The TextBox Object Txt is declared with Public Scope and qualified with the Keyword WithEvents. There is another Property param also declared with Public Scope.

Following this, the Sub txt_AfterUpdate() Event procedure will execute three times consecutively. This occurs for the same event procedure but originates from three distinct Wrapper Class instances. The execution order aligns with the sequential creation of the instances, starting with the first instance, followed by the second, and concluding with the third.

To discern the order of Wrapper Class instance creation and execution sequence, a sequence number is passed as a parameter to the param variable. When the AfterUpdate event subroutine is executed, the number within the param variable is displayed in a message text. This approach allows us to identify from which Wrapper Class instance the message is displayed and in what order.

The Macro and Function Name demo running VBA Code lines are temporarily disabled for test-running the [Event Procedure] alone.

The Intermediary or Interface Class Module ClsObj_Init VBA Code.

Option Compare Database
Option Explicit

Private ctxt As ClsTextBox
Private frm As Form

Private WithEvents cmd As CommandButton
Dim coll As New Collection

Public Property Get m_Frm() As Form
    Set m_Frm = frm
End Property

Public Property Set m_Frm(ByVal vFrm As Form)
    Set frm = vFrm
    
    Call Class_Init
End Property

Private Sub Class_Init()
Dim ctl As Control
Dim j As Integer
Const EP = "[Event Procedure]"
    
For Each ctl In frm.Controls
    Select Case TypeName(ctl)
        Case "TextBox"
            Select Case ctl.Name
                Case "Text0" 'The Text0 Object Reference.
                  For j = 1 To 3
                    Set ctxt = New ClsTextBox
                     Set ctxt.txt = ctl
                        ctxt.param = j
                        
    'ctxt.txt.AfterUpdate = "Macro" & CStr(j) 'Macro1, 2, 3
    'ctxt.txt.AfterUpdate = "=DisplayText" & CStr(j) & "()"

    ctxt.txt.AfterUpdate = "[Event Procedure]"
                        coll.Add ctxt
                    Set ctxt = Nothing
                  Next
            End Select
        Case "CommandButton"
          Select Case ctl.Name
            Case "CmdClose"
              Set cmd = frm.cmdClose
                cmd.OnClick = "[Event Procedure]"
          End Select
    End Select
 Next
End Sub

Private Sub cmd_Click()
DoCmd.Close acForm, frm.Name
End Sub

As usual, the first two lines of Code in the global declaration area, the TextBox Wrapper Class Object ctxt, and the Form object frm are declared.

A Command Button on the form requires a Click Event to close the form. To capture the event in the Interface Class Module when fired on the form, a Command Button object is declared in the global area, qualified with the keyword WithEvents. It's important to note that since there is only one Command Button on the form, handling the Click Event in the Interface Class Module can be done directly without the need to create a Wrapper Class.

A Collection object is declared to store all TextBox object instances in memory, enabling the capture of events fired on the form and the execution of the corresponding event subroutines.

When the Form is open the Form Object is passed to the Form object's Set Property Procedure. The Object reference is assigned to the frm Property.

Next, the Class_Init() Subroutine is called.

In the For...Next loop, the Code scans the Form for the Text0 TextBox. Once found, the inner For...Next loop is configured to run for three cycles, creating three instances of the Wrapper Class Object ctxt. All three instances are assigned with the same Text0 Control's reference. It's worth noting that this can be achieved without using the For...Next loop by duplicating the code three times. However, for consistency, we adhere to the coding style employed thus far.

When the Wrapper Class instances are created, the sequence number is passed to the Property ctxt.param. This number is then displayed in the MsgBox within the AfterUpdate Subroutine. This approach proves beneficial for identifying the execution order of the Event Subroutines, mirroring the sequence in which the instances are created.

If you manually create an AfterUpdate Event Subroutine in the Form Module in addition to the three instances, that Form Module Event Subroutine will execute first, followed by the Subroutines in the Wrapper Class instances. This sequence ensures that any manually added code in the Form Module takes precedence over the dynamically created instances.

As you have seen in the AfterUpdate Event Subroutine we can check the sequence number in the param Property (see the Code segment given below) and based on its sequential order it is possible to call three different Sets of Programs when a single AfterUpdate or similar Event fires.

msg = "INSTANCE OF " & UCase(txt.Name)
Select Case param
    Case 1
        'DoCmd.RunMacro "Macro1"
        'x = DisplayText1()

        MsgBox "1st " & msg
    Case 2
        'DoCmd.RunMacro "Macro2"
        'x = DisplayText2()

        MsgBox "2nd " & msg
    Case 3
        'DoCmd.RunMacro "Macro3"
        'x = DisplayText3()

        MsgBox "3rd " & msg
End Select

The test run of the Event Subroutine Image (message from the first Instance) is given below:

The provided screenshot captures the moment when the message is displayed from the AfterUpdate() Event Subroutine, indicating that the message originates from the first instance of the ClsTextBox Wrapper Class. Subsequently, two similar messages follow, each indicating the 2nd and 3rd instances, respectively.

Experiments with Macro Names: Macro1, Macro2, and Macro3

In the upcoming experiment, we will replace the text [Event Procedure] with the Macro names Macro1, Macro2, and Macro3 to trigger the AfterUpdate Event. It's important to note that no alterations will be made to the TextBox Wrapper Class VBA Code. Below is the modified code for the Class_Init() Subroutine in the Interface Module for your reference.

Private Sub Class_Init()
Dim ctl As Control
Dim j As Integer
Const EP = "[Event Procedure]"
    
For Each ctl In frm.Controls
    Select Case TypeName(ctl)
        Case "TextBox"
            Select Case ctl.Name
                Case "Text0"
                  For j = 1 To 3
                    Set ctxt = New ClsTextBox
                     Set ctxt.txt = ctl
                        ctxt.param = j
                        
        'Macro1, Macro2, Macro3              
    	ctxt.txt.AfterUpdate = "Macro" & CStr(j)
        
                       coll.Add ctxt
                    Set ctxt = Nothing
                  Next
            End Select
        Case "CommandButton"
          Select Case ctl.Name
            Case "CmdClose"
              Set cmd = frm.cmdClose
                cmd.OnClick = "[Event Procedure]"
          End Select
    End Select
 Next
End Sub

All three Macros have a Message Box Function that displays a message with the Macro Version Number 1, 2, and 3 as shown below:

In the Initializing Class_Init() Subroutine, the AfterUpdate Property is assigned with the Macro Names: Macro1, Macro2, and Macro3 as we normally do with the [Event Procedure] Option.

Following the modifications in the VBA code, input some text into the TextBox and press the Enter key to trigger the AfterUpdate event. Subsequently, you will be greeted with a message from Macro3 exclusively. As previously emphasized, it's crucial to note that the Form Class Module or the Wrapper Class does not play a role in the invocation of the macro or function from the AfterUpdate or any other event property.

Running Public Functions: =DisplayText1(), =DisplayText2(), =DisplayText3().

It's worth noting that there are three functions in the standard module bearing the names mentioned above. Each of these functions utilizes the MsgBox function to display text, identifying the sequence number as depicted in their respective names.

If you substitute the AfterUpdate event-enabling line in the Class_Init() subroutine with the provided line of code and subsequently open the form, triggering the AfterUpdate event will reveal that the displayed message emanates solely from the function DisplayText3().

ctxt.txt.AfterUpdate = "=DisplayText" & CStr(j) & "()"

In contrast to the [Event Procedure] option, which invokes the RaiseEvent action, the macro and function name coding pertains to the direct modification of the AfterUpdate Event Property of the TextBox on the Property Sheet. This manual alteration involves replacing the existing name with three different names successively, with the final one prevailing.

How to Run Macros or Functions.

Indeed, the experimental approach undertaken served as an extraordinary demonstration, underscoring the crucial point that macro or function names coded in the Event Property are executed directly by the system. The involvement of the Wrapper Class Instance is entirely bypassed in this process. Whether the names are manually written in the Event Property or scripted through the Class_Init() Subroutine, the modifications are consistently made directly to the TextBox Property.

To alleviate potential confusion, a recommended approach is to consistently opt for the [Event Procedure] option and then call the macro or function, if needed, from within the TextBox Wrapper Class Instance-based subroutine. An illustrative example is provided below:

Private Sub txt_AfterUpdate() 
Dim x As Variant

Select Case txt.Name
    Case "Text0"
        'DoCmd.RunMacro "Macro1" ' Run Macro
        'x = DisplayText1()      ' Run Function

        MsgBox "TextBox" & txt.Name
End Select

By adopting this method, clarity is enhanced, and the direct execution of macros or functions from the Event Property remains seamless within the designated TextBox Wrapper Class Instance.