July 2023 ~ LEARN MS-ACCESS TIPS AND TRICKS

Streamlining Form Module Code - Part Eight

a.p.r. pillaiJuly 28, 2023 No comments

Introduction.

Continued from "Streamlining Form Module Code - Part Six & Seven"

In the past, we utilized the TextBox Wrapper Class Array to store TextBox instances with Events enabled, allowing us to capture them in memory and execute the associated Event Subroutines. Although this Array method served its purpose, it required re-dimensioning the Array space every time and maintaining separate indices for various types of objects, such as Command Buttons, ComboBoxes, ListBoxes, and others. Fortunately, there is a more efficient option at our disposal - the Collection Object.

Earlier Article Links:

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

Usage of 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.

Using Data Table.

We run all kinds of validation checks on the data entered into the TextBox before accepting it. The entered data also can be part of any calculations and the result may be saved in another TextBox on the Form. These actions we could do on the Form from the Class Module, whether the TextBox's Control Source is assigned to a Table field or not. The only difference is that the data is not stored anywhere, but we could run the required data validation checks away from the Form Module and in 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 type into a single one.

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 there are more TextBoxes with the Exit() Event Subroutines, they can be written within this structured Subroutine. If we write them in the Form Module they will be within three different Subroutines in three different locations among other object's Subroutines in the Form Module.

The TextBox names (highlighted with Red Color along with other TextBoxes) may be enabled (RaiseEvent) with other required inbuilt Events, like GotFocus, LostFocus, and others and they also will be grouped as shown above, all GotFocus cases within a single GotFocus() Event Subroutine, and all LostFocus cases within one LostFocus() Event Subroutine.

In short, you need to write only one BeforeUpdate() Event Subroutine in the Standalone Class Module for 25 TextBoxes on the Form, if all of them are enabled with this Event. All TextBox's BeforeUpdate() Event Procedure can be inserted into this single Subroutine. This rule applies to all other Event Subroutines of TextBoxes. Everything will be in one Standalone TextBox Wrapper Class Module for Code maintenance and debugging.

All Object Types will have their own Standalone Wrapper Classes and their Event Subroutines are organized this way.

All these Event Procedures are executed one at a time as and when it is called for. They can be in the Form Module or they can be in the Wrapper Class Module in memory.

The TextBoxes (and other objects) on the Form are defined with the Keyword WithEvents individually when they are inserted in the Form. They are all spread out on the Form and we need to write Event Subroutines separately for each TextBox on the Form Module.

But, with the use of our Wrapper TextBox Class, we create only one Instance of the TextBox Class predefined with the keyword WithEvents. The TextBox Class declaration in the Wrapper Class is with an object name Txt like Private WithEvents Txt As TextBox. So all the TextBox-related Event Subroutine Name will be prefixed with the TextBox Object Name Txt like Private Sub Txt_GotFocus().

We will be creating separate instances of this Wrapper Class Module for each TextBox on the Form, and assigned with the References of the TextBoxes on the Form, through an automated procedure. This saves a lot of time we normally spend on writing and maintaining the Code for individual TextBoxes on the Form Module, during the Database development phase.

All GotFocus Events will be captured in this Subroutine and we need to check for their name in Select Case Txt.Name followed by Case "Quantity" to determine which TextBox on the Form fired the Event, accordingly we write the required Code under the TextBox Name. This kind of individual name checking is unnecessary if all the TextBoxes require the same VBA Code for a particular Event, like the TextBox highlight example we tried in the earlier Episode. Or selectively write Code for required TextBoxes in this way and Code that applies globally for all TextBoxes can be written outside this name-checking structure.

The Wrapper Class will have an additional declaration of a Form object, with the name like frm, mostly without the WithEvents qualification. It is necessary to read or write other control values on the Form, from the Wrapper Class in memory.

To keep the Wrapper Class Instances in memory we first chose the Wrapper Class Array option in the Class_Init() Subroutine in the WrapObject_Init2 intermediate Class Module. We need to create Wrapper Classes for other Access Controls like Command Buttons, Combo Boxes, and others. In that case, we need separate Indices based on the number of such controls on the Form. An alternative option is to use the Collection Object. The Collection object can hold any type of object or other type of Values, with or without the Item Key. Those who are not familiar with the Collection Object go through the following links:

