Streamlining Form Module Code - Part Seven ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

Streamlining Form Module Code in Standalone Class Module.

Next week, we may continue from Part Six, refining the coding methods introduced earlier and introducing further improvements in their implementation.

Some readers may still question the effectiveness of these new coding methods. This page demonstrates the traditional Form Module approach alongside the streamlined standalone Class Module method. The demo is divided into three parts, ranging from standard coding to advanced automation, which helps determine which approach works best.

The Normal Coding method.
The Stand-alone Class Module supported method.
Two Stand-alone Class Modules supported the Option.

Part-I.

The review of existing Coding practices to assess the differences.

When designing a data entry or view form, it’s often helpful to highlight the active field with a distinct color or border, making it easy to spot or resume work from that point. Achieving this requires two event subroutines for each TextBox: a GotFocus event to apply the highlight and a LostFocus event to reset the field’s original formatting. For example, with 10 TextBoxes, we would need 10 GotFocus and 10 LostFocus subroutines—a total of 20. The usual approach is to copy and paste one pair of subroutines and then adjust the TextBox name in each subroutine header.

The Event Subroutines can call predefined Functions or write Code for Back Color, Border Color, and Border Width Property settings within all the Subroutines.

In either case, the time spent manually implementing this method for all TextBoxes, ComboBoxes, and ListBoxes on a form offers no flexibility or reusability. The effort cannot be leveraged for another form design or project, except for reusing the public function code stored in a standard module.

We’ll now explore a simple yet powerful technique using a standalone class module. This approach automatically handles any number of TextBoxes, ListBoxes, and ComboBoxes you add to a form—without requiring additional code. Even better, the same class module can be easily reused in other projects.

Even though the common Event Subroutines GotFocus() and LostFocus() for TextBox Color Attribute settings can be written in the stand-alone Class Module. However, it is better to write them in the Standard Module as two separate Functions, and it is easy to call them from the Form Module or from the stand-alone Class Module.

We’ll kick things off with the traditional Form Module approach so you can see firsthand how much time and repetitive effort it takes. Then, in the second part, we’ll switch gears and bring in a standalone class module. Here, only a small bit of code stays in the Form Module, while the heavy lifting is handled automatically behind the scenes. The real payoff comes when you see how effortless it is to add new TextBoxes to the form—no extra coding required. By the end, you’ll have a clear picture of just how much time and frustration this new method can save compared to the old way.

The Traditional Method.

Create a new form and place eight TextBoxes on it, arranged one below the other. Rename them to: SID, Description, Quantity, UnitPrice, TaxPcnt, TotalPrice, Discount, and NetPay.

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

Let’s begin with the traditional method. First, select the OnGotFocus event property of a TextBox and set its value to [Event Procedure]. Then, click the Build Code button to open the Form Module, where Access automatically creates an empty GotFocus event subroutine. Inside this subroutine, we’ll add a single line of VBA code to call the GFColor() function, passing the current Form object as the parameter. In the same way, we’ll create the LostFocus event subroutine to call the LFColor() function from the Standard Module, as shown below.

If we follow the same route to write Event Subroutines for 16 Event Procedures, you can imagine how much time it will take to complete all of them.

Private Sub SID_GotFocus()
    GFColor Me
End Sub

Private Sub SID_LostFocus()
    LFColor Me
End Sub

Next, copy and paste the GotFocus/LostFocus subroutine pair, then update the code to match each of the other TextBox controls on the form. Once you’ve completed this process for all eight text boxes, the Form Module will be filled with multiple event procedures, similar to the example shown in the image below.

Option Compare Database
Option Explicit

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

Private Sub SID_GotFocus()
    GFColor Me
End Sub

Private Sub SID_LostFocus()
    LFColor Me
End Sub

Private Sub Description_GotFocus()
    GFColor Me
End Sub

Private Sub Description_LostFocus()
    LFColor Me
End Sub

Private Sub Quantity_GotFocus()
    GFColor Me
