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

Introduction

Streamlining Form Module Code in Standalone Class Module.

Maybe next week we will continue from the earlier episode Part Six refine those new coding methods introduced there and bring in some further improvements in its implementation.

After going through the earlier Sessions of these exercises at least some of the readers may be in doubt about the power of these new Coding methods. This page is dedicated to them to experience the normal way of Coding on the Form Module and compare it with streamlined Coding that uses the standalone Class Module. We will split this new Coding Demo into three Parts, from the normal one to the advanced automation level.

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

Part-I.

The review of existing Coding practices to assess the differences.

When we design a Form for Data Entry/View we would like to see the selected field highlighted with some eye-catching color or border, (or both) to spot the active field quickly or to mark and resume work from there onwards. We need to run two Event Subroutines for a single textbox, the GotFocus Event Subroutine to highlight the field and the LostFocus one to reset the color to its earlier setting. If we have 10 TextBoxes on the Form we will write 10 GotFocus, LostFocus pairs of (total of 20) Event Subroutines on the Form Module. In this case, the quickest way is to copy-paste one pair of Event Subroutines and modify the TextBox name prefixes in the Subroutine Name.

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 implementing this method for all text boxes, Combo boxes, and List Boxes on the Form manually doesn't have any flexibility to save time for another Form design or for any other Project, except the Public Function Code in the Standard Module.

We will try a simple and very effective technique, with the use of a standalone Class Module-based solution that runs for any number of TextBox, ListBox, and ComboBoxes, which you may introduce on the Form any time, automatically without any additional Code writing for them. Besides that, you can transport the Class Module to 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.

The first part of this demo is shown as we normally do with the Form Module to get a rough idea of how much time it takes to do that. The second part will be implemented with the addition of a standalone class Module. Part of the VBA Coding is done on the Form Module with flexibility for the addition of TextBox controls on the Form. You will know the difference between the traditional and the new Access VBA Coding approaches.

The Traditional Method.

Create a new Form and add 8 TextBoxes to the Form, one below the other. Change the TextBox Names to SID, Description, Quantity, UnitPrice, TaxPcnt, TotalPrice, Discount, and NetPay.

First, we will try as we do normally by selecting the OnGotFocus Event Property and setting the Event enabling text "[Event Procedure]" value in the property, then opening the Form Module by clicking on the Code Build button. The Form Module opens up with the empty GotFocus Event Subroutine Stub where we write the VBA Code for highlighting the active TextBox. Here, we will write a single line of Code within the GotFocus Event Subroutine to call the GFColor() Function with the current Form object as the Parameter value. Similarly, write 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

Copy and Paste the above Subroutine Pairs and modify them for other TextBox controls on the Form. When completed the Form Module content will look like the image given 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

These subroutine pairs must be created for any addition of TextBoxes on the Form, to implement the highlight feature for them too. Save the Form with the name Form1_Normal.

We will write two small functions with the appropriate Color Attributes of the TextBox, also suitable for ComboBox and ListBox, the first one for highlighting the TextBox, to run on the OnGotFocus Event, with the Function name GFColor(). The second function LFColor() is for resetting the color of the TextBox on the LostFocus Event. These functions are useful in our standalone Class Module-based Demo runs too. If you prefer a different Color combination then it is easy to change in the Function.

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 on the Form you need to add Event Subroutines in the Form Module for those two 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, 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 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 declares an Instance of Access TextBox Class. The Public scope declaration is used here for demo purposes only (normally it will be Private), to avoid writing the Property Procedures and to keep the myClass1 Module Code simple. When the Form is open the active TextBox instance is assigned to the Tx Textbox object in myClass1 Module. The WithEvents qualification allows the Tx TextBox instance to capture the active TextBox generated Events (in this case the GotFocus and LostFocus Events) in the corresponding Event Procedures coded in the myClass1 Class Module.

The next line declares a Form object with the Instance name Fm. The Form1_myClass Form Object will be assigned to the Fm Form Property. We will create this Form after we are through with the VBA Code in myClass1 Class Module. Three Variant type Variables are declared to save the existing Color and Property values of the TextBox before changing them in the GotFocus Event Subroutine. These are restored in the LostFocus Event Subroutine.

Next, in the GotFocus Event Subroutine Private Sub Tx_GotFocus() name the Tx represents the active TextBox on the Form, like Private Sub Description_GotFocus(), if the active TextBox name is Description in the Form.

