LEARN MS-ACCESS TIPS AND TRICKS

WithEvents and All Form Control Types

a.p.r. pillaiAugust 16, 2019WithEvents 9 comments

'WithEvents' and All Form Controls.

So far, we have primarily worked with a single type of control — TextBoxes on a form — enabling specific events to be raised and captured in a Class Module using various methods. We have performed several demo runs to capture events, such as AfterUpdate and LostFocus raised from multiple TextBoxes, and handled them in the Class Module.

For each TextBox on the form, a separate Class Object instance is created, enabled with the required event, and stored either in an Object Array, a Collection Object, or as items in a Dictionary Object.

For example, suppose the form has five TextBoxes, but requires only three event-handling subroutines to perform tasks such as validating the entered data. In that case, only three instances of the ClsText Class Module are created—one for each of the three active TextBoxes—while the remaining two are ignored.

When an enabled event fires, the corresponding Class Object instance captures the event and executes the associated event procedure code.

In our previous session, we extended this concept by adding Command Button controls and their own Class Module alongside the TextBoxes for our demo runs.

New Demo Form: frmControls_All

In this demo Run, we will include most of the frequently used controls on the Form. The image of the demo Form is given below:

Right-click to open Large Image in New Tab/Window.

In addition to TextBoxes and Command Buttons, forms can also include other controls such as Tab Controls, Combo Boxes, List Boxes, and Option Groups.

To handle events from these controls, we need to create a separate Class Module for each new control type on the demo form. We already have dedicated Class Modules set up for TextBoxes and Command Buttons.

The TextBox and CommandButton Class Modules.

The Text Box and Command Button Class Module code, along with their explanations, were presented in an earlier article. To keep this post concise, those details are not repeated here.

You can revisit those sections by using the bookmark links provided below:

Class Module: ClsText
Class Module: ClsCmdButton
Download: You may download the demo database from that post, run the sample form, and experiment with the controls to explore the implemented methods and VBA code.

What we are doing here is simply an extension of that work, now incorporating more control types on the form.

Tab-Control Class Module: ClsTabCtrl

The Tab-Control Class Module: ClsTabCtrl VBA Code is given below:

Option Compare Database
Option Explicit

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

Public Property Get p_frm() As Access.Form
  Set p_frm = frm
End Property

Public Property Set p_frm(ByRef frmValue As Access.Form)
  Set frm = frmValue
End Property

Public Property Get p_Tb() As Access.TabControl
   Set p_Tb = Tb
End Property

Public Property Set p_Tb(ByRef tbValue As Access.TabControl)
  Set Tb = tbValue
End Property

Public Sub Tb_Change()
Dim msg As String, title As String

Select Case Tb
    Case 0
        frm.Controls("Computer").Value = Environ("ComputerName")
        title = Tb.Pages(0).Name
        msg = "Tab Page Index = 0"
    Case 1
        frm.Controls("UserName").Value = Environ("UserName")
        title = Tb.Pages(1).Name
        msg = "Tab Page Index = 1"
End Select

MsgBox msg, , title

End Sub

The ClsTabCtrl Class Module declares two properties: a Form object (frm) and a Tab Control object (Tb). The Tb property is declared using the WithEvents keyword, enabling it to capture events occurring on the Tab Control.

The Set property procedure p_frm assigns the active form object to the frm property, which is declared with Private scope. The corresponding Get procedure allows access to the form reference, enabling you to address other controls on the form.

For example:

frm.p_frm.Controls("UserName").Value = Environ("UserName")

Similarly, the Set property procedure assigns the Tab Control object to the Tb property, and the Get property procedure returns this Tab Control reference to the calling program, as explained above.

Finally, the Tab Page Change event is captured in the Tb_Change() subroutine, which executes the desired code when the user selects a different tab page. Note that clicking a tab page does not trigger a Click event—instead, it triggers the Change event. This is why we handle it using the Tb_Change() procedure.

Two string variables, msg and title, are initialized with text that will be displayed in a message box. When a Tab Page is clicked, the Tab Page Change event is triggered and captured in the Tb_Change() subroutine. When the event is captured in the Class Module and the Event Procedure is executed successfully, it displays a Message.

The tab control contains two pages, with page index numbers 0 and 1, and each page has a TextBox control.

When the Tab Page Change event fires, the code checks the selected tab page index.

If the first page (index 0) is selected, the TextBox on that page (named Computer) is updated with the computer’s name, using the frm object reference to access the control.

If the second page (index 1) is selected, the TextBox on that page is updated with the current Windows user name (Environ("UserName")).

Note: The exact procedure you write inside the Tb_Change() subroutine will depend on your project’s specific requirements for handling this event.

The Combo Box Class Module: ClsCombo

The Class Module Code for the Combo Box control is given below:

Option Compare Database
Option Explicit

Private WithEvents cbx As Access.ComboBox

Public Property Get p_cbx() As Access.ComboBox
 Set p_cbx = cbx
End Property

Public Property Set p_cbx(ByRef cbNewValue As Access.ComboBox)
 Set cbx = cbNewValue
End Property

Private Sub cbx_Click()
Dim vVal As Variant
Dim cboName As String

vVal = cbx.Value
cboName = cbx.Name

Select Case cbx
    Case "Combo10"
        'Code Goes here
    Case "Combo12"
        'Code Goes here
End Select

MsgBox "Clicked: " & vVal, , cboName
End Sub

The Combo Box Class Module declares a single property, cbx, with Private scope and the WithEvents keyword to capture any events fired from the Combo Box.

The Public Property Set procedure p_cbx() receives the Combo Box control reference from the Form and assigns it to the cbx property.

The Property Get procedure returns the Combo Box control reference to any calling procedure outside this class module.

The cbx_Click() subroutine captures the Click event of the Combo Box on the Form. Within this procedure, a Select Case structure determines which Combo Box triggered the event and then executes the appropriate block of code based on the control’s name.

If you want to capture other events—such as OnGotFocus, OnLostFocus, AfterUpdate, OnChange, and so on—you can create corresponding subroutines in the same class module (for example, cbx_GotFocus()) and write the required VBA code within them.

