Collection Object Replaces Class Object Array

Continued from Wrapper Classes: The Reusable Class Module

In the past, we used the TextBox Wrapper Class Array to store TextBox instances with Events enabled, allowing us to capture them in memory and execute their associated Event Subroutines. While this Array-based method worked, it required frequent re-dimensioning and the maintenance of separate indices for different object types, such as Command Buttons, ComboBoxes, and List Boxes. Fortunately, there is a more efficient solution—the Collection Object.

The earlier article links are given below:

In this session of our Tutorial on the Title topic, we will learn the following tricks:

Usage of the Data Table.
Collection Object replaces the Object Array.
Command Button Wrapper Class.
Form Wrapper Class. Sections: Header, Detail, and Footer Event Procedure.
Using Timer-Interval Property for Digital Clock on the Form.
Form Closing Count Down without using TimerInterval Property.

Trial Run Form-Image, taken from the Countdown and Form closing phase.

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

Using a Data Table.

We often run various validation checks on the data entered into a TextBox before accepting it. The entered data may also participate in calculations, with the results stored in another TextBox on the Form. These Events can be handled in the Class Module, regardless of whether the TextBox’s Control Source is bound to a table field. The only difference is that, in the unbound case, the data is not stored anywhere—but we can still perform all necessary validations outside the Form Module, within the stand-alone Class Module.

The Collection Object replaces the Object Array.

With the new Coding procedure, we could reduce the number of Event Subroutines of a particular Event into a single one, without writing a separate Event Procedure for each Textbox.

For example, check the following sample Exit() Event Procedure of three TextBoxes written under a single Event Subroutine:

Private Sub Txt_Exit(Cancel As Integer)
Dim i As Integer, Msg As String
Dim info As Integer

Select Case Txt.Name
    Case "Description"  'TextBox - Description
        If Len(Nz(Txt.Value, "")) = 0 Then
            MsgBox "Item Description Empty"
            Cancel = True
        End If
        
        
    Case "Quantity"     'TextBox - Quantity
        i = Nz(Txt.Value, 0)
        If i < 1 Or i > 10 Then
            Msg = "Valid Value Range 1 - 10 Only."
            info = vbCritical
            MsgBox Msg, vbOK + info, "Quatity_Exit()"
        GFColour frm ', Txt
            Cancel = True
        Else
            Msg = "Quantity: " & i & " Valid."
            info = vbInformation
            MsgBox Msg, vbOK + info, "Quatity_Exit()"
        End If
        
        
    Case "UnitPrice" 'TextBox - UnitPrice
    
        i = Nz(Txt.Value, 0)
        Msg = ""
        If i <= 0 Then
            Msg = "Enter a Value greater than Zero!"
             info = vbCritical
         GFColor frm 
            Cancel = True
            MsgBox Msg, vbOK + info, "UnitPrice_Exit()"
        Else
            info = vbInformation
            MsgBox "Unit Price: " & i, vbOK + vbInformation, "UPrice_Exit()"
        End If
    End Select
End Sub

If additional TextBoxes require `Exit()` event procedures, they can all be implemented within this structured event subroutine. If they are placed in the Form Module instead, each TextBox would require a separate event procedure, resulting in code scattered throughout the module alongside the Event procedures of other controls. This approach makes the code more difficult to organize and maintain compared to the centralized structure provided by the standalone Wrapper Class Module.

The TextBox controls (highlighted in red), as well as any additional TextBoxes, can also be configured to raise other required built-in events, such as `GotFocus`, `LostFocus`, and others. These events can be organized in the same manner, with all `GotFocus()` event procedures handled within a single `GotFocus()` event subroutine and all `LostFocus()` event procedures handled within a single `LostFocus()` event subroutine.

In practice, you need to write only one `BeforeUpdate()` event subroutine in the standalone Wrapper Class Module, even if the form contains 25 TextBoxes configured to raise the `BeforeUpdate` event. The `BeforeUpdate` event procedures for all those TextBoxes can be managed within that single subroutine. The same principle applies to all other TextBox events. As a result, all event-handling logic is centralized within a single standalone TextBox Wrapper Class Module, making the code easier to organize, maintain, and debug.

