WithEvents Textbox CommandButton Dictionary ~ LEARN MS-ACCESS TIPS AND TRICKS

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 Command Button 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 as a 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, I will bring in 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 the later versions of the Code.

The placement of the following lines of Code under the Case "TextBox is not in the correct location 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 without any events enabled for them, 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 no code change is necessary.

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, where we will incorporate nearly all the common control types 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.