End Sub

Private Sub Quantity_LostFocus()
    LFColor Me
End Sub

Private Sub UnitPrice_GotFocus()
    GFColor Me
End Sub

Private Sub UnitPrice_LostFocus()
    LFColor Me
End Sub

Private Sub TaxPcnt_GotFocus()
    GFColor Me
End Sub

Private Sub TaxPcnt_LostFocus()
    LFColor Me
End Sub

Private Sub TotalPrice_GotFocus()
    GFColor Me
End Sub

Private Sub TotalPrice_LostFocus()
    LFColor Me
End Sub

Private Sub Discount_GotFocus()
    GFColor Me
End Sub

Private Sub Discount_LostFocus()
    LFColor Me
End Sub

Private Sub NetPay_GotFocus()
    GFColor Me
End Sub

Private Sub NetPay_LostFocus()
    LFColor Me
End Sub

Each time you add new TextBox controls to the form, you’ll need to create a corresponding pair of GotFocus and LostFocus subroutines to apply the highlight feature to them as well. Once this step is complete, save the form with the name Form1_Normal.

Next, we’ll create two small, reusable functions that define the color attributes for highlighting controls. The first one will be used in the OnGotFocus event to highlight the active TextBox (and works equally well for ComboBoxes and ListBoxes). The second one LFColor() will reset the control’s colors when it loses focus. These functions will also play an important role in our standalone Class Module–based demos. If you’d like to customize the color scheme, you can easily update it directly within the functions.

The Color Attributes change the Function Code in the Standard Module.

Option Compare Database
Option Explicit

Dim save_BackColor As Variant
Dim save_BorderWidth As Variant
Dim save_BorderColor As Variant

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Highlighting Textboxes on Form
'Author: a.p.r. pillai
'Date  : 13/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Public Sub GFColor(ScrForm As Form)
'Active Field Highlight
   With ScrForm.ActiveControl
      save_BackColor = .BackColor
      save_BorderWidth = .BorderWidth
      save_BorderColor = .BorderColor
 
        .BackColor = &HD1FDFF
        .BorderWidth = 2
        .BorderColor = &H1914BA
    End With
End Sub

Public Sub LFColor(ScrForm As Form)
'Reset Active Field Highlight
    
   With ScrForm.ActiveControl
        .BackColor = save_BackColor
        .BorderWidth = save_BorderWidth
        .BorderColor = save_BorderColor
    End With
End Sub

Copy and paste the above VBA Function Code into a Standard Module and save it.

Now, open Form1_Normal in normal view and tab through the TextBoxes to see how it works. If you add two more textboxes to the Form, you need to add Event Subroutines manually in the Form Module for those two new textboxes.

Part-II.

The Stand-alone Class Module supported method.

First, let us create a stand-alone Class Module for the same Functions we wrote in the Standard Module. Let us see what difference it makes.

Open the VBA Editing Window (ALT+F11).
Select the Class Module Option from the Insert Menu to add a new Class Module.
Click on the new Class Module to select it, and click on the Properties button above to open the Properties Window in the left panel.
Change the name of the Class Module to myClass1.

Copy and paste the following VBA Code into the myClass1 Class Module.

Option Compare Database
Option Explicit

Public WithEvents Tx As TextBox
Public Fm As Form

Dim save_BackColor As Variant
Dim save_BorderWidth As Variant
Dim save_BorderColor As Variant

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Highlighting Textbox on Form
'Author: a.p.r. pillai
'Date  : 13/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Private Sub Tx_GotFocus() 'TextBox
    'GFColor Fm 'Call the standard Module Function
    
    With Tx
      save_BackColor = .BackColor
      save_BorderWidth = .BorderWidth
      save_BorderColor = .BorderColor
      
         .BackColor = &HD1FDFF
         .BorderWidth = 2
         .BorderColor = &H1914BA
    End With
End Sub