However, to make these events fire when they occur on the form, you must also enable them in the Derived Class Module.

Currently, the Click event subroutine simply displays a message showing the item value selected from the Combo Box.

The List Box Class Module: ClsListBox

The List Box Class Module Code structure is similar to the ComboBox Code and programmed to capture only the Click Event.

Option Compare Database
Option Explicit

Private WithEvents LstBox As Access.ListBox

Public Property Get p_LstBox() As Access.ListBox
 Set p_LstBox = LstBox
End Property

Public Property Set p_LstBox(ByRef pNewValue As Access.ListBox)
 Set LstBox = pNewValue
End Property

Private Sub LstBox_Click()
Dim vVal As Variant
Dim lst As String

vVal = LstBox.Value
lst = LstBox.Name

Select Case lst
    Case "List16"
        'Code
    Case "List18"
        'Code
End Select

  MsgBox "Clicked: " & vVal, , lst

End Sub

It can be modified with additional sub-routines to capture any other Event Raised on the List Box. The existing Code works on similar lines to the ComboBox and displays the selected item value in the message box.

The Class Module for Options Group: ClsOption

The Options Group has three different styles: Option Buttons, Check Boxes, and Toggle Buttons. In our Demo Form, we have used only two of these styles — Option Buttons and Check Boxes. All three, however, work in the same way; the only difference is in their display style. Their control names start with the word Frame followed by a number, just like other controls on the form (e.g., Frame18, Text2, Combo10, List12, etc.).

The ClsOption Class Module Code is given below:

Option Compare Database
Option Explicit

Private WithEvents Opts As Access.OptionGroup

Public Property Get p_Opts() As Access.OptionGroup
  Set Opts = Opts
End Property

Public Property Set p_Opts(ByRef pNewValue As Access.OptionGroup)
  Set Opts = pNewValue
End Property

Private Sub Opts_Click()
Dim txtName As String, intVal As Integer
Dim msg As String, strVal As String

intVal = Opts.Value
strVal = Opts.Name

Select Case strVal
    Case "Frame25"
        Select Case intVal
            Case 1
                'code
            Case 2
                'Code
            Case 3
                'Code
        End Select
    Case "Frame34"
        Select Case intVal
            Case 1
                'Code
            Case 2
                'Code
            Case 3
                'Code
        End Select
End Select

msg = msg & " Click :" & intVal

MsgBox msg, , Opts.Name

End Sub

Option Group items have labels that indicate their actual purpose and meaning, and each item is assigned an index number starting from 1. In the Click event procedure, we check the selected item's predefined index and perform the Task, such as opening a Form, displaying a Report, running a Macro, or any other.

Whenever an item is clicked, the frame’s events are triggered, and we use the selected index number to run the corresponding action.

Now, the Class Modules for all the controls on the Demo Form are ready.

Note: Some controls on the form may not be associated with any events and therefore will never fire an event. In such cases, we do not create Class Module instances for them. However, we can still read from or update the values of these controls from within the event procedures of other controls that do have Class Module instances.
Example: On the Tab Control pages, we have two Text Boxes that display the computer name and the Windows user name. These values are updated from the Tab Control’s Class Module, even though the Text Boxes themselves do not raise any events.
At this stage, we now have sample Class Modules (we can refer to them as Class Module Templates) for all types of controls on the form. Depending on the needs of a specific form in your project, you can create copies of the required Class Module Templates and customize their existing subroutines—or add new ones—to capture the necessary events and execute the related code.

The Derived Class Module: ClsControls_All

We now need an intermediary Class Module to act as a bridge between the stand-alone Class Modules for each type of control and the Form’s own Class Module. This intermediary will help organize all the control-specific Class Modules used on the form and also enable the required events for each control.

To achieve this, we will create a Wrapper Class Module that contains properties for all the different control-type Class Modules. It will scan the controls on the form, match them by their control names, and then enable the corresponding events by linking each control to its appropriate Class Module instance.

The Wrapper Class Module ClsControls_All Code is given below:

Option Compare Database
Option Explicit

Private tx As ClsText
Private cmd As ClsCmdButton
Private cbo As Clscombo
Private lst As ClsListBox
Private opt As ClsOption
Private tbc As ClsTabCtrl
Private Coll As Collection
Private fom As Access.Form

Public Property Get p_fom() As Access.Form
    Set p_fom = fom
End Property

Public Property Set p_fom(ByRef objFrm As Access.Form)
    Set fom = objFrm
    Class_init
End Property

Private Sub Class_init()
Dim ctl As Control
Const Evented = "[Event Procedure]"

Set Coll = New Collection