COLLECTION OBJECT

The Class_Init() Subroutine Code in the WrapTextBox_Init Class is presented in the Title Topic Part-Six and the new version WrapTextBox_Init2 is implemented with the Collection Object.

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 both versions of the same VBA Code, those lines marked with red, in both versions, are used for the TextBox Object Instances.

The Z-Concept - Streamlining of Form Module Coding Logic of Class Objects at a Glance in the updated Diagram.

Form Sections Header, Detail, Footer Event handling.

There are several Form object Events. We will check how the Click and MouseMove Events are invoked from the Form Sections: Detail, Form Header, and Form Footer areas. I don't ask you to design a Form like the one shown at the top of this page. You can download the Demo database from the link given at the end of this page and try it out. Look for the changes we made in the WrapObject_Init2, WrapTextBox, in the Demo database. Compare the VBA Code with the Code you already have in the Demo Database downloaded from Episode Six.

Episode Number Eight introduces two new Wrapper Classes, WrapForm, and WrapCmdButton Classes. Form Event Subroutines and Events specific to Form Sections are run from the WrapForm Class Module.

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 Public Property procedures.

The Form Sections Click, MouseMove, and Double Click Events are enabled for the WrapForm Class Module Demo. The Mouse Move is enabled in the Detail Section and displays the X, and Y coordinate values in a label in the Detail Section, the Form Header is enabled with the Click Event, and the Footer Section is enabled with the Double-Click Event to run. Both Header and Footer Events display a message.

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 in one-second intervals it is not advisable to put this Code in the WrapForm Class Module. The Form Section-wise Events create three instances of the WrapForm Class in memory. If the Clock running Code is put in this Class Module the Code will run from all three instances of WrapForm Class in memory.

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 a simple Class that mostly handles the Click Event only. The Command Button and Form object declarations are there in the Global declaration area. The Get and Set Property Procedure pairs of both objects appear next.

Next, the Private Sub cmd_Click() Event Subroutine for the cmdClose Button Clicks Event is inserted. If any other Command Buttons are inserted on the Form their Click Event also can be put in this Event Subroutine by introducing the Select Case . . . End Select structure to identify the Command Button name to handle their Events separately.

Before closing the Form the Sub cfm_Unload() Event fires. The rest of the Code is a Form Closing countdown simulation that runs for 10 seconds before actually closing the Form. There is a Label Control kept hidden above the TextBoxes on the Form made visible now. The Countdown simulation text is displayed in the Label Control and when the count is equal to zero, closes the Form.

Note: If the Form_Unload() Event Subroutine is present in the Form Module then the WrapCmdButton Class-based cfm_Unload() Event Subroutine will not be able to execute. Because the Form Module-based Form_Unload() Event gets priority and closes the Form.

Demo Database Download Link:

Streamline8.zip

Streamlining Form Module Code in Standalone Class Module.

Streamlining Form Module Code - Part Seven

a.p.r. pillaiJuly 17, 2023 No comments

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

Streamlining Form Module Code in Standalone Class Module.

Streamlining Form Module Code - Part Six

a.p.r. pillaiJuly 04, 2023 No comments

Continued from the last Episode - Part Five.

Introduction.

Streamlining Form Module VBA Code in Standalone Class Module.

Earlier Episode Links:

After experimenting with the previous examples, you should now have a clear understanding of how the Events Subroutines of TextBox objects work on a Form and how they can be relocated into a standalone Class Module to run the Event Procedures for specific tasks on the Form.

Our earlier experiments were mainly focused on only one TextBox to learn the fundamentals, except the last Part-Five episode where we utilized three TextBoxes to demonstrate enabling events and capturing Raised Events in a standalone Class Module. It is important to note that all these actions were performed solely within the standalone Class Module, rather than within the Form Module.