Private Sub Tx_LostFocus()
   'LFColor Fm 'Call the standard Module Function
    With Tx
        .BackColor = save_BackColor
        .BorderWidth = save_BorderWidth
        .BorderColor = save_BorderColor
    End With
End Sub

Let us review the above VBA Code to understand what they do for highlighting the TextBox on the Form.

The first declaration line Public WithEvents Tx As TextBox creates an instance of the Access TextBox class. For demonstration purposes, it is declared as Public though, in practice, it would normally be Private. This approach helps us avoid writing additional Property Procedures and keeps the myClass1 module simple. When the form is opened, the currently active TextBox instance is assigned to the Tx object in the myClass1 module. The WithEvents keyword enables the Tx instance to capture events triggered by the TextBox (in this case, the GotFocus and LostFocus events) and handle them through the corresponding event procedures coded within the myClass1 Class Module.

The next line declares a Form object with the instance name Fm. This will later be assigned to the Form1_myClass form object through the Fm property. (We’ll create this form once we finish the VBA code in the myClass1 Class Module.) Additionally, three Variant-type variables are declared to store the original color and property values of a TextBox before they are modified in the GotFocus event procedure. These stored values are then restored in the LostFocus event procedure, ensuring the control returns to its original appearance once it loses focus.

Next, in the GotFocus event subroutine, Private Sub Tx_GotFocus() the Tx object represents the currently active TextBox on the form. For example, if the active TextBox is named, Description this works just like having a procedure named Private Sub Description_GotFocus() in the form’s module.

The next (currently disabled) line is meant to call the GFColor() function we wrote earlier in the Standard Module. For this demo, however, we’ve chosen to keep it disabled and instead write the color-setting procedure directly here in the class module. In our earlier ‘normal’ demo, we called the function with the form object Me as a parameter. But since the built-in GotFocus and LostFocus event procedures don’t allow parameters, we’ll now assign the values directly to the Fm (Form) and Tx (TextBox) object instances. This way, we can easily reference these objects in our code while the form is running.

Next, a few lines of Code set the appropriate color attribute values to highlight the active TextBox Control.

Similarly, the Tx_LostFocus() Event Procedure resets the Color attributes when the TextBox activity is lost.

We are writing this code in the Design View of the myClass1 class module. These actions take effect only when the module is loaded into the computer’s memory. Unlike form modules, a class module cannot open and run directly. To make it active, we have two options available.

Open myClass1 Class Module in memory through the Form_Load() Event Procedure.
Seek the help of another stand-alone Class Module to do that. We will use the first option in this case. We will demonstrate the second option in Part III.

The Second Part Demo Form.

Create a Copy of the first Form, Form1_Normal, and rename it as Form1_myClass.
Let the TextBoxes remain as they are on the Form.
Open the Form in Design View and display its Form Module.

Copy the following VBA Code and paste it over the existing VBA Code in the Form Module.

Option Compare Database
Option Explicit

Private TBox As myClass1
Private Col As New Collection

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Highlighting Textbox on Form
'Author: a.p.r. pillai
'Date  : 13/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Private Sub Form_Load()
Set TBox = New myClass1
Call Class_InitEnd Sub

Private Sub Class_Init()
Dim ctl As Control

For Each ctl In Me.Controls
Select Case TypeName(ctl)
    Case "TextBox"
        Set TBox = New myClass1
        Set TBox.Fm = Me
        Set TBox.Tx = ctl
        
        TBox.Tx.OnGotFocus = "[Event Procedure]"
        TBox.Tx.OnLostFocus = "[Event Procedure]"
        
        Col.Add TBox
End Select
Next
End Sub

Private Sub form_Unload(cancel As Integer)
Set Col = Nothing
End Sub