For Each ctl In fom.Controls 'check through Form controls

    Select Case TypeName(ctl) 'pick only the required control type
           Case "TextBox"
                Select Case ctl.Name
                    Case "Text2", "Text4", "Text6"
                        Set tx = New ClsText 'create new instance
                        Set tx.p_frm = fom 'assign Form Object to property
                        Set tx.p_txt = ctl 'assign control to p_txt Property
                        
                        tx.p_txt.AfterUpdate = Evented 'enable AfterUpdate Event
                        tx.p_txt.OnLostFocus = Evented 'enable LostFocus Event
                        
                'Add ClsText Object instance tx to Collection
                        Coll.Add tx
                'Release ClsText Object tx from memory
                        Set tx = Nothing
                End Select
                
            Case "TabControl"
                Set tbc = New ClsTabCtrl 'create instance of ClsTabCtrl
                Set tbc.p_frm = fom 'pass Form Object to p_frm Property
                Set tbc.p_Tb = ctl 'pass Tab Control to p_Tb Property
                
                tbc.p_Tb.OnChange = Evented 'enable OnChange Event
                
            'Add ClsTabCtrl instance tbc to Collection Object
                    Coll.Add tbc
            'Release tbc instance from memory
                Set tbc = Nothing
            
            Case "CommandButton"
                Select Case ctl.Name
                    Case "Command8", "Command9"
                    
                       Set cmd = New ClsCmdButton 'create new instance of ClsCmdButton
                       Set cmd.p_Btn = ctl ' pass Command Button control to p_Btn Property
                       
                       cmd.p_Btn.OnClick = Evented 'enable OnClick Event
                       
            'Add ClsCmdButton instance cmd to Collection Object
                      Coll.Add cmd
            'Release cmd instance from memory
                       Set cmd = Nothing
                End Select
                
            Case "ComboBox"
                Select Case ctl.Name
                    Case "Combo10", "Combo12"
                        Set cbo = New Clscombo ' create new instance of ClsCombo Class
                        Set cbo.p_cbx = ctl ' pass control (Combo10 or Combo12) to CB Property
                        
                        cbo.p_cbx.OnClick = Evented ' enable OnClick Event
                        
            'Add ClsCombo instance cbo to Collection Object
                       Coll.Add cbo
            'Release cbo instance from memory
                      Set cbo = Nothing
                End Select
                
            Case "ListBox"
                Select Case ctl.Name
                    Case "List14", "List16"
                      Set lst = New ClsListBox ' create new instance of ClsListBox
                        Set lst.p_LstBox = ctl ' pass the control to lst.LB Property of instance.
                        lst.p_LstBox.OnClick = Evented ' enable OnClick Event
                        
            'Add lst instance to Collection Object
                       Coll.Add lst
            'Release lst instance from memory
                      Set lst = Nothing
                End Select
                
            Case "OptionGroup"
                Select Case ctl.Name
                    Case "Frame25", "Frame34"
                      Set opt = New ClsOption ' create new instance
                        Set opt.p_Opts = ctl  ' pass control to opt.OB Property
                        
                        opt.p_Opts.OnClick = Evented ' enable OnClick Event
                        
            'Add opt instance to Collection Object
                       Coll.Add opt
            'Release lst instance from memory
                      Set opt = Nothing
                End Select
    End Select
    
Next

End Sub

In the global declaration area of the ClsControls_All Class Module, we have declared the Class Module objects of all the controls on the form with Private scope. We have declared a Collection object and a Form object as properties of this class.

From our earlier trial runs, we learned that using a Collection object is a simpler and more efficient approach than using arrays of Class Module instances. The array method requires maintaining separate index counters for each control type, incrementing them for each new object instance, and repeatedly resizing the arrays to add new elements.

By contrast, adding each instance of a control-specific Class Module directly to a single Collection object is much easier and eliminates all extra steps.

The Set property procedure assigns the Form object reference, passed from the active form, to the Fom property in this class.

The Class_Init() subroutine is called from the Set property procedure to enable the events for each required control on the form. This ensures that when an event occurs, it is captured by the corresponding subroutines in their respective Class Modules.

The Get property procedure handles external requests for the Form object reference stored in this class.

Inside Class_Init(), a Control object (ctl) is declared to iterate through the form’s controls, and a string constant named Evented is set to the value "[Event Procedure]", which is used when assigning event procedures to control properties.

The Collection object (Coll) is then instantiated to store the Class Module instances of the controls.

Each control on the form—such as Text Boxes, Tab Controls, and others—is scanned for, and if it requires event handling (like AfterUpdate, LostFocus, or Click), a new instance of its corresponding Class Module is created, events are enabled, and the instance is added to the Coll collection object.

Remember, each Form control’s Object Property—such as a TextBox—was declared with the WithEvents keyword in its corresponding Class Module. This allows the Class Module instance to capture and handle events that occur on the control at runtime. When an event occurs, it is caught by the Class Module object instance stored in the Collection, and the relevant subroutine code is executed.

In the Class_Init() subroutine, a For Each ... Next loop iterates through all controls on the form. Each control is tested for its type—such as TextBox, TabControl, ComboBox, and so on. Controls that are not event-driven, like Labels, Images, or ActiveX objects, are skipped.

Within each control type, an additional check is performed on the control’s Name property to identify which controls should be wired to events.

For example, when a control of type TextBox is found, its Name is checked against "Text2", "Text4", and "Text6".

If the control matches one of these names, a new ClsText Class Module instance (e.g., tx) is created.
The active form reference (fom) is assigned to the tx.p_frm property, and the TextBox control reference (ctl) is assigned to the tx.p_txt property.
The specific events for that control—such as AfterUpdate and LostFocus—are then enabled.

Finally, this initialized ClsText object (tx) is added as an item to the Collection object, so that when any of these events fire, they are captured and handled through their Class Module instance.

The same process is repeated for Text4 and Text6. If each TextBox requires different events to be enabled, then they must be handled in separate Case statements. Each control is configured with its specific events, and a new instance of the corresponding Class Object is created and added to the Collection.

However, since all three TextBoxes (Text2, Text4, and Text6) are enabled with the same AfterUpdate and LostFocus events, their names are grouped within a single Case statement for convenience.

Note: There are two additional TextBoxes, one on each Tab Page. Although they are part of the form’s controls, they are not enabled with any events. Instead, they are used to display values dynamically during the execution of the Tab Control’s Change() event procedure.

The Tab Control does not raise a Click event when its pages are selected; instead, it triggers a Change event. By default, TabCtl18.Pages(0) is the current page.

When the user switches to 'TabCtl18.Pages(1)', the TextBox named UserName is updated with the Windows user name.

When switching back to the first page, the TextBox named Computer is updated with the computer’s name, using the statement:

frm.Controls("Computer").Value = Environ("ComputerName")

To enable this, the ClsTabCtrl Class Module includes a Form object property (frm) that allows the class to directly reference and update these TextBox controls on the form.

All other controls on the form—Command Buttons, Combo Boxes, List Boxes, and Option Groups—are currently enabled only for the click event. Their respective Class Modules contain only Click event procedures to capture the event and display a message for demonstration purposes.

If you want to capture any additional events from these controls, simply add the corresponding event procedures (e.g., GotFocus, AfterUpdate, Change) in their respective Class Modules, and then enable those events in the derived Class Module ClsControls_All.

The Form: frmControls_All's Class Module Code

Option Compare Database
Option Explicit

Private A As New ClsControls_All

Private Sub Form_Load()
    Set A.p_fom = Me