Similarly, every object type on the form can have its own standalone Wrapper Class Module, with all of its event procedures organized using the same structured approach.

All these event procedures are executed individually whenever their corresponding events occur. They may reside either in the Form Module or in a Wrapper Class instance loaded into memory, with both maintaining a reference to the same control on the form.

When TextBox controls (and other controls) are declared by using the `WithEvents` keyword, each control raises its own events independently. Because these controls are distributed across the form, the Form Module requires a separate event procedure for each TextBox.

However, by using our Wrapper TextBox Class, we need only a single instance of the TextBox object, declared with the WithEvents keyword. In the Wrapper Class, the TextBox is declared with the object name: Txt, as in Private WithEvents Txt As TextBox. As a result, all TextBox-related Event Subroutines are automatically prefixed with the object name: Txt, for example, Private Sub Txt_GotFocus().

A separate instance of the Wrapper Class Module is created for each TextBox on the form, and during the initialization process, each instance is automatically associated with its corresponding TextBox. This approach eliminates the need to manually create and maintain separate event procedures for every TextBox in the Form Module, significantly reducing development effort and simplifying database maintenance.

All GotFocus Events are captured in a single Subroutine. Within this Subroutine, we use a Select Case Txt.Name block to identify which TextBox triggered the Event—for example, Case "Quantity"—and then write the corresponding VBA code under that TextBox name. This individual name-checking approach is unnecessary if all TextBoxes require the same code for a particular Event, such as the TextBox highlighting example we discussed in an earlier episode. In that case, the code can be written once and applied globally. Alternatively, you can combine both approaches: write specific code for selected TextBoxes inside the Select Case structure, while placing the common logic outside it to apply across all TextBoxes.

The Wrapper Class also includes a declaration for a Form object, typically named frm, without the WithEvents qualification. This reference is essential because it allows the Wrapper Class, while in memory, to read from or write to other controls on the Form. In this way, the Wrapper Class is not limited to handling events of a single TextBox but can also interact with and manipulate other controls on the Form as needed.

To keep the Wrapper Class instances in memory, we initially used the Wrapper Class Array approach within the Class_Init() Subroutine of the WrapObject_Init2 Intermediate Class Module. However, when Wrapper Classes are also created for other Access controls—such as Command Buttons, Combo Boxes, List Boxes, and others—this method requires maintaining separate indices for each control type and re-dimensioning arrays accordingly.

A more flexible alternative is the Collection object. Unlike arrays, a Collection can hold any type of object or value, with or without an associated item key. This makes it far more convenient for managing multiple wrapper Class instances of different control types within a single structure.

For those unfamiliar with the Collection object, please review the following resources:

COLLECTION OBJECT

The Class_Init() Subroutine code for the WrapTextBox_Init Class was presented earlier in the Title Topic – Part Six. In the updated version, WrapTextBox_Init2, the same functionality has been implemented using the Collection object instead of Arrays.

WrapTextBox_Init Version VBA Code.

'Scan for TextBox Controls on Form
j = 0
'and Enable the required Event Procedures
For Each ctl In iFrm.Controls
Select Case TypeName(ctl)
    Case "TextBox"
        Select Case ctl.Name
            Case "Quantity"
                
                    Set iTxt = New WrapTextBox 'Create Instance
                    Set iTxt.tx_Frm = iFrm     'Pass Form object
                    Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form
            
                    iTxt.t_Txt.OnExit = EP     'Enable Event
                    iTxt.t_Txt.OnGotFocus = EP '     "
                    iTxt.t_Txt.OnLostFocus = EP '    "
        
                j = j + 1                       'increment counter
                ReDim Preserve TxtArray(1 To j) 'Redim Array
                Set TxtArray(j) = iTxt          'Save WrapTextBox Class
                Set iTxt = Nothing              'Erase temp Instance

            Case "UnitPrice"
                
                    Set iTxt = New WrapTextBox 'Create Instance
                    Set iTxt.tx_Frm = iFrm
                    Set iTxt.t_Txt = ctl
            
                    iTxt.t_Txt.OnExit = EP
                    iTxt.t_Txt.OnGotFocus = EP
                    iTxt.t_Txt.OnLostFocus = EP
        
                j = j + 1
                ReDim Preserve TxtArray(1 To j)
                Set TxtArray(j) = iTxt          'Save it in Object Array
                Set iTxt = Nothing              'Erase temp Instance
    
            Case "TotalPrice"
            
                Set iTxt = New WrapTextBox      'Create Instance
                Set iTxt.tx_Frm = iFrm
                Set iTxt.t_Txt = ctl
            
                iTxt.t_Txt.OnGotFocus = EP
                iTxt.t_Txt.OnLostFocus = EP
        
                j = j + 1
                ReDim Preserve TxtArray(1 To j)
                Set TxtArray(j) = iTxt           'Save it in Object Array
                Set iTxt = Nothing               'Erase temp Instance
            End Select
        
    End Select