Select Compile from the Debug Ribbon to ensure that no issues with the VBA Code.
Save the Form with the VBA Code.
If curiosity gets the better of you, go ahead and open the form in Normal View. Experiment with it first—we’ll review the code afterward to understand exactly how it works.
Press the Tab Key to move from one TextBox to the other.
Let’s try another quick experiment. Close the form and reopen it in Design View. Resize the Detail section so there’s enough space to duplicate the controls. Now, select the entire set of TextBoxes along with their labels, copy them, and paste them into the adjacent area once or twice. Don’t worry about the TextBox names for now.

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

Save the form and open it in Normal View. Use the Tab key to move from one TextBox to another. You’ll notice that the Tab order may not follow the expected sequence. Check whether the highlight moves from one TextBox to the other and reaches the new TextBox group's end.
Add a few TextBoxes manually anywhere on the Form and try them out to check whether the new TextBoxes also get highlighted without any changes to the Code..

If you are ready to continue, then let us review the Form Module VBA Code segment-wise.

Option Compare Database
Option Explicit

Private TBox As myClass1
Private Col As New Collection

In the global section of the Class Module, two objects are declared. The first one TBox is defined as a myClass1 object, which in turn represents a TextBox. This declaration sets the stage for bringing the standalone Class Module myClass1 into memory. However, the declaration by itself does not load the object into memory. To do that, we must instantiate the Class Object—that is, create an instance of it. In practice, we’ll need multiple instances of this object, one for each TextBox on the form. These instances are created within the Class_Init Subroutine.

The second declaration is a Collection Object with the Object name Col, and the usage of the New keyword in the declaration creates an Instance of the Collection object in memory. If you are not familiar with the Collection Object, please go through the following Links to learn the Basics of this Object:

The Form_Load() Event Subroutine.

Private Sub Form_Load()
    Call Class_Init
End Sub

The Class_Init statement calls the Subroutine to take over the rest of the serious work. The Class_Init part of the Code can be written within the Form_Load() Event Procedure, too. But, we will be moving the Class_Init() Subroutine Code into a different Class Module later.

Private Sub Class_Init()
Dim ctl As Control

For Each ctl In Me.Controls
Select Case TypeName(ctl)
    Case "TextBox"
        Set TBox = New myClass1
        Set TBox.Fm = Me
        Set TBox.Tx = ctl
        
        TBox.Tx.OnGotFocus = "[Event Procedure]"
        TBox.Tx.OnLostFocus = "[Event Procedure]"
        
        Col.Add TBox
        
        Set TBox = Nothing
End Select
Next
End Sub

Private Sub form_Unload(cancel As Integer)
  Set Col = Nothing
End Sub

First, we declare an Access.Control object variable named Ctl. Inside the For…Next loop, this variable is used to iterate through all the controls on the form, one by one. To filter out only the controls we are interested in, we use a Select Case…End Select block with multiple conditions. Within this block, the TypeName() function helps us check the type of control currently stored in Ctl. If the control is identified as a TextBox (Case "TextBox"), we then create a new instance of the myClass1 Class by assigning it to the TBox object using the statement Set TBox = New myClass1.

Note: We found the first TextBox object, probably with the name: SID, found in the Ctl Control. Since all the TextBoxes need the same Property settings, their names (like Quantity or UnitPrice) are not important here. We only need to make sure that it is a TextBox.

In the next step, we assign the Form object Me to the TBox.Fm property of the myClass1 Class, and the current TextBox control (Ctl) to the TBox.Tx property of the same Class instance.

The following two lines of code activate the GotFocus and LostFocus events for this TextBox by setting their event-enabling property values to "[Event Procedure]".

At this point, our first instance of the myClass1 Class is fully initialized with references to the Form and the first TextBox control. Once this setup is complete, the reference to the current TextBox is released from the TBox instance, making the Class ready to be reused for the next TextBox on the Form.

Think of it like plugging each TextBox into its own event-handler socket. Each time the loop encounters a new TextBox, it ‘plugs’ that control into a fresh instance of the Class, wiring it up with the necessary events. Once connected, the socket is free again to handle the next control in line.