End Sub

The derived class object ClsControls_All is declared and instantiated as the object A.

The current form object is then passed as a parameter to the A.p_fom property procedure.

This is the only code required in the form’s class module to initialize and connect all the individual control class modules through the ClsControls_All class.

The Demo Run

Download the Demo database from the download link given at the end of this Article.

When you open the Demo Database, the Form frmControls_All opens in Normal View by default.

Testing Text Box – AfterUpdate, LostFocus Events.

Press the Tab key while the insertion point is in the first TextBox to trigger its LostFocus event. When this event fires, the TextBox is automatically filled with the text “msaccesstips.com”. This approach is useful for inserting default text when a field should not be left empty.

Next, modify the text by adding or removing some characters, and then press Tab again. This time, the AfterUpdate event will fire, and a message box will appear displaying the updated text.

The other two TextBoxes on the form respond to these events in the same way.

Note: The default text “msaccesstips.com” is inserted only if the TextBox is left empty when it loses focus. If you type any value into the TextBox and then press Tab, both the AfterUpdate and LostFocus events will fire sequentially.

Testing Tab Control Page Click Event.

By default, the first Tab Control Page will be the active page. Click on the second Tab Control Page. The Change Event fires, and the TextBox on the page is updated with the Windows User Name.

Click on the first Tab Page. The text box on the first tab page is updated with the Computer Name.

**Command Button Click Event.**

Click on the top Command Button. This will open Form1 displaying some text with hyperlinks to this Website.

The second Command Button Click displays a message from the ClsText Class Module instance Item from the Collection Object.

Click Events of ComboBox, ListBox, Option Group

All the controls on the frmControls_All form are enabled with the Click event through the Derived Class Module (ClsControls_All). Clicking any of these controls will display the selected item in a message box.

The event-enabled subroutines in these class modules are provided purely to demonstrate the programming approach.

We have developed a systematic and customizable Class Module template that makes VBA coding much easier. When starting a new project, you can simply copy this template and customize it to fit your project’s requirements. This approach not only simplifies debugging but also helps you quickly locate and fix any issues during field testing or while resolving logical errors in your project.

The Functional Diagram.

But before moving on, if you haven’t fully understood how all the pieces of this puzzle fit together and how they interact in their respective roles, take a moment to closely examine the diagram below.

Right-click to open Large Image in New Tab/Window.

I suggest revisiting the opening pages of this series; the links are provided at the bottom of this page.

If you have a ready-to-use Access database, make a copy of it and try restructuring the code based on what you’ve learned here. You’ll notice the difference when your project becomes better organized and easier to manage.

The Demo Database.

You may download the demo database from the link given below and try out the Form as explained above.

Download AllControls_2007.zip

Download AllControls_2003.zip

Links to WithEvents Tutorials.

WithEvents Textbox CommandButton Dictionary

a.p.r. pillaiJuly 29, 2019WithEvents No comments

Continued from Last Week's Post

So far, we have used the Collection object to hold all the Form controls' Class Object Instances as an alternative to using class object Arrays.

If you have landed directly on this page, please go through the earlier post, “WithEvents TextBox and CommandButton Control Arrays”, before continuing.

In that earlier post, we used class object arrays of TextBox and CommandButton Controls on the Form. The main challenge with this approach was the need to maintain separate array indexes for each control type’s class object instances, as well as repeatedly re-dimensioning the arrays. When several different types of form controls are involved—such as ComboBoxes, ListBoxes, OptionGroups, TabControls, and others—this method quickly becomes cumbersome and complex.

We overcame this complexity by using a Collection object Container for all the control class object instances.

We have already explored a few examples using the Collection object in some of our earlier posts. Links to all our posts on Form and Report control event capturing are provided at the end of this article for your reference.

Usage of Dictionary Object, Replacing Array

Here, we will replace the Collection Object with the Dictionary Object to hold all the Form Controls' Class Object Instances. Let us see what difference it makes to implement the Dictionary method.

Here, we will examine last week's Derived Class Object (ClsTxtBtn_Derived) Code without any change. The full VBA Code of last week's Class Module Derived Object Code is given below:

Option Compare Database
Option Explicit

Private T() As New ClsText
Private B() As New ClsCmdButton
Private m_Frm As Access.Form

Public Property Get mfrm() As Access.Form
  Set mfrm = m_Frm
End Property

Public Property Set mfrm(ByRef frmObj As Access.Form)
  Set m_Frm = frmObj
  init_Class
End Property

Private Sub init_Class()
   Dim ctl As Control
   Dim tCount As Long
   Dim bCount As Long
Const Evented = "[Event Procedure]"

tCount = 0 'counter for textbox controls
bCount = 0 'counter for Command Button controls
For Each ctl In m_Frm.Controls
   Select Case TypeName(ctl) 'Type name TextBox or CommandButton ?
     Case "TextBox"
         tCount = tCount + 1 'increment counter
         ReDim Preserve T(1 To tCount)  'redimension TextBox Class Objecct Array
         Set T(tCount).p_frm = m_Frm 'pass Form Object
         Set T(tCount).p_txt = ctl 'pass the TextBox control
         
         Select Case ctl.Name
            Case "Text2" ' enable AfterUpdate and OnLostFocus Events
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text4"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text6"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
        End Select
            
      Case "CommandButton"
         bCount = bCount + 1 'increment counter for CommandButton
         ReDim Preserve B(1 To bCount) 'redimension Button Class Object Array
         Set B(bCount).p_Btn = ctl 'pass CommandButton control
        
        Select Case ctl.Name
            Case "Command8" 'Enable Click Event
                B(bCount).p_Btn.OnClick = Evented
            Case "Command9"
                B(bCount).p_Btn.OnClick = Evented
        End Select
      End Select
    Next
End Sub

The ClsText and ClsCmdButton classes are defined as array properties—T() and B()—with an initially undefined number of elements. The Form property (m_Frm) is declared next, followed by its Get and Set property procedures for assigning and retrieving the active Form object.

After the Form object is assigned in the Set property procedure, the Class_Init() subroutine is called to enable the required event procedures for the form’s controls.