Next

WrapTextBox_Init2 Version VBA Code.

'Scan for TextBox Controls on Form
'and Enable the required Event Procedures
For Each ctl In iFrm.Controls

    Select Case TypeName(ctl)
        Case "TextBox"
            Select Case ctl.Name
                Case "Description"
                    Set iTxt = New WrapTextBox2 'Create Instance
                    Set iTxt.tx_Frm = iFrm
                    Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form
                     
                    iTxt.t_Txt.OnGotFocus = EP '     "
                    iTxt.t_Txt.OnLostFocus = EP '    "
                
                    Coll.Add iTxt 'Save WrapTextBox2 Class
                
                    Set iTxt = Nothing 'Erase temp Instance
                    
                Case "Quantity", "UnitPrice"
                    Set iTxt = New WrapTextBox2 'Create Instance
                    Set iTxt.tx_Frm = iFrm
                    Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form
                     
                    iTxt.t_Txt.OnExit = EP     'Enable Event
                    iTxt.t_Txt.OnGotFocus = EP '     "
                    iTxt.t_Txt.OnLostFocus = EP '    "
                
                    Coll.Add iTxt 'Save WrapTextBox2 Class
                
                    Set iTxt = Nothing 'Erase temp Instance
                
                
                Case "Discount"
                    Set iTxt = New WrapTextBox2 'Create Instance
                    Set iTxt.tx_Frm = iFrm
                    Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form
                     
                    iTxt.t_Txt.OnGotFocus = EP '     "
                    iTxt.t_Txt.OnLostFocus = EP '    "
                
                    Coll.Add iTxt 'Save WrapTextBox2 Class
                
                    Set iTxt = Nothing 'Erase temp Instance
            End Select
        
    End Select
Next

Compare the two versions of the same VBA code. The lines highlighted in red in both versions pertain to the TextBox object instances.

The updated diagram illustrates the Z-Concept – a streamlined logic for handling Form Module coding through Class Objects, providing a clear overview at a glance.

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

Form Sections: Header, Detail, Footer, Event handling.

There are several events associated with a Form object. In this section, we will explore how the Click and MouseMove events are triggered from different areas of the form: the Detail, Form Header, and Form Footer sections.

You don’t need to design a form exactly like the one shown at the top of this page. Instead, you can download the demo database from the link at the end of this page and experiment with it yourself. Pay attention to the changes made in the WrapObject_Init2 and WrapTextBox modules. Then, compare this updated VBA code with the version you already have from Episode Six.

Episode Eight introduces two new Wrapper Classes: `WrapForm` and `WrapCmdButton`. The `WrapForm` class handles Form event procedures, including events associated with the various sections of a form.

WrapForm Class Module VBA Code is given below:

Option Compare Database
Option Explicit

Private WithEvents Sfrm As Access.Form
Private WithEvents ScD As Access.Section

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Access.Form Wrapper Class Module
'Author: a.p.r. pillai
'Date  : 27/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Public Property Get s_frm() As Access.Form
Set s_frm = Sfrm
End Property

Public Property Set s_frm(ByRef FFrm As Access.Form)
    Set Sfrm = FFrm
End Property

Public Property Get s_SecD() As Section
    Set s_SecD = ScD
End Property

