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 Class Object instances of the Form Controls as an alternative to Class Object Arrays.

If you have directly landed on this page, then please go through last Week's Post: WithEvents TextBox and CommandButton Control Arrays and then continue on this page.

Last week we have used Class Object Arrays of Text Box and Command Button Controls on the Form.

The difficulty of this method was to maintain separate Array Indexes for each type of control's Class Object instances, besides re-dimensioning each Array element every time. When there are several types of Form Controls, like Combo Boxes, List Boxes, Option Groups, Tab-Control, and others this approach will grow into a very complex situation. Earlier, we were able to alleviate this complexity by using Collection Object, as the container of all the controls Class Object Instances.

We have tried a few examples with Collection Object in some of our earlier posts. Links to all the Posts, on Form and Report Control's Event Capturing topic, are given at the end of this post for your reference.

Usage of Dictionary Object, Replacing Array

Here, we are going to use the Dictionary Object, instead of Collection Object, to hold all the Form Controls' Class Object Instances. Let us see how it works and what it takes 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 Objects T() & B() Properties, with an undeclared number of array elements. The Form Property m_Frm is declared as the third item followed by the Get/Set Property Procedures for the Form Object.

After assigning the Form Object in the Set Property Procedure the Class_Init() Sub-Routine is called to enable the required Form controls' Event Procedures.

Three Text Boxes (Text2, Text4, Text6) are enabled with the AfterUpdate() and LostFocus() Events only because we didn't set up any other Sub-Routine in the Class Module ClsText to capture other Events from the TextBoxes.

Note: Even if we have sub-routine code for, say BeforeUpdate, GotFocus, KeyDown, KeyUp, etc., in the ClsText Class Module we may not use all of them for a particular Project. Whatever Event we need to capture in a particular Form will only be enabled in the Derived Class Module. Other sub-routines will remain in the Class Module till we encounter a need for them in 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 to place, 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 above code placement is that if there are some extra text boxes on the Form, without any Event enabled on them, even then the Class Object will be instantiated for those cases also and added to the Array, occupying extra memory space. It will happen silently without any side effects.

The above code lines must be placed immediately below all the Case statements Case "Text2", Case "Text4", and Case "Text6" structures for the correction of the logical error. This change is required for the Command Button Case statements also. If all the TextBoxes and Command Buttons on the Form are enabled with some Event then no need for any change.

In all our demo Forms we have introduced two or three TextBoxes or a few Command Buttons and enabled some Events on all of them. In those situations the above Code placement is correct and intentionally made that logical position to keep the code simplified, avoiding duplication of code, under 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:

By Comparing the Collection Object with the Dictionary, both have the Add method and take two parameter values: ItemKey and Item.

Dictionary Object Syntax: Object.Add ItemKey, Item – both values are mandatory.

Collection Object Syntax: Object.Add Item, ItemKey – second parameter optional.

Unlike Dictionary Object the ItemKey is the second parameter in Collection Object and is optional. Since all the control names on the Form are unique it can be used as Dictionary Object ItemKey.

Dictionary Object is part of Windows Scripting Language and needs to create an Object in VBA with the following statement:

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

Alternatively, you can select and add the Microsoft Scripting Runtime Library File to your Project from Tools - - > References Library List. After that you can declare Dictionary Object as shown below:

Dim D as Dictionary

If you do this then it has an added advantage of displaying its list of Properties and Methods, when you type the declared Object name followed by a dot ( say D.)

The new derived Class Module ClsTxtBtn_Dictionary VBA Code is given below. So far we have used Collection Object as an alternative to Class Object Instance Arrays. Here we will learn the usage of Dictionary Object as a container of Class Object Instances (of TextBox and Command Button).

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.

Create a new Class Module in the Demo database, you have downloaded from last week's post, with the name ClsTxtBtn_Dictionary.
Copy and Paste the above Code into the Class Module and save.
Select Compile from the Debug Menu to recompile the database and ensure that no errors are encountered.
Make a copy of the Form frmClassArray with a new name frmClass_Dictionary.
Open the Form frmClass_Dictionary in the design view.

Display the Form's Code Module and change the Code to match the Code lines given 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 the LostFocus, After Update, and Command Button Clicks work as before.

Revised Code Segments.

Since, all the 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 also can 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 create a new Class Module, Copy and Paste the above Code and save it. Change the Form Module Code to incorporate the new name of this Module and try out the Form controls, to test whether all of them work as before.

We have tried only two types of controls, the Text Box and Command Button class Modules here. How it will be when we have to deal with all types of controls Combo, List, Tab Control, Option Group on the Form. All of them need separate Class Modules, like ClsText and ClsCmdButton. We will conclude this topic with one or two posts with almost all types of controls on the Form.

If you have progressed through the earlier Posts (the links: No. 1 to 12) presented below, then you will not find any difficulty in defining and setting up Class Modules for those controls mentioned above in a similar way, with Click Event enabled for each one of them yet to be tested form controls.