In this example, three text boxes (Text2, Text4, Text6) are enabled only for the AfterUpdate() and LostFocus() events, since the ClsText class module currently contains subroutines only for these events.

Note: Even if the ClsText class module contains subroutines for other events (such as BeforeUpdate, GotFocus, KeyDown, KeyUp, etc.), not all of them need to be enabled for every project. Only the events required for a specific form are activated in the derived class module, while the other event subroutines remain dormant until needed for a particular form or report.

The Form and TextBox control references are passed from the Derived Object to the ClsText Class Object Properties through the following statements:

Set T(tCount).p_frm = m_Frm
Set T(tCount).p_txt = ctl

Command Button Control reference is passed to the ClsCmdButton Class Module. The Command Button Click Event is enabled.

This procedure is repeated for each Control on the Form in the Derived Class Object Module: ClsTxtBtn_Derived.

Logical Error in VBA Code

The ClsTxtBtn_Derived Class Module Code works perfectly for our earlier example. But there is a logical error in the code, and we will correct it in later versions of the Code.

The placement of the following lines of Code under the Case "TextBox is not in the correct location " is incorrect because there is a chance that the Class Module Object will occupy more memory space.

Case "TextBox"
         tCount = tCount + 1 'increment counter
         ReDim Preserve T(1 To tCount)  'redimension TextBox Class Objecct Array
         Set T(tCount).p_frm = m_Frm 'pass Form Object
         Set T(tCount).p_txt = ctl 'pass the TextBox control

The issue with the current code placement is that if there are extra text boxes on the form with no events enabled, the code will still instantiate class objects for those controls and add them to the array, unnecessarily consuming memory. This happens silently without causing any functional errors, but it is logically incorrect.

To correct this, the lines that instantiate and add the class object must be placed inside the corresponding Case blocks—immediately below each Case "Text2", Case "Text4", and Case "Text6" statement. This ensures that only the intended controls with events enabled will get their class object instances created.

A similar change is required for the Command Button Case statements.

However, if all the TextBoxes and Command Buttons on the form are intended to have the specified events enabled, then there is no code change.

In our demo forms, we only used two or three text boxes and a few command buttons, and we enabled events for all of them. In such situations, the current code placement is acceptable and intentional, as it keeps the logic simple and avoids repeating the instantiation code inside each Case statement.

The Dictionary Object.

If you have not come across the Dictionary Object and its usage so far, then please visit the following links, or at least the first two:

When comparing the Collection and Dictionary objects, both support the Add method and accept two parameters—Key and Item—but their usage differs:

Dictionary object syntax:

'Object.Add' ItemKey, Item

Both parameters are mandatory.

Collection object syntax:

'Object.Add' Item, ItemKey

The ItemKey is the second parameter and is optional.

Unlike the Dictionary object, the ItemKey in a Collection is optional. However, since all control names on a form are unique, they can serve as ItemKey values when using a Dictionary.

The Dictionary object is part of the Windows Scripting Runtime library and can be created in VBA with the following statement:

Dim D as Object
Set D = CreateObject("Scripting.Dictionary")

Alternatively, you can enable the Microsoft Scripting Runtime library in your project by selecting it from Tools → References in the VBA editor. After adding this reference, you can declare a Dictionary object using early binding, as shown below:

Dim D As Dictionary

Using early binding offers the added benefit of IntelliSense support—when you type the declared object name followed by a dot (for example, D.), the VBA editor will display a list of all available properties and methods.

The new derived Class Module ClsTxtBtn_Dictionary (shown below) demonstrates this approach. Until now, we have used a Collection object as an alternative to arrays of class object instances. In this section, we will explore how to use the Dictionary object as a container for class object instances (specifically, for TextBox and CommandButton controls).

Option Compare Database Option Explicit Private T As ClsText Private B As ClsCmdButton Private m_Frm As Access.Form

Private D As Object Public Property Get mfrm() As Access.Form Set mfrm = m_Frm End Property Public Property Set mfrm(ByRef frmObj As Access.Form) Set m_Frm = frmObj init_Class End Property Private Sub init_Class() Dim ctl As Control Const Evented = "[Event Procedure]"

Set D = CreateObject("Scripting.Dictionary")

For Each ctl In m_Frm.Controls Select Case TypeName(ctl) 'Type name TextBox or CommandButton ? Case "TextBox" Select Case ctl.Name Case "Text2" Set T = New ClsText 'create new instance of Class Module Set T.p_frm = m_Frm 'pass Form Object Set T.p_txt = ctl 'pass the TextBox control T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item Set T = Nothing 'erase ClsText instance Case "Text4" Set T = New ClsText Set T.p_frm = m_Frm Set T.p_txt = ctl T.p_txt.AfterUpdate = Evented T.p_txt.OnLostFocus = Evented 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T Set T = Nothing Case "Text6" Set T = New ClsText Set T.p_frm = m_Frm Set T.p_txt = ctl T.p_txt.AfterUpdate = Evented T.p_txt.OnLostFocus = Evented 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T Set T = Nothing End Select Case "CommandButton" Select Case ctl.Name Case "Command8" Set B = New ClsCmdButton 'create new instance of ClsCmdButton Set B.p_Btn = ctl 'pass CommandButton control to Class Module B.p_Btn.OnClick = Evented 'Enable Click Event 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item Set B = Nothing 'erase ClsCmdBtn instance Case "Command9" Set B = New ClsCmdButton Set B.p_Btn = ctl B.p_Btn.OnClick = Evented 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B Set B = Nothing End Select End Select Next End Sub

Preparing for a Trial Run.

Steps to Implement the ClsTxtBtn_Dictionary Class Module
1. Create the Class Module
  - In the demo database you downloaded from last week’s post, open the VBA editor (Alt + F11).
  - From the Insert menu, select Class Module.
  - In the Properties window, set its (Name) property to:
    ClsTxtBtn_Dictionary.
2. Add the Code
  - Copy the ClsTxtBtn_Dictionary class code provided above.
  - Paste it into the newly created class module.
  - From the Debug menu, select Compile <your database name> to recompile the project and confirm that no errors occur.