I hope you recollect one important point that I mentioned earlier, how we are able to write Event Procedures on the Form Module itself. All Access Objects are designed in stand-alone Class Modules.

Note: If you would like to find out what they are, then open the Object Browser in the VBA Window and select Access in the <allLibraries> Control. You will find the list under the heading Classes, in the left pane. They are in alphabetical order, look for CommandButton, Form, Label, ListBox, TextBox, and others.

When we insert a TextBox on the Form Access declares the TextBox object Instance with the Keyword WithEvents and assigns a default unique name like Text0. The Name Property Value can be changed according to our requirement manually.

When a TextBox instance is declared with the keyword WithEvents, it allows TextBox to capture and handle its own inbuilt collection of events and execute the Event-related User-written Code within an Event Subroutine like AfterUpdate. In this case, the event subroutine can be written in the parent Form Module, with the TextBox name as the prefix for the subroutine name.

However, when creating a standalone Class Module, specifically for capturing events from the TextBox from the Form, we need to declare a TextBox object instance with the keyword WithEvents within the Class Module. Then assign the TextBox Control Reference from the Form to the TextBox Instance. This allows the Class Module to capture and handle the Events Raised by the TextBox. The event subroutine will then be written within the Standalone Class Module itself, rather than in the parent Form Module.

By declaring the TextBox instance with the keyword WithEvents within the Class Module, we establish the necessary connection between the TextBox control and the event handling logic defined within the Class Module. This enables us to encapsulate the event-handling functionality within the Class Module, providing a more modular and reusable approach for handling TextBox events.

In the earlier examples, we were able to capture events from TextBox controls by declaring them with the keyword WithEvents in the standalone Class Module. However, when there are several TextBoxes or other controls on the form, it becomes difficult to declare individual instances for each control and manage them in the Class Module.

A Flexible Approach for Class Module Coding.

To handle events from multiple controls efficiently, we can use a dynamic approach. Instead of declaring individual control instances in the Class Module, we can use arrays or collections to manage and iterate through the controls. This allows us to capture events from multiple controls without the need for individual declarations.

For example, if there are 25 TextBox controls on the form and need to handle their Events, we can declare a single TextBox Instance with the keyword WithEvents in a Standalone Class Module. Once it is done, create an Array of these TextBox Instances to hold references of several TextBox controls on the Form.

Similarly, other types of controls like Command Buttons or Combo Boxes, follow a similar approach to manage them through arrays or Collection Object Items and capture their events in the Standalone Class Module.

By using this dynamic approach, it is easy to handle events from a large number of controls on the form in a more scalable and manageable way. It allows us to centralize the event handling logic in the standalone Class Module and provides a more modular and reusable solution.

Re-organizing the stand-alone Class Module.

With all those points in mind, we need to reorganize the Class Module of our last episode and split it into two Parts.

The Event enabling Segment (Class_Init()) in one Class Module
In the second Class Module, a single TextBox Instance and a Form Object Instance Declaration, their Property Procedures, and their Event Subroutines will be placed.

The last episode's Class Module (with the name Class3_1) VBA Code is listed below for reference.

Option Compare Database
Option Explicit

Private frm As Form
Private WithEvents Qty As TextBox 'TextBox object Instance - 1
Private WithEvents UPrice As TextBox  'TextBox object Instance - 2
Private WithEvents Total As TextBox 'TextBox object Instance - 3

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

Public Property Set m_Frm(ByRef mfrm As Form)
    Set frm = mfrm

Call class_Init
End Property

'==================================================
Private Sub class_Init()
Dim ctl As Control
Const EP = "[Event Procedure]"

