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

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

LEARN MS-ACCESS TIPS AND TRICKS

Tuesday, July 4, 2023