Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

One TextBox and Three Wrapper Class Instances.

 Streamlining Event Subroutines in Standalone Class Module.

The Standalone Class Module Coding Rules overview.

  1. One Wrapper Class Module for several objects of the same Type (E.g.: TextBox) on the Form. Example: ClsTextBox.
  2. 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
  3. 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.

  4. 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
    
  5. The above Subroutine modifies the AfterUpdate Event Property Values with the text  [Event Subroutine] option at run-time.
  6. The Enter Key press, after typing something in the TextBox, fires the AfterUpdate Event. 

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

  8. As we are all aware, typically, we write only one Subroutine per Event and create one Wrapper Class Instance per TextBox. However, what happens if we deviate from this convention and create three instances of the Wrapper Class, with an AfterUpdate Event Subroutine,  for a single TextBox (e.g., Text0) and assign all of them the same TextBox reference?

  • 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:

Text0 Instances

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:

Event Procedure Message

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.

Demo Database Download


  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.
  14. Streamlining Form Module Code Part-14:All Controls
  15. Streamlining Custom Made Form Wizard-15
  16. Streamlining Custom Report Wizard-16
  17. Streamlining Form VBA External File Browser-17
  18. Streamlining Event Procedures of 3D TextWizard-18
  19. Streamlining Event Procedures RGB Color Wizard-19
  20. Streamlining Event Procedures Number To Words-20
  21. Streamlining VBA Code Presentation at Access User Groups.org (Europe)
  22. The Event-firing Mechanism in Microsoft Access
Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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