'Scan for TextBox Controls on Form
'and Enable the required Event Procedures
For Each ctl In frm.Controls
    Select Case ctl.Name
        Case "Quantity"
 'Assign Quantity TextBox on Form to 'Qty'
 'Private Property of Class_3_1 Class Module
 
          Set Qty = ctl
        Qty.Visible = True
    'Enable RaiseEvent
        Qty.OnExit = EP
        Qty.OnGotFocus = EP
        Qty.OnLostFocus = EP
        
        Case "UnitPrice"
 'Assign UnitPrice TextBox on Form to 'UPrice'
 'Private Property of Class_3_1 Class Module

    Set UPrice = ctl
    
    'Enable RaiseEvent
        UPrice.OnExit = EP
        UPrice.OnGotFocus = EP
        UPrice.OnLostFocus = EP
    

        Case "TotalPrice"
 'Assign TotalPrice TextBox on Form to 'Total'
 'Private Property of Class_3_1 Class Module
        
            Set Total = ctl
    
    'Enable RaiseEvent
            Total.OnGotFocus = EP
            Total.OnLostFocus = EP
    End Select
Next

End Sub

'==================================================

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

i = Nz(Qty.Value, 0)
If i < 1 Or i > 10 Then
    Msg = "Valid Value Range 1 - 10 Only."
    info = vbCritical
    Cancel = True
Else
    Msg = "Quantity: " & i & " Valid."
    info = vbInformation
End If

MsgBox Msg, vbOK + info, "Quatity_Exit()"

End Sub

Private Sub Qty_GotFocus()
    With Qty
        .backcolor = 65535 '&H20FFFF
        .forecolor = 0
    End With
End Sub

Private Sub Qty_LostFocus()
On Error Resume Next
    With Qty
        .backcolor = &HFFFFFF
        .forecolor = 0
    End With
End Sub

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

i = Nz(UPrice.Value, 0)
Msg = ""
If i <= 0 Then
    Msg = "Enter a Value greater than Zero!"
    info = vbCritical
    Cancel = True
End If

If Len(Msg) > 0 Then
    MsgBox Msg, vbOK + info, "UnitPrice_Exit()"
Else
    MsgBox "Unit Price: " & i, vbOK + vbInformation, "UPrice_Exit()"
End If

End Sub

Private Sub UPrice_GotFocus()
    With UPrice
        .backcolor = &H20FFFF
        .forecolor = 0
    End With
End Sub

Private Sub UPrice_LostFocus()
    With UPrice
        .backcolor = &HFFFFFF
        .forecolor = 0
    End With
End Sub


Private Sub Total_GotFocus()
    
    With Total
        .backcolor = &H20FFFF
        .forecolor = 0
    End With
    
    frm!TotalPrice = Qty * UPrice
    frm.TotalPrice.Locked = True
    
End Sub

Private Sub Total_LostFocus()
    With Total
        .backcolor = &HFFFFFF
        .forecolor = 0
    End With
End Sub

Private Sub Class_Terminate()
    Set Qty = Nothing
    Set UPrice = Nothing
    Set Total = Nothing
End Sub

The Class_init() Subroutine segment, the area marked by double-dash lines above and below, will be placed within a separate Class Module, with the name "WrapObjects_Init". The Class Module will undergo changes to handle more than one TextBox and other Objects when needed. This Class Module will be dedicated to the Event enabling (RaiseEvent) of all categories of Objects when needed. This Class Module plays an intermediary role in streamlining the Form Module Event Subroutine Code in standalone Class Modules.

The Code Segment above the first double line and Event Subroutines below the second double line will be placed in a separate Class Module with the name "WrapTextBox". This Class Module is dedicated to the TextBox Objects and for their Event Subroutines only.

We will create the WrapTextBox Class Module with a single TextBox object declaration and with a Form object Instance. We are familiar with the Form and TextBox Property Procedures too. This single Class Module Instances will serve all the TextBoxes on the Form and their Event Subroutines.

The WrapTextBox Class Module VBA Code.

Option Compare Database
Option Explicit

Private frm As Form 'Form Object declaration

'TextBox Object declaration with keyword WithEvents
Private WithEvents Txt As TextBox 'TextBox object

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

Public Property Set tx_Frm(ByRef pfrm As Form)
    Set frm = pfrm    'Assin Form
End Property


'TextBox Property GET/SET Procedures
Public Property Get t_Txt() As TextBox
    Set t_Txt = Txt
End Property

Public Property Set t_Txt(ByRef tTxt As TextBox)
    Set Txt = tTxt