The next disabled line calls the GFColor() Function, written earlier in the Standard Module. We kept this line disabled and chose to write the Color Property setting Procedure here in this Module. In the first normal procedure demo, we called this Function with the Form object Me as the parameter. The predefined GotFocus and LostFocus Event Procedures don't have the parameter options to pass these values to the Subroutines. Instead, we will directly assign these values in the Fm and Tx object instances and we can refer to these objects on the Form in the Code.

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. When this module is loaded into the Computer's memory, then only these actions can take place. The Class Module cannot open on its own, like the Form Modules. For that, 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 you can't hold back your curiosity, then open the Form in Normal View. We will review the Code to know what they do, after your experiments.
Press the Tab Key to move from one TextBox to the other.
We will do another experiment here. Close the Form and open it again in Design View. Make the detail section of the Form wide enough to Copy and Paste the controls together (once or twice). Select the entire set of TextBoxes with their Child Labels, Copy the controls, and Paste them into the adjacent area. Don't bother about the TextBox Names.

Save the Form and then open it in Normal View, try moving from one TextBox to the other using the Tab Key. The Tab Index order may not be in the expected sequence.
Check whether the highlight moves from one TextBox to the other and reaches the new TextBox group's end.
You may add a few TextBoxes manually and try them out too.

If you are ready to proceed further, 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 Declaration Area, two object declarations are made. The first declaration is for our stand-alone Class Module: myClass1 with the Object name TBox. The name TBox is an indicator that it has something to do with the TextBox object. You can give any suitable name preferable to you.

The purpose of this declaration here is that it is the starting point to bring myClass1 stand-alone Class Module into the Memory. But this declaration alone will not load the Class Object in memory. We must Instantiate this Class Object (or create an Instance) to bring it into memory. In fact, we need several instances of this Object, (as many times as the number of TextBoxes on the Form) and they will be created in 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 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 declared an Access.Control object Variable with the name Ctl. In the For. . . Next Loop, it is used to iterate through the controls on the Form and to pick only the TextBox Control in Ctl.

To test and select only the required control, we are using the Select Case...Case...End Select statement with multiple conditions.

Then the TypeName() method is used to check the Type of Control we have in the Ctl object Variable. If it is the TextBox (Case "TextBox") Control then we Instantiate myClass1 Class in the TBox object in the statement Set TBox = New myClass1.

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

In the next step, the Form object Me is passed to the TBox.fm Property of myClass1 Class. The current TextBox object in Ctl is passed to the TBox.Tx Property of the myClass1 Class's first Instance.

The next two lines of Code enable the GotFocus and LostFocus Events of the TextBox instance, in myClass1 Class Instance in memory, by setting the Event enabling Property Value with the Text "[Event Procedure]".

So, we have the first Instance TBox of myClass1 Class Module loaded with the reference of the first Textbox from the Form ready to save in the computer's memory and release the TextBox Reference from the TBox instance of myClass1 Class to take the next TextBox on the Form.

The best option is to save the current instance of myClass1 Class in the Collection Object that we already Instantiated in memory, the next statement Col.Add TBox does that. Using a Key value is not mandatory for Collection Object members.

We are not planning to retrieve any of the Class Object instances saved in the Collection Object but to keep them in memory and want them to listen to the Events fired from the TextBox in the Form, capture it, and execute the Event Subroutines. When the Form is closed the Collection object with all the Class Object Instances in it will be removed from memory.

Remember, we already instantiated the Collection object in the global declaration area itself with the New Keyword.

Now, it is time to execute the statement Set TBox = Nothing to release it from holding the reference of the First TextBox of the Form.

The For . . . Next loop takes other Textboxes, one after the other, and repeats the same process till all the TextBoxes on the Form are enabled with the Event Procedures and their references are added into the Collection Object in memory. All these actions will happen dynamically when the Form is open.

In the Form_Unload() Event Subroutine the Collection Object is erased from Memory when the Form is closed.

Note: Normally on the Form we create physical instances of the TextBox Class as Property of the Form and write the Event Subroutine Code on the Form Class Module. In our new Coding approach, we create the Wrapper-Class Module enclosing a Form Class Object (all Access objects are designed using the stand-alone Class Module) and a TextBox Class that is redefined with the Keyword WithEvents, to capture the enabled Events in the Class Module to write their Event Subroutines in the Class Module.

We create one instance of the Wrapper Class for each TextBox on the Form. Because the TextBox Class in the Wrapper Class is redefined with the keyword WithEvents and capable of capturing the TextBox's inbuilt Events when fired. When the reference of the TextBox on the Form is assigned to the Wrapper Class TextBox it is as good as the copy of the TextBox on the Form and responds to Events of the TextBox on the Form and executes the Event Procedure written in the Wrapper Class.