Public Property Set s_SecD(ByVal iSec As Section)
    Set ScD = iSec
End Property

Private Sub scD_Click()
Select Case ScD.Name
    Case "FormHeader"
        MsgBox "FormHeader Click", , "FormHeader Section"

End Select
End Sub

Private Sub scD_DblClick(Cancel As Integer)
Select Case ScD.Name
    Case "FormFooter"
        MsgBox "FormFooter DblClick", , "FormFooter Section"
End Select
End Sub

Private Sub scD_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Select Case ScD.Name
    Case "Detail"
    Sfrm.Label39.Caption = X & " , " & Y
End Select
End Sub

In the Global declaration area of the Class Module, two Class objects, Form and Form Section objects, are declared. Followed by their Object assignment and Public Property procedures.

In the WrapForm Class Module demo, the Form sections’ Click, MouseMove, and Double-Click events are enabled. The MouseMove event in the Detail section displays the X and Y coordinates in a label. The Form Header is set up to respond to the Click event, while the Footer section responds to the Double-Click event. Both the Header and Footer events display a message when triggered.

Note: The indicator label 'Click here' in the Form Footer area may read as 'Double Click'.

The Digital Clock is run from the WrapObject_Init2 Class Module. The iFrm_Timer() Subroutine is also placed in this Module to update the label control Caption Property with the Time in "hh:nn:ss" format.

The clock runs at one-second intervals. It is not recommended to place this code in the WrapForm Class Module because each form section event creates a separate instance of the WrapForm Class in memory. If the clock code is placed there, it would run simultaneously from all three instances, causing unintended behavior.

The WrapCmdButton Class Module VBA Code.

Option Compare Database
Option Explicit

Private WithEvents cfrm As Form
Private WithEvents cmd As CommandButton 'CommandButton object

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Access.CommandButton Wrapper Class Module
'Author: a.p.r. pillai
'Date  : 27/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

'Form's Property GET/SET Procedures
Public Property Get c_Frm() As Form
    Set c_Frm = cfrm
End Property

Public Property Set c_Frm(ByRef cmdfrm As Form)
    Set cfrm = cmdfrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get c_cmd() As CommandButton
    Set c_cmd = cmd
End Property

Public Property Set c_cmd(ByRef pcmd As CommandButton)
    Set cmd = pcmd
End Property

Private Sub cmd_Click()
Select Case cmd.Name
    Case "cmdClose"
        If MsgBox("Close this Form?", vbOKCancel + vbQuestion, "cmd_Click") = vbOK Then
            DoCmd.Close acForm, cfrm.Name
        End If

End Select
End Sub

'Form Closing CountDown...
'Do Not write Form_Unload() on Form Module

Private Sub cfrm_Unload(Cancel As Integer)
Dim T As Double, t2 As Double
Dim strMsg As String

'cFrm.TimerInterval = 0 - Disable Clock, if necessary
strMsg = "Form will Close in "
cfrm.Label29.Visible = True
T = Timer
Do While Timer < T + 10
            t2 = Timer
            Do While Timer < t2 + 0.25
                DoEvents
            Loop
cfrm.Label29.Caption = strMsg & " " & Int((T + 10) - Timer) & " Seconds."
    DoEvents
Loop

End Sub

The Command Button Wrapper Class is straightforward, primarily handling the Click event. In the global declaration area, the Command Button and Form objects are defined. Following that, the Get and Set property procedures for both objects are implemented.

Next, the Private Sub cmd_Click() event subroutine handles the Click event for the cmdClose button. If additional command buttons are added to the form, their Click events can also be managed within this same subroutine by using a Select Case ... End Select structure to identify each button by name and handle their events individually.

Before the form closes, the Form_Unload() event fires. The remaining code runs a 10-second form-closing countdown simulation. A hidden label control positioned above the TextBoxes is made visible, and the countdown text is displayed there. When the count reaches zero, the form automatically closes.

Note: If a Form_Unload() event subroutine exists in the Form Module, the cfm_Unload() event subroutine in the WrapCmdButton class will not execute. This is because the Form Module’s Form_Unload() event takes priority and closes the form first.