End Property

'Event Subroutines Section
'-------------------------
Private Sub Txt_Exit(Cancel As Integer)
Dim i As Integer, Msg As String
Dim info As Integer

Select Case Txt.Name
    Case "Quantity"
        i = Nz(Txt.Value, 0)
        msg = ""
        If i < 1 Or i > 10 Then
            Msg = "Valid Value Range 1 - 10 Only."
            info = vbCritical
            MsgBox Msg, vbOK + info, "Quatity_Exit()"
            Cancel = True
        Else
            Msg = "Quantity: " & i & " Valid."
            info = vbInformation
            MsgBox Msg, vbOK + info, "Quatity_Exit()"
        End If
        
    Case "UnitPrice"
    
        i = Nz(Txt.Value, 0)
        Msg = ""
        If i <= 0 Then
            Msg = "Enter a Value greater than Zero!"
            info = vbCritical
            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

Private Sub txt_GotFocus()
        With Txt
            .backcolor = &H20FFFF
            .forecolor = 0
        End With

Select Case Txt.Name
    Case "TotalPrice"
        frm!TotalPrice = (frm!Quantity * frm!UnitPrice)
        frm.TotalPrice.Locked = True
End Select
End Sub

Private Sub txt_LostFocus()
Select Case Txt.Name
    Case "Quantity", "UnitPrice", "TotalPrice"
        With Txt
            .backcolor = &HFFFFFF
            .forecolor = 0
        End With
End Select
End Sub

Create a new Class Module and change its name to WrapTextBox. Copy and Paste the Code given above into the Class Module and save the file.

The naming convention you choose for your wrapper class, such as "WrapTextBox," is flexible and can be customized according to your preference and coding standards. The term "Wrap" in the class name is often used to indicate that the class serves as a wrapper or encapsulation for another class or object.

In this case, the "WrapTextBox" class serves as a wrapper for the Access.TextBox class and the Access.Form class objects. It encapsulates the functionality related to capturing and handling events fired from the TextBoxes on the form.

By declaring the TextBox instance with the WithEvents keyword in the WrapTextBox class, you establish a direct connection between the TextBox object on the form and the event handlers within the WrapTextBox class. This allows the WrapTextBox class to capture and execute the event subroutines associated with the TextBox on the Form.

The purpose of using a wrapper class like WrapTextBox is to centralize the event-handling logic for multiple TextBox objects and provide a modular and scalable approach to managing those events. The wrapper class acts as an intermediary between the form and the individual TextBox instances, making it easier to handle events and apply common functionality across multiple TextBoxes.

Ultimately, the specific name you choose for the wrapper class should align with your project's naming conventions and accurately represent its purpose as a wrapper for TextBox objects.

How to capture TextBox Events originating from the Form in a single instance of the TextBox object in the Wrapper Class? In last week's demo trial runs we declared three TextBox instances for Quantity, Unit Price, and Total Price. That is the new trick we are going to explore here.

We used a generic name Txt for the TextBox instance declaration in the WrapTextBox Class. The Property Procedure Code of TextBox and Form is transferred as it is from Class Module Class 3_1.

Since Txt is the TextBox object instance name, we must use this name as the Event Subroutine name prefix in all cases rather than using the TextBox Control names on the Form. Besides that, in earlier trial runs there were a total of eight Event Procedures: for Quantity and UnitPrice TextBoxes (OnExit, OnGotFocus, and OnLostFocus) three Event Procedures each, and two (OnGotFocus & OnLostFocus) for TotalPrice TextBox. But here there are only three physical Event Subroutines: Sub txt_Ext(), Sub txt_GotFocus(), and Sub txt_LostFocus().

The logic is very simple when the Exit() Event fires from any of the three TextBoxes the wrapper class calls the Sub txt_Exit() Event Subroutine. We already enabled the OnExit Event in Quantity and UnitPrice Texboxes on the Form. At this point, it is unknown exactly from which TextBox the Event is fired. But, to identify the name of the TextBox that fired the Event, the Select Case . . . Case . . . End Select structure runs a test, and based on that the corresponding Event Subroutine is executed as shown above.