If you write two different actions for a textbox, one action on the active Form Module and another one in the Wrapper Class-based Textbox both will be executed one after the other. Try writing a 'Hello World' in a MsgBox in the Form Module GotFocus Event Subroutine of any one TextBox and the Form-based action executes first, followed by the Wrapper Class-based instance action next.

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 in it 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

Added two more Properties, the ListBox with the object name Lst and ComboBox (Cbo) Properties in the above Class Module, in addition to the earlier TextBox (Tx) and Form (fm) Properties, in the Global declaration Area. All of them are declared as Public Properties, to avoid writing Property Procedure pairs for each of the declared Properties.

The Event Subroutines of TextBox are duplicated for ListBox and Comboboxes and check their Subroutine names. The GotFocus and LostFocus Subroutine name prefix, the ListBox is Lst_ in Private Sub Lst_GotFocus() and Lst_LostFocus() in Event Subroutine names.

Similarly cbo_ for ComboBox Subroutine names: Private Sub cbo_GotFocus() and cbo_LostFocus Event Procedure names.

Here, we prefer to call the GFColor() and LFColor() Functions to highlight or reset the selected control, rather than writing statements for individual Color attributes settings in all the TextBox, ListBox, and Combobox Event Subroutines.

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 will know what change we made in there. We declared the myClass2 Class in the global declaration area with the object name TBox. In the earlier version of this Class Module myClass1 had only the TextBox Class, but in the new Version myClass2 added the ListBox and ComboBox Classes also as its Properties.

The myClass2_Init Class scans the Form with the For . . . Next Loop to look for the presence of the ListBox and Combobox controls besides TextBoxes. If any of them is found then it instantiates the myClass2 Class object into the TBox Object. Take a look at the following four lines of Code:

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

The Case "TextBox" finds a TextBox in Ctl. In the next step, myClass2 Class instantiates into the TBox object. The Form object is assigned in the TBox.fm Property. The Ctl Control now has a TextBox control and this is assigned to the TBox.Tx Property. In the next two steps, the GotFocus and LostFocus Events are enabled, and the TBox myClass2 object instance is added to the Collection object. After that, the TextBox instance Reference in the TBox object is erased.

If you look at the fourth statement for the ListBox (Set TBox.Lst = Ctl) and ComboBox (Set TBox.cbo = Ctl) they are assigned to the ListBox Property with the name Lst and ComboBox Property cbo in the myClass2 Class Object. When these controls fire the GotFocus and LostFocus Events their corresponding Event Subroutine will be executed in the myClass2 Instance saved in Collection Object in memory.

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.
Delete some Textboxes which we copy-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, we need the above essential VBA Code to load the Class Module myClass2_Init into memory. The declaration of myClass2_Init Class Module with the object name Clr (short-form for Color) when declared with the keyword New allocates memory to myClass2_Init Class object.

In the Form_Load() Event Subroutine the Form object Me is assigned to the Clr.o_Frm Property Procedure of myClass2_Init Class Object. After getting the Form Module reference, we can call the Class_Init() Subroutine to scan for TextBox, ListBox, and ComboBoxes on the Form and enable their required Events and facilitate capturing the events in the respective TextBox, ComboBox & ListBox instances saved in the Collection Object in memory.

When the Form is closed the Form_Unload() Subroutine runs and myClass2_Init Class Object is removed from memory, by the statement Set Clr = Nothing.

Loading myClass2, myClass2_Init Class Modules in Memory.

When the Form is open, the other two stand-alone Class Modules must be loaded into the memory to remain in sync with each other and to work together.

In the Form Module the declaration Private Clr As New myClass2_Init Instantiates the myClass2_Init Class and loads it into the memory.

The myClass2_Init Class Module has the reference to the myClass2 Class and creates Instances of it to enable Events after assigning Form and TextBox Instance References, ListBox, and ComboBox instances to save them in the Collection.

With these declarations, all three Class Modules; Form Module, myClass_Init, and myClass2 along with the Collection Object are loaded into memory. When the Form is closed all of them will be removed from Memory. An image posted in an earlier Episode on this topic is reproduced here as how this is made possible and see it in graphical view.

So all three Class Modules will be in memory and work together to achieve the streamlining of the Form Module Coding in the Stand-alone Class Module.

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

In the Form_Unload() Event Subroutine the statement Set Clr = Nothing gives the signal to close the myClass2_Init Class Object.

This signals to close of the myClass2 Class Module. Before closing, the Class_Terminate() Subroutine runs and removes the Collection object with all its contents; the instances of Text Boxes, List Boxes, and Combo Boxes.

Next week, we will continue from our last episode and I hope you enjoyed what you saw here.

Download Demo Database

Streamline7.zip