3. Create a New Form
  - In the Navigation Pane, right-click the existing form frmClassArray and select Copy.
  - Right-click again and select Paste.
  - In the prompt, give the new form the name:
    frmClass_Dictionary.
4. Update the Form’s Code
  - Open frmClass_Dictionary in Design View.
  - Open its Code Module window (right-click the form’s title bar → Build Event → Code Builder, or press F7).
  - Replace its existing code with the new code provided below.
```
Option Compare Database
Option Explicit

Private A As New ClsTxtBtn_Dictionary

Private Sub Form_Load()
   Set A.mfrm = Me
End Sub
```
Save the Form with the changed code.
Open the Form in Normal View and try the TextBoxes and Command Buttons to test that the LostFocus, After Update, and Command Button Clicks work as before.

Revised Code Segments.

Since all three TextBoxes (Text2, Text4, and Text6) are enabled by the same set of Events (AfterUpdate & LostFocus), the Case statements can be clubbed into one line and can avoid duplication of Code as given below:

Select Case ctl.Name
            Case "Text2", "Text4", "Text6"
                Set T = New ClsText 'create new instance of Class Module
                Set T.p_frm = m_Frm 'pass Form Object
                Set T.p_txt = ctl 'pass the TextBox control

                    T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event
                    T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event
                
          'Add ClsTxt Class Object as Dictionary Object Item
                    D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item
                Set T = Nothing 'erase ClsText instance
        End Select

Similarly, both the Command buttons have only one common Event, the Click Event. Hence, their Code can also be combined into a single step, like the following code segment:

Select Case ctl.Name
            Case "Command8", "Command9"
                Set B = New ClsCmdButton 'create new instance of ClsCmdButton
                Set B.p_Btn = ctl 'pass CommandButton control to Class Module
           
                    B.p_Btn.OnClick = Evented 'Enable Click Event
            'Add ClsCmdButton Class Instance as Dictionary Item
                    D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item
                Set B = Nothing 'erase ClsCmdBtn instance
        End Select

The Revised ClsTxtBtn_Dictionary Code.

The full Class Module Code with the above change is given below:

Option Compare Database Option Explicit Private T As ClsText Private B As ClsCmdButton Private m_Frm As Access.Form Private D As Object Public Property Get mfrm() As Access.Form Set mfrm = m_Frm End Property Public Property Set mfrm(ByRef frmObj As Access.Form) Set m_Frm = frmObj init_Class End Property Private Sub init_Class() Dim ctl As Control Const Evented = "[Event Procedure]"

Set D = CreateObject("Scripting.Dictionary")

For Each ctl In m_Frm.Controls Select Case TypeName(ctl) 'Type name TextBox or CommandButton ? Case "TextBox" Select Case ctl.Name Case "Text2", "Text4", "Text6" Set T = New ClsText 'create new instance of Class Module Set T.p_frm = m_Frm 'pass Form Object Set T.p_txt = ctl 'pass the TextBox control T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item Set T = Nothing 'erase ClsText instance End Select Case "CommandButton" Select Case ctl.Name 'Both Command Buttons have only the same Click Event Case "Command8", "Command9" Set B = New ClsCmdButton 'create new instance of ClsCmdButton Set B.p_Btn = ctl 'pass CommandButton control to Class Module B.p_Btn.OnClick = Evented 'Enable Click Event 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item Set B = Nothing 'erase ClsCmdBtn instance End Select End Select Next End Sub

You may now create a new Class Module, paste the code provided earlier, and save it. Next, update the Form Module code to reference this new module name, and test the form controls to verify that they function as before.

So far, we have worked with only two types of controls—TextBox and CommandButton—using their respective class modules (ClsText and ClsCmdButton). But what if we need to handle all the types of controls on a form, such as ComboBoxes, ListBoxes, Tab Controls, and Option Groups? Each of these would require its own dedicated Class Module, similar to ClsText and ClsCmdButton.

We will conclude this topic with one or two more posts. We will create a sample database to demonstrate the Structured VBA Event Procedure reusable coding in a Standalone Class Module, rather than duplicating Subroutines for the same Events from the same type of controls in the Form Module. We will incorporate almost all Types of commonly used controls on a Form, and demonstrate how to handle their events through class modules.

If you have followed the earlier posts (links numbered 1 to 12 listed below), you should have no difficulty defining and configuring class modules for these additional controls in a similar way. Their Click events can then be enabled, making them ready for testing on the form.

Links to 'WithEvents' Coding Tutorial Pages.

WithEvents TexBox and CommandButton Control Arrays

a.p.r. pillaiJuly 18, 2019WithEvents 5 comments

Textbox and CommandButton Arrays.

I hope you’ve gone through the last three posts on Report-based Event Trapping in Class Modules and on modifying Report controls in real-time.

For your convenience, the links to those posts are provided below for quick reference, if needed:

So far, we have worked exclusively with TextBox arrays to capture native events in a Standalone Class Module and execute Event Procedures.

The TextBox Events Capturing Route Map.

Create a Base Class Module that defines Text Box Control and Form properties.
Add AfterUpdate() and LostFocus() subroutines to this class to handle these built-in events from the text boxes on the form. Inside each subroutine, identify the text box that triggered the event and execute the corresponding code based on its name.
Next, create a Derived Class Module using this Text Box class as the base class.
Within the derived class, create an array of base-class objects so that each element can handle the required events for a specific text box. This approach eliminates the need to keep empty AfterUpdate() and LostFocus() subroutine stubs on the form’s class module, which are otherwise required just to raise these events.
Because the base class contains only a single txt property (which can hold a reference to only one text box at a time), having multiple class objects—one per text box—is essential.
Instead of using an array of class objects, a Collection object can be used to store all the individual text box class instances. This method has proven to be a more flexible and reliable way to manage multiple text box class objects.

In brief, our idea is to create a dedicated Class Module Object for each control type—such as Text Box, Combo Box, List Box, Tab Control, and Option Group—each containing the required event procedures specific to that control.