Note: Assume that there are 15 TextBoxes on the Form you enabled all the Events of all the TextBoxes without checking their Names. Then you write the Event Subroutine of a few TextBox for their selected Events by checking their Names, only those Event Subroutines will be executed, other TextBoxes and enabled Events are ignored.

The same procedure can be followed to identify the Events OnGotFocus and OnLostFocus and write their Code in the same way. Since the LostFocus Event Procedure applies to all TextBox Controls to reset the background with the same color, no testing was necessary.

Create another Class Module with the name WrapObjects_Init. Copy and Paste the following Event-enabling Code into the Class Module and save it.

Option Compare Database
Option Explicit

Private iTxt As WrapTextBox 'Instance of WrapTextBox
Private TxtArray() As WrapTextBox 'TextBox Array
Private ifrm As Access.Form

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

Public Property Set m_Frm(ByRef mfrm As Form)
    Set ifrm = mfrm

Call class_Init
End Property

Private Sub class_Init()
Dim ctl As Control
Dim j As Integer

Const EP = "[Event Procedure]"

'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 "Quantity"
        
                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 "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
        
        Case "CommandButton"
            Select Case ctl.Name
                Case "cmdClose"
                    'Code
            End Select
    End Select
Next

End Sub

Private Sub Class_Terminate()
    Set ifrm = Nothing
    Erase TxtArray
End Sub

VBA Code Review.

Let's go through the code and identify the changes compared to last week's demo code, which focused on three different TextBoxes on the Form: Quantity, UnitPrice, and TotalPrice. As you already know, it's difficult to declare 25 separate instances of the TextBox class with 25 different names if there are that many TextBoxes requiring event handling on the Form. This approach would be cumbersome and not practical.

Instead, the solution lies in using a more flexible and scalable approach. Creating a standalone Class Module defines a custom TextBox Class that encapsulates the event handling logic. This Class can be instantiated multiple times, allowing us to handle events for any number of TextBoxes dynamically. The WrapTextBox Class is created just for that.

In the updated code, you'll find the implementation of the custom TextBox class (WrapTexBox Class) in the standalone Class Module. This class will contain the necessary event procedures and any additional functionality required. The WrapObjects_Init Module will then instantiate instances of this custom TextBox class for each TextBox control on the Form.

This approach enables to centralization of the event handling logic, making it more manageable and scalable, regardless of the number of TextBoxes present on the Form.