The best way to manage the current instance of the myClass1 Class is to store it in the Collection object that we already instantiated in memory. The statement Col.Add TBox takes care of this. Using a Key value for Collection members is optional, and in this case, unnecessary.

Our goal is not to retrieve these Class instances later, but simply to keep them alive in memory so they can ‘listen’ for events fired by their assigned TextBox controls, capture those events, and run the corresponding Event Subroutines. When the Form closes, the Collection object (along with all its stored Class instances) is automatically cleared from memory.

The Collection Object itself was already instantiated in the global declaration area using the New keyword; all we need to do after saving the current instance is release its reference to the current TextBox with the statement Set TBox = Nothing. This frees the TBox variable to handle the next TextBox on the Form, while the saved instance in the Collection continues doing its job.

Think of it like parking cars in a garage: once you park one car safely inside (store the instance in the Collection), you step out and free yourself up (Set TBox = Nothing) to drive in the next car. Each stays parked (active in memory) until the entire garage (the Form) is closed.

The For…Next loop continues this process for every TextBox on the Form, one after another. Each TextBox is linked to a new instance of myClass1 its events are enabled, and the reference is stored in the Collection object. By the time the loop finishes, all TextBoxes on the Form are dynamically equipped with their GotFocus and LostFocus event procedures, ready to respond as soon as the Form is open.

Finally, in the Form_Unload() event, the Collection object itself is cleared from memory. This automatically removes all the stored class instances, ensuring a clean release of resources when the Form is closed.

Note: Normally, on a Form, we create physical instances of the TextBox class as properties of the Form, and their event procedures are written directly in the Form’s Class Module. In our new coding approach, however, we introduce a Wrapper Class Module that contains both a Form object (since every Access object is ultimately implemented as a stand-alone Class Module) and a TextBox object declared with the WithEvents keyword. This special declaration allows the Wrapper Class to capture the events fired by the assigned TextBox and handle them within its own event procedures.

For each TextBox on the Form, we create one instance of the Wrapper Class. Once the reference of a Form TextBox is assigned to the WithEvents TextBox in the Wrapper Class, it effectively mirrors the Form’s TextBox—listening to its events and executing the event procedures defined in the Wrapper Class.

An important point to note is that the Form-based events and the Wrapper Class–based events are independent, and both will execute if defined. For example, if you place a simple MsgBox "Hello World" in the GotFocus event of a TextBox in the Form Module, and also define an event procedure for the same TextBox in the Wrapper Class, the Form Module action will execute first, followed by the Wrapper Class action.

Save and Close the Form Form1_myClass.

Open the Form in Normal View, try moving the focus from one TextBox to the other, and check whether the TextBox highlighting works as before. You may remove/add TextBoxes and try again.

Part-III

First, we will prepare the myClass2 (Version 2 of myClass1) Class Module, then an intermediate Class Module to move the Class_Init Subroutine into it, and then create a new Demo Form. Creating separate Class Modules and Forms will be helpful to go back to check the earlier methods and to understand where the change took place.

Create a New Class Module with the name myClass2.

Copy and paste the following VBA Code into it and save the Class Module.

Option Compare Database
Option Explicit

Public WithEvents Tx As TextBox
Public WithEvents Lst As ListBox
Public WithEvents Cbo As ComboBox
Public Fm As Form

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Highlighting Textbox,ListBox & ComboBox on Form
'Author: a.p.r. pillai
'Date  : 13/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Private Sub Tx_GotFocus() 'TextBox
    GFColor fm 'Call Function from Standard Module
End Sub

Private Sub Tx_LostFocus()
    LFColor fm
End Sub

Private Sub Lst_GotFocus() 'ListBox
    GFColor fm
End Sub

Private Sub Lst_LostFocus()
    LFColor fm
End Sub

Private Sub cbo_GotFocus() 'Combo0Box
    GFColor fm
End Sub

Private Sub cbo_LostFocus()
    LFColor fm
End Sub