In the Derived Class Module, we then include these control-specific class objects as properties (Base Class properties), depending on which control types are present on the form.

One important point to remember:

It is absolutely necessary to reference a different control directly from the Class Module Object (other than the one that triggered the event). For that, we must add a Form object property also to the Base Class Module and assign the current Form object to it through the Derived Class Module. This allows the class to access and update/retrieve values from other controls on the same form or Subforms as needed.

The TextBox and CommandButton Arrays.

So far, we have worked with arrays of class modules containing only the TextBox property.

Now, we will create a few TextBoxes and Command Buttons on a form and explore how their object references can be assigned to instances of the TextBox and Command Button Base Class module arrays within a Derived Class module.

Each array element of a class object corresponds to a specific control (either a TextBox or a Command Button) and is responsible for capturing that control’s built-in events and executing the associated event procedures from within the corresponding array element.

A sample image of the form named frmClassArray in Design View is shown below:

Right-click to open Large Image in New Tab/Window.

There are three Text Box controls on the form named Text2, Text4, and Text6. Each of these will be represented by individual single-dimensional array elements (three elements in total) within the TextBox base class module. Similarly, there are two Command Button controls named Command8 and Command9, which will be represented as a two-element array within the Command Button base class module.

Class Module for TextBox: ClsText.

The Class Module: ClsText Code for Text Box control is given below:

Option Compare Database
Option Explicit

Private frm As Access.Form
Private WithEvents txt As Access.TextBox

Public Property Get p_Frm() As Access.Form
   Set p_Frm = frm
End Property

Public Property Set p_Frm(ByRef pNewValue As Access.Form)
   Set frm = pNewValue
End Property

Public Property Get p_txt() As Access.TextBox
   Set p_txt = txt
End Property

Public Property Set p_txt(ByRef ptxtValue As Access.TextBox)
   Set txt = ptxtValue
End Property

Private Sub txt_AfterUpdate() 'Capture AfterUpdate Event here
Dim txtName As String, varVal As Variant
Dim msg As String

txtName = txt.Name 'save TextBox name
msg = txtName 'save the same name in msg variable for msgbox
varVal = txt.Value 'read the value, if any, from TextBox

'write required code under textbox name
Select Case txtName
    Case "Text2"
        'Code
    Case "Text4"
        'code
    Case "Text6"
        'Code
End Select
'a common message displays for
'all TextBoxes for demo purposes.
msg = msg & ": " & varVal
MsgBox msg, , "AfterUpdate()"

End Sub

Private Sub txt_LostFocus() 'LostFocus actions from TextBoxes are captured here.
Dim txtName As String, varVal As Variant
Dim msg As String, strVal As String
Dim ctl As Control

txtName = txt.Name
varVal = txt.Value
strVal = Nz(varVal, "")

'if any textbox is empty
'then inserts the text: MSAccessTips.com
Select Case txtName
    Case "Text2" ' inserts MSAccessTips.com when empty.
        GoSub Check
    Case "Text4"
        GoSub Check
    Case "Text6"
        GoSub Check
End Select

Exit Sub

Check:
   If Len(strVal) = 0 Then
     txt.Value = "MsAccessTips.com"
   End If
Return

End Sub

The form-level property variable frm is declared at the beginning of the class module, followed by the Access.TextBox property txt. The txt property is declared using the WithEvents keyword, which allows the class to capture both built-in and user-defined events triggered on the form.

The frm object property serves as a reference to any TextBox control on the form other than the one that triggered the event, if needed. Both properties are declared with Private scope to prevent direct access from outside the class module.

Next, Get and Set property procedures — p_Frm() for the frm property and p_txt() for the txt property — are implemented. The active form object is assigned to the frm property during the Form_Load() event, not directly, but through the derived class module ClsTxtBtn_Derived.

The p_txt() property procedures are invoked from the derived class module (ClsTxtBtn_Derived—the VBA code for this module is provided after the ClsCmdButton class module code). These procedures assign each TextBox control on the form to the corresponding element in the ClsTxt Class Module array.

Once the frm property is assigned the active Form object, it becomes possible to reference any control on the form to read or write its values.

Within the event-capturing subroutines, you can also reference a different TextBox control on the same form to read or write its value using the following approach:

'Assume Text2 is the active control now
Set mytxt = frm.Controls("Text6")

'Read value from mytxt
    mytxtvalue = mytxt.Value

'Write value to mytxt
    mytxt = 25

The txt_AfterUpdate() subroutine handles the events for Text2, Text4, and Text6 individually, corresponding to their respective array elements. You can add validation checks on the TextBox values, display messages, replace values, or perform other actions. In this sample subroutine, a generic message is displayed indicating the name of the TextBox and confirming that the subroutine executed upon the event being triggered on the form.

The txt_LostFocus() event procedure automatically inserts the text "msaccesstips.com" if the TextBox is left empty.

Note: During the demo run of the attached database, you can tab through the text fields to automatically insert the default text. Editing a field and pressing Tab or Enter will trigger the AfterUpdate() event.

Class Module of Command Button: ClsCmdButton.

The Command Button Class Module: ClsCmdButton VBA Code is given below:

Option Compare Database
Option Explicit

Private WithEvents Btn As Access.CommandButton

Public Property Get p_Btn() As Access.CommandButton
  Set p_Btn = Btn
End Property

Public Property Set p_Btn(pBtnValue As Access.CommandButton)
   Set Btn = pBtnValue
End Property

Private Sub Btn_Click()
Dim BtnName As String

BtnName = Btn.Name

Select Case BtnName
    Case "Command8"
        DoCmd.OpenForm "Form1", acNormal
    Case "Command9"
        MsgBox "Thank you, " & BtnName & " Clicked.", , BtnName
End Select

End Sub

The Btn property is declared with Private scope and the WithEvents keyword to capture events triggered by the Command Button on the form.

The Get and Set Property Procedures (p_Btn()) are used to assign or retrieve the Command Button object to/from the property.

Within the Btn_Click() event procedure, the name of the clicked Command Button is checked to determine the corresponding action:

Command8: Opens the form Form1.
Command9: Displays a message.

The Derived Class Module: ClsTxtBtn_Derived

In this example, we will monitor and capture the enabled built-in events from both TextBoxes and Command Buttons on the form, and execute their corresponding event procedures within the respective Class Module object instances.

To implement this, we use a TextBox and a CommandButton Class Module as base classes. Both of these base classes are incorporated into the Derived Class Module ClsTxtBtn_Derived, which manages the assignment of form controls to class instances and handles event execution seamlessly.

This approach keeps the form’s module clean while allowing the centralized, reusable code for handling multiple control events.

The derived Class Module's VBA Code is given below:

Option Compare Database
Option Explicit

Private T() As New ClsText
Private B() As New ClsCmdButton
Private m_Frm As Access.Form

Public Property Get mfrm() As Access.Form
  Set mfrm = m_Frm
End Property

Public Property Set mfrm(ByRef frmObj As Access.Form)
  Set m_Frm = frmObj
  init_Class
End Property

Private Sub init_Class()
   Dim ctl As Control
   Dim tCount As Long
   Dim bCount As Long
Const Evented = "[Event Procedure]"

tCount = 0 'counter for textbox controls
bCount = 0 'counter for Command Button controls
For Each ctl In m_Frm.Controls
   Select Case TypeName(ctl) 'Type name TextBox or CommandButton ?
     Case "TextBox"
         tCount = tCount + 1 'increment counter
         ReDim Preserve T(1 To tCount) 'redimension TextBox Class Objecct Array
         Set T(tCount).p_Frm = m_Frm 'pass Form Object
         Set T(tCount).p_txt = ctl 'pass the TextBox control
         
         Select Case ctl.Name
            Case "Text2" ' enable AfterUpdate and OnLostFocus Events
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text4"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text6"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
        End Select
            
      Case "CommandButton"
         bCount = bCount + 1 'increment counter for CommandButton
         ReDim Preserve B(1 To bCount) 'redimension Button Class Object Array
         Set B(bCount).p_Btn = ctl 'pass CommandButton control
        
        Select Case ctl.Name
            Case "Command8" 'Enable Click Event
                B(bCount).p_Btn.OnClick = Evented
            Case "Command9"
                B(bCount).p_Btn.OnClick = Evented
        End Select
      End Select
    Next
End Sub

The TextBox Class Module ClsText is instantiated as an array with a dynamically determined number of elements. Similarly, the ClsCmdButton Class Module is declared in the next line.

The Form object m_frm is declared to receive the active form reference passed from the Form_Load() event procedure. It acts as an intermediary, providing the reference to each array element of the ClsText class object.

The Get and Set property procedures mFrm() control the retrieval and assignment of the m_frm form object.

Within the Set property procedure, the Class_Init() subroutine (distinct from the built-in Class_Initialize()) is called to create separate class object arrays for each TextBox and Command Button on the form.

A single Control (ctl) object and two counter variables—tcount for TextBoxes and bcount for Command Buttons—are used as array indexes during the re-dimensioning of the ClsText and ClsCmdButton class object arrays.

The constant Evented variable is assigned the string "[Event Procedure]".

Next, both counter variables—tcount for TextBoxes and bcount for Command Buttons—are initialized to zero.

A For Each ... Next loop iterates through all controls on the form and checks each control's type using the TypeName(ctl) function. If the Control is a TextBox, the tcount variable is incremented by one.

The TextBox class module array T is then re-dimensioned to accommodate the new element while preserving existing data, using:

ReDim Preserve T(1 To tcount)

Note: In this ReDim Preserve statement, we do not specify the class type (e.g., As ClsText) as we would with normal variables like:

ReDim Preserve Qty(1 To cnt) As Single

This is because arrays of objects in VBA can only declare the object type when initially dimensioned, not during ReDim Preserve.

Next, the Form object reference is assigned to the TextBox class module property for the current array element:

Set T(tcount).p_Frm = m_Frm

This ensures that each instance of the class has access to the form.

Then, the TextBox control reference is passed to the same array element:

Set T(tcount).p_txt = ctl

At this stage, we check the TextBox name to enable the required built-in Events. Different TextBoxes may require different Events depending on the purpose of that data field.

For demonstration purposes, we apply a blanket rule and enable both the AfterUpdate and LostFocus Events for all TextBoxes.

Hence, the following statements are added for every TextBox control:

' Example statements to enable Events

T(tCount).p_txt.AfterUpdate = Evented

T(tCount).p_txt.OnLostFocus = Evented

When a Command Button is encountered, its counter variable bcount is incremented by one. The Command Button class array B is then re-dimensioned to 1 to bcount, preserving any previous array elements containing data.

Unlike the TextBox class, the Command Button class module does not require a Form property.

The current Command Button control reference is assigned to the bcount-th element of the array:

Set B(bcount).p_Btn = ctl

Next, the Command Button name is checked to enable the required events. For demonstration purposes, the Click event is enabled for each Command Button.

Note: If all Command Buttons only require the Click event, there is no need to check individual names. Simply assigning the Click event immediately after setting the control reference is sufficient:

B(bcount).p_Btn.OnClick = "[Event Procedure]"

However, the detailed name-based checks are included here for clarity.

This process is repeated for all TextBoxes and Command Buttons on the form. Other controls, such as Labels or non-interactive controls, are ignored.

The Form frmClassArray Class Module Code.

Option Compare Database
Option Explicit

Private D As New ClsTxtBtn_Derived

Private Sub Form_Load()
   Set D.mfrm = Me
End Sub

In the Form_Load() event procedure, the current Form object is passed to the D.mFrm() property procedure of the derived class object ClsTxtBtn_Derived. The derived class then copies this Form object reference to each element of the ClsText class array, ensuring that every TextBox class instance has access to the parent form for reading or writing values as needed.

Downloads

You can download the demo database from the links below to explore and study the code in detail. To follow the execution step by step, run the code in Debug mode by pressing F8, which allows you to step through each line and observe how the Class Module objects handle the form controls’ events.

Download TextandButton2007.zip

Download TextandButton2003.zip