On the global declaration area of the Demo Form Class Module, declare an Instance of the WrapObjects_Init Class. When the Form is open the WrapObjects_Init Class will be Instantiated and will become active in Memory. When this Class is active it declares two Instances of the WrapTextBox Class and these TextBox Class Instances will also become active in memory. So when the Form is open all three Class Objects (Form Module, WrapObjects_Init Class, and WrapTextBox Class Objects(2 instances) are active in memory. The functional diagram of all three Class Objects is given below. It shows how they are related to each other and become active in memory at the same time.

Class Modules in Memory.

The Class_Init() Procedure segment, in our earlier Demo Class module, is now transformed into an independent Class Module with the name WrapObjects_Init. An instance of the WrapTextBox Class, with the name iTxt, is declared in the Global declaration area of this Module.

A second Instance of the WrapTextBox Class Module is declared as an Array object with an unspecified number of elements with the object name TxtArray() in there too.

The first one iTxt is a temporary instance to assign the TextBox's reference one by one from the Form, to access its Event Properties to enable the required Events and then pass it on to the TxtArray(). After that iTxt will be erased to make it ready to take the next TextBox reference from the Form.

The TxtArray() will be re-dimensioned dynamically, based on the number of Textboxes on the Form, their Events enabled before the TextBox is stored in the Array. In both of these global declarations, the keyword WithEvents is not used, because the TextBox object declaration in the global area of WrapTextBox Class is already qualified with the Keyword WithEvents to enable them to listen to the TextBox Events on the Form. Each TextBox Reference from the Form is assigned to different Instances of the WrapTextBox Class and required Events are enabled and stored in the TxtArray().

Access.TextBox Class Instances

After these two declarations as usual the Form Object declaration is also inserted in the Global declaration area with the Property name iFrm. The Form object that is passed from the Form Class Module through the Form Property Procedure m_Frm() is assigned to the iFrm Property. This will be passed on to the Frm Property declaration in the global area of WrapTextBox Class through the tx_Frm() Property Procedure.

It is important to get familiarized with the Code in this particular Class Module and to know what it does. The WrapObjects_Init Class Module along with the open Form and the TextBox Wrapper Class should work all together instantly when the Form is open. It should Enable the Events of the required Controls on the Form. The WrapTextBox Class should capture the Events when fired from the Form and execute the Event Subroutine. There will be only one Class_Init Wrapper Class for a Form but there can be several Object Wrapper Classes, one for each type of Control on the Form, like Command Buttons, Combo boxes, and others.

The Class_Init() Subroutine.

Let us check the Class_Init() Subroutine Code. There are two local variable declarations. The first one is a Control Variable Ctl to scan through the Form to find the Event enabling TextBoxes and the second one is an Integer Variable. The third one is a constant that holds the text "[Event Procedure]".

The For Each ctl In ifrm.Controls are the start of the For . . . Next Loop and in the next step we should check whether the first Control found in the Ctl Variable is the Type of control that we are looking for, the TextBox. The Select Case Typename(Ctl) does that. We are only interested in the TextBox Controls on the Form. So the Case "TextBox" does just that. If that is proven true, then we will further filter them by their names with an inner Select Case Ctl.Name check and take only the Quantity, UnitPrice, and TotalPrice TextBoxes on the Form. Let us bring that Code Segment here and take a close look at the VBA Code.

For Each ctl In ifrm.Controls

    Select Case TypeName(ctl)
        Case "TextBox"
            Select Case ctl.Name
              Case "Quantity"
        
                Set iTxt = New WrapTextBox 'Create an Instance of WrapTextBox Class
                Set iTxt.tx_Frm = ifrm     'Pass the Form Object to WrapTextBox Property
                Set iTxt.t_Txt = ctl       'Pass the TextBox Control 'Quantity'
            
                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 "UnitPrice"

When we detect the Quantity TextBox Control in Ctl then instantiate the WrapTextBox Object in the local iTxt object. Remember the TextBox Class is wrapped in the WrapTextBox Class. After creating an Instance of the WrapTextBox Class we can access its TextBox and Form Properties through the Public Property Procedures. Through these Property Procedures, assigns the Form object iFrm and Quantity TextBox Control Reference in Ctl, and they are passed on to their corresponding Properties in the WrapTextBox Class through its own Public Property Procedures:

            Select Case ctl.Name
                Case "Quantity"
        
                Set iTxt = New WrapTextBox 'Create an Instance of WrapTextBox Class
                Set iTxt.tx_Frm = ifrm     'Pass the Form Object to WrapTextBox Property
                Set iTxt.t_Txt = ctl       'Pass the TextBox Control 'Quantity' Reference

The next step in this Module is to enable the required Event Properties of the Quantity TextBox to fire the Events so that they can be captured in the WrapTextBox Class Instance and execute the related Event Subroutines for the required Task. We need to enable three Events OnExit, OnGotFocus, and OnLostFocus. The VBA Code is given below.

                iTxt.t_Txt.OnExit = EP     'Enable Event "[Event Procedure]"
                iTxt.t_Txt.OnGotFocus = EP             "
                iTxt.t_Txt.OnLostFocus = EP            "

Remember, we are working on the first instance of the WrapTextBox Class Object. In last week's trial run, we created the TextBox instances manually with the name Qty, UPrice, and Total for TextBox Controls Quantity, UnitPrice, and TotalPrice TextBox on the Form. Earlier we wrote the Event Procedure in the WrapObjects_Init Class (Class3_1 Class) Module itself. That has changed now, a separate WrapTextBox Class Module with TextBox and Form Object Properties takes care of the Event handling task. The TextBox Class Object Instance is declared with the WithEvents keyword to capture its inbuilt Events and Execute the Event Subroutine in the WrapTextBox Class.

The next few lines of Code are very important to keep watching and pay close attention to.

Our WrapTextBox instance was created in the WrapObject_Init Class and the active Form object instance is assigned to the Frm Property and also assigned to the Quantity TextBox instance. The required events for the Quantity TextBox on the Form are enabled. This particular WrapTextBox instance is created now for the Quantity TextBox only.

We will create two more instances of WrapTextBox Class for UnitPrice and TotalPrice TextBoxes on the Form using the same iTxt WrapTextBox.

Before that the current Instance iTxt must be saved in the TxtArray() in memory, then release the iTxt instance for reuse.

We declared a WrapTextBox Array with the name TxtArray(). The required number of Array elements is not known in advance, they will be redefined as and when the need arises. Now, the Quantity TextBox is current and enabled with the required Events OnExit, OnGotFocus, and OnLostFocus (but only one of these Events will be fired at one time) and is ready to save the instance after Redimensioning the Array by incrementing its array elements count J by 1. The Code segment is given below.

                J = J + 1
                ReDim Preserve TxtArray(1 To J)
                Set TxtArray(J) = iTxt          'Save it in Object Array
                Set iTxt = Nothing              'Erase temp Instance

The Array index Variable incremented by 1. The TextArray() of WrapTextBox is Redimensioned for 1 to 1 elements and the keyword Preserve ensures that the earlier elements, if any, with the data should be preserved. The statement Set TxtArray(j) = iTxt saves the WrapTextBox object instance iTxt in the first Array element of the TxtArray() object collection. The next statement Set iTxt = Nothing releases the memory occupied by this temporary WrapTextBox Instance for reuse.

When we instantiate an object in memory it occupies a certain area of the Computer's memory with a specific reference address. When we save the instance of this object in the Array, this address is copied into the array element and held there. This enables us to release the TextBox instance and reuse it for other TextBoxes on the Form. So the statement Set iTxt = Nothing releases it from holding on to the same memory Reference. Since, TxtArray is holding the WrapTextBox Instance reference so long as all the objects: the Form, WrapObjects_Init and WrapTextBox Classes are active, the saved Instance of Object in the Array will be safe and active too.

When we assign another TextBox Instance to the same temporary object iTxt it will look for free space in memory and will not overwrite earlier objects held in memory.

When the UnitPrice and TotalPrice Text Boxes are detected on the Form the same process is repeated and their corresponding Events will be enabled the Array is Redimensioned and the object instances are saved in the Array Elements.

When an enabled Event of Quantity or UnitPrice or TotalPrice fires from the Form it is captured in its corresponding Array element-based Event Subroutine and is executed.

Now, let us design our Demo Form.

Make a Copy of the last episode Form, with the name Form1_RaiseEvent_4, and rename it as frmMultiple_TextBoxes.
Open the frmMultiple_TextBoxes Form in Design View and change the Heading Label Caption to 'Events of Multiple Text Boxes on Form'.
Display the Form Module.

Copy and Paste the following VBA Code into the Form Module overwriting the existing Code:

Option Compare Database
Option Explicit

Private C As WrapObjects_Init

Private Sub Form_Load()
  Set C = New WrapObjects_Init
  Set C.m_Frm = Me
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set C = Nothing
End Sub

Select Compile from the Debug Menu to Compile the VBA Code to ensure that everything is ok with the Code. Save the Form with the Code.
Open the Form in Normal View.
Enter a Value 10 or less in the Quantity TextBox and press Enter Key. It will display a message displaying the entered value from the Quantity TextBox.
Enter some numerical value in the Unit Price TextBox and press Enter Key. It should display the entered value in MsgBox.

When the Total Value Textbox receives the Focus, the GotFocus Event fires and displays the result of the (Quantity * UnitPrice) Value in the TotalPrice TextBox.

Try entering a value larger than 10 in Quantity TextBox and see what happens.

Demo Database Download.

The Demo Database Download Link is given below:

Streamline6.zip