In the above Class Module, we have now added two more properties—Lst for the ListBox and Cbo for the ComboBox—alongside the earlier Tx (TextBox) and Fm (Form) properties in the global declaration area. All of these are declared as Public properties to avoid the need for writing separate property procedure pairs for each control type.

The event subroutines originally defined for the TextBox are duplicated for the ListBox and ComboBox as well, with distinct prefixes in their names. For the ListBox, the event subroutine names begin with Lst_ , for example: Private Sub Lst_GotFocus() and Private Sub Lst_LostFocus(). Similarly, for the ComboBox, the prefix Cbo_ is used, as in Private Sub Cbo_GotFocus() and Private Sub Cbo_LostFocus().

In these subroutines, rather than repeating explicit color-setting statements for each control type, we call the GFColor() and LFColor() functions to apply the highlight or reset effect. This keeps the code centralized, consistent, and easier to maintain across TextBoxes, ListBoxes, and ComboBoxes.

Relocating the Form Module Class_Init() Subroutine.

We will transfer the Class_Init() Subroutine into a separate stand-alone Class Module and free the space in the Form Module. We need only a few essential lines of Code on the Form Module. The Class_Init Subroutine will be run from the intermediate Class Module-based Subroutine Class_Init().

In this demo, with some changes in the Code, we will include the ComboBox and ListBox Controls for highlighting them too, if they are present in the Form.

Creating the Intermediate Class Module.

Create a new Class Module in the VBA Editing Window.
Rename it as myClass_Init.
Copy and paste the following VBA Code into the Module and save it:

Option Compare Database
Option Explicit

Private TBox As myClass2
Private Fom As Form
Private Col As New Collection

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Highlighting Textbox, ListBox and ComboBox on Form
'Author: a.p.r. pillai
'Date  : 10/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Public Property Get o_Fm() As Form
    Set o_Fm = Fom
End Property

Public Property Set o_Fm(ByRef vFrm As Form)
    Set Fom = vFrm
    
    Call Class_Init
End Property

'Class Init Subroutine transferred from Form Module
'
Private Sub Class_Init()
Dim ctl As Control

For Each ctl In Fom.Controls
Select Case TypeName(ctl)
    Case "TextBox"
        Set TBox = New myClass2 'Instantiate myClass2 Class
        Set TBox.Fm = Fom
        Set TBox.Tx = ctl
        
        TBox.Tx.OnGotFocus = "[Event Procedure]"
        TBox.Tx.OnLostFocus = "[Event Procedure]"
        
        Col.Add TBox
        Set TBox = Nothing       'Erase MyClass2 Class
        
    Case "ListBox"
        Set TBox = New myClass2  'Instantiate myClass2 Class
        Set TBox.Fm = Fom
        Set TBox.Lst = ctl
        
        TBox.Lst.OnGotFocus = "[Event Procedure]"
        TBox.Lst.OnLostFocus = "[Event Procedure]"
        
        Col.Add TBox
        
        Set TBox = Nothing     'Erase MyClass2 Class

        
    Case "ComboBox"
        Set TBox = New myClass2
        Set TBox.Fm = Fom
        Set TBox.Cbo = ctl
        
        TBox.Cbo.OnGotFocus = "[Event Procedure]"
        TBox.Cbo.OnLostFocus = "[Event Procedure]"
        
        Col.Add TBox
        
        Set TBox = Nothing
End Select
Next
End Sub

By looking at the above code, you can see the changes we made. In the global declaration area, we declared the myClass2 class with the object name TBox. In the earlier version myClass1 handled only the TextBox class, but in this new version myClass2 has been expanded to include the ListBox and ComboBox classes as additional properties.

The myClass2_Init Procedure scans the form using a For … Next loop, checking for the presence of ListBox and ComboBox controls in addition to TextBoxes. Whenever one of these controls is found, an instance of the myClass2 class is created and assigned to the TBox object.

Take a look at the following four lines of code, which show this process in action:

    Case "TextBox"
        Set TBox = New myClass2 'Instantiate myClass2 Class
        Set TBox.Fm = Fom
        Set TBox.Tx = ctl

When the Case "TextBox" condition is met, the control Ctl is identified as a TextBox. The myClass2 class is then instantiated into the TBox object. The Form object is assigned to the TBox.fm property, and the TextBox control Ctl is assigned to the TBox.Tx property. The GotFocus and LostFocus Events are enabled, and the TBox instance is added to the Collection object. Finally, the TextBox reference TBox is released with Set TBox = Nothing.

For ListBoxes and ComboBoxes, the process is similar. The control is assigned to the appropriate property—Set TBox.Lst = Ctl for ListBoxes or Set TBox.Cbo = Ctl for ComboBoxes. When these controls trigger the GotFocus or LostFocus events, their corresponding event subroutines are executed through the myClass2 instance stored in the Collection object.

The intermediary Class Module myClass2_Init is ready. Now we will create our Demo Part-III Form.

Prepare Form1_myClass2 Form.

Make a Copy of Form Form1_myClass and rename it as Form1_myClass2.
Open the new Form in Design View.

Right-click to open Large Image in New Tab/Window.
Delete some Textboxes that we copied and pasted to the right side of the Form.
Add two or three List Boxes and a few Combo Boxes on the right side of the Text Boxes.
Save the Form and display the Form VBA Module.
Copy and paste the following VBA Code in the Module, overwriting the existing Code in there.

Option Compare Database
Option Explicit

Private Clr As New myClass2_Init

Private Sub Form_Load()
    Set Clr.o_Fm = Me
End Sub

Private Sub Form_UnLoad()
    Set Clr = Nothing
End Sub

Review of the Form1_myClass2 Module Code.

In the Form Module, the necessary VBA code is required to load the myClass2_Init class module into memory. The class is declared with the object name Clr (short for Color), and when declared with the New keyword, memory is allocated for the myClass2_Init object.

Within the Form_Load() event, the Form object (Me) is assigned to the Clr.o_Frm property of the myClass2_Init class. Once the Form reference is passed, the Class_Init() A subroutine is called to scan the Form for TextBox, ListBox, and ComboBox controls. This process enables the required events and ensures that the corresponding event handlers are captured in the respective instances of these controls, which are stored in the Collection object in memory.

When the Form is closed, the Form_Unload() subroutine executes, and myClass2_Init The object is released from memory with the statement Set Clr = Nothing.

Loading myClass2, myClass2_Init Class Modules in Memory.

When the Form is opened, the other two stand-alone class modules must also be loaded into memory so that they remain synchronized and work together.

In the Form Module, the declaration Private Clr As New myClass2_Init instantiates the myClass2_Init class and loads it into memory.

myClass2_Init class The module holds a reference to the myClass2 class and creates instances of it. These instances enable events after assigning references to the Form, TextBox, ListBox, and ComboBox controls, and then storing them in the Collection object.

With these declarations, all three modules—the Form Module myClass2_Init and myClass2—along with the Collection object, are loaded into memory. When the Form is closed, all of them are automatically released from memory. An image posted in an earlier article on this topic is reproduced here to illustrate this process graphically.

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

So, all three Class Modules will reside in memory and work together to streamline the Form Module code within the stand-alone Class Module.

The Class_Terminate() Subroutine in the myClass2_Init Class Module runs automatically, much like the Form_Unload() Event Procedure in the Form Module.

The Form_Unload() Event Subroutine executes the statementSet Clr = Nothing , and it signals the closure of the myClass2_Init Class Object. This, in turn, triggers the shutdown of the myClass2 Class Module. Before it closes, the Class_Terminate() Subroutine clears the Collection object along with all its contents—the instances of TextBoxes, ListBoxes, and ComboBoxes.

We will continue from this point in our next episode. I hope you found this session insightful and engaging.

Download Demo Database

Streamline7.zip