June 2023 ~ LEARN MS-ACCESS TIPS AND TRICKS

Continued from the last Episode - Part Four.

Introduction.

Writing Form Module VBA Code in Standalone Class Module.

Earlier Episode Links:

Readers still need to go through the earlier Posts on this topic may please read them before continuing.

So far we worked with only one TextBox object on the Form and found how it can be programmed in several ways by writing VBA Code in a standalone Class Module rather than on the Form Module. We are familiar with the rules that govern defining Events, RaiseEvent to announce the Event, and declaring a TextBox object instance with the keyword WithEvents, for capturing the Events in the Class Module, raised on the TextBox control on the Form.

Let us see what it takes to deal with Events for more than one TextBox on the Form. This time we will introduce three TextBoxes on the Form for our experiments of Event handling in the standalone Class Module. Normally, we can expect 10 to 20 or more TextBoxes on a Form, but very unlikely that all of them need to be programmed with Event Procedures. But, if all of them are required we can do that too. There are several inbuilt Events in a TextBox but all of them may not be used, but several of them may be used depending on the requirements. We must be prepared to take that challenge too.

Let's start with three TextBoxes and see how we can handle Coding for a few Event Procedures. We will start with a crude approach first that works okay in the beginning, even though it is not an elegant solution, but is easy to understand.

Demo Form Design.

Create a new Form with the following design in your database:

Create a new Form and insert 3 unbound TextBoxes, one below the other, on the Detail Section of the Form.
Insert a Label control above the Textboxes and write the description as shown in the Form Image given above, in its Caption Property.
Select the first TextBox and display the Property Sheet. Change the Name Property Value to Quantity and change its Child-label caption to Quantity(1-10).
Select the second TextBox change its Name Property Value to UnitPrice and change the child-label Caption to Unit Price.
Change the Name Property value of the third TextBox as TotalPrice and enter Total Price in the child-label Caption.
Display the VBA Code Module of the Form.

Copy and Paste the following VBA Code into the Form Module, overwriting existing lines of Code, if any:

Option Compare Database
Option Explicit

Private C As New Class3_1 'create an instance of Class3_1 Class Object

Private Sub Form_Load()
  Set C.m_Frm = Me 'Pass current Form Object to C.m_Frm Property Procedure
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set C = Nothing 'Release the Class Object instance from memory
End Sub

Save the Form with the name Form1_RaiseEvent_4

We made some changes, in the earlier Global Declaration of Class Object C to create an Instance of the Class Module Class3_1 object. This is the short form (Private C As New Class3_1) of the following two-line statements we used in earlier cases:

Private C As Class3_1 'create an instance of Class3_1 Class Object

Private Sub Form_Load()
  Set C = New Class3_1
End Sub

The New keyword is used in the class object declaration itself to create an instance of the Class Object in memory. The current Form Object Me is passed to the SET Property Procedure C.m_Frm() as a Parameter in the Form_Load() Event Procedure. You will see the m_Frm() Property Procedure when we create the Class3_1 Class Module Code. The Class Object declaration in the Global area alone will not load the Class Object into the Memory. We instantiated the class object with the keyword New to load an instance of the class object into memory. When we open a Form its Class Module is also loaded into the memory, when the Form's Has Module Property is set to Yes.

The Form_Unload() Event Procedure removes the Class Object Instance C from memory to release the memory for other uses, when the Form is closed.

The New Class Module: Class3_1.

We will find a better way to name our Class Modules so that by looking at them, we can easily guess where it belongs, or what category of object it is. But, we are on a learning curve now, and understanding the new concept is more important than such cosmetic changes.

Create a new Class Module, display its Property Sheet (Select Properties Window from the View Menu), and change its Name Property Value to Class3_1.

Copy and Paste the following VBA Code into the Class Module and Save the Module:

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 Object'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

'After receiving Form Object, scan for TextBoxes on Form
'and Enable them with required Events.
'
   Call class_Init

End Property
'---------------------------------------------------------------------

'Scan for TextBox Controls on Form
'and Enable the required Events
'
Private Sub class_Init()
Dim ctl As Control
Const EP = "[Event Procedure]"

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
        
    '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

'---------------------------------------------------------------------

'Qty.Exit() Qty.GotFocus() & Qty.LostFocus() Event Procedures

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 'I-bar to stay in the TextBox
Else
    Msg = "Quantity: " & i & " Valid."
    info = vbInformation
End If

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

End Sub

Private Sub Qty_GotFocus()
    With Qty
        .BackColor = &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

'---------------------------------------------------------------------
'UPrice.Exit() Qty.GotFocus() & UPrice.LostFocus() Event Procedures

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 'I-bar to stay in the TextBox
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

'---------------------------------------------------------------------
'Total.GotFocus() & Total.LostFocus() Event Procedures

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

'---------------------------------------------------------------------

You can ignore/delete the dashed lines I put in between segments for better readability on this Page.

Segment-wise Review of VBA Code.

Declaration of Objects and Get/Set Property Procedures of Form Object.

Let us review the Class Module Code segment-wise to understand what they do. This VBA Code structure will undergo some major changes altogether later to make them easy to manage.

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 Object'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

'After receiving Form Object, scan for TextBoxes on Form
'and Enable them with required Event Procedures.
'
   Call class_Init

End Property
'---------------------------------------------------------------------

In the Global declaration area, a Form object Frm is declared with Private Scope. That means this Form object cannot be accessed directly from outside the Class Module except through Property Procedures declared with Public Scope. We can pass the current Form Object Me to the SET m_Frm() Property Procedure through the Form_Load() Event Procedure. When we get the current Form instance in the Frm object in the Class3_1 Class Module we get access to all Controls or get their values from the Form. We can capture the Events fired (RaiseEvent) from the Controls on the Form, update values in the Textbox or access other properties of the Controls on the Form.

We will create a Form Object in every standalone Class Module we create in the demo of the 'Streamlining the Form Module VBA Code' procedure. You can track how the current active Form Object is passed from the Form_Load() Event Procedure to the form object Instance in the standalone Class Module, through the Set Property Procedure parameter. You may keep an eye on its usage in the standalone Class Module (Class3_1) and in the Event Procedure Subroutines.

The next three TextBox Object declarations: Qty, UPrice, and Total will be assigned with the three TextBox object References on the Form Quantity, UnitPrice, and TotalPrice respectively.

Next, the Set Property Procedure will receive the Form Object into the Frm Property, through the Form_Load() Event Procedure through the m_frm Property Procedure. The Get Property Procedure will stand by for servicing the external request for the Form object when received.

The Call Class_Init() statement calls the Subroutine Class_Init().

The Class_Init() subroutine scans the Form for TextBox Objects, and when found their References are assigned to the three TextBox Control Instances with the names, Qty, UPrice, and Total we created in the Global declaration area.

Scan the Form to find the Quantity, UnitPrice, and TotalPrice TextBoxes.

'Scan for TextBox Controls on Form
'and Enable the required Event Procedures
'
Private Sub class_Init()
Dim ctl As Control
Const EP = "[Event Procedure]"

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
        
    '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

'---------------------------------------------------------------------

Let us check the Code Segmentwise. We declared one general purpose Control Object Ctl and a Constant EP with the text "[Event Procedure]". In the next step the For Each Ctl In Frm.Controls . . . Next check through the controls on the Form one by one and read in the Ctl Control Variable. We are only interested in three TextBoxes with the name Quantity, UnitPrice, and TotalPrice on the Form. Other TextBoxes, if any, or any other controls on the Form are ignored. So, we need the Select Case Ctl.Name ... Case ... End Select Structure to test the name the Ctl Control represents in each cycle of the For . . . Next Loop to find the required one.

When the Case "Quantity" is found the Ctl-object reference is assigned to the Qty TextBox Instance (Set Qty = Ctl), declared in the global declaration area. The next three statements are enabled, by setting the constant EP ([Event Procedure]) text in all the three Event Procedures (OnExit, OnGotFocus, and OnLostFocus) of the Quantity Textbox on the Form.

This time we took the OnExit Event instead of AfterUpdate. The OnExit Event has some added advantages, if the value entered into the TextBox is not within the allowed range we can keep the I-bar in the same TextBox, after displaying an Error message, by executing the line Cancel = True, till the user enters a valid entry.

The UnitPrice Textbox object is assigned to the UPrice TextBox object instance and enables the same set of Event Procedures.

The TotalPrice TextBox Reference is assigned to the Total TextBox object instance and enables the OnGotFocus and OnLostFocus Events only.

The Quantity TextBox Events

The Qty_Exit, Qty_Gotfocus, and Qty_LostFocus Event Procedures.

'---------------------------------------------------------------------

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 'I-bar to stay in the TextBox
Else
    Msg = "Quantity: " & i & " Valid."
    info = vbInformation
End If

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

End Sub

Private Sub Qty_GotFocus()
    With Qty
        .BackColor = &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

'---------------------------------------------------------------------

In the Qty_Exit() Subroutine three Variables are declared. The i = Nz(Qty.Value,0) statement reads the Quantity value from the Qty TextBox object instance. In the next step, it checks whether the received value is within the valid range of 1 to 10, if not the Msg and Info String Variables are assigned with Message Text for the MsgBox. The Cancel = True ensures that the I-Bar stays in the Quantity TextBox till a valid value is entered.

Next, the GotFocus Event Procedure changes the background Color and LostFocus resets the TextBox Color to its default value.

The UnitPrice TextBox goes through the same process.

 '---------------------------------------------------------------------

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 'I-bar to stay in the TextBox
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

'---------------------------------------------------------------------

The TotalPrice TextBox Event Procedures.

'---------------------------------------------------------------------

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

The TotalPrice TextBox is enabled with the GotFocus and LostFocus Events only. The OnGotFocus Event Procedure will change the background Color. Next, the calculated value Qty * UPrice is updated in the TotalPrice TextBox. Immediately after updating the value the TextBox is locked by executing the statement frm.TotalPrice.Locked = True. The OnLostFocus Event resets to its earlier background Color.

We could write the full Event Procedure Code in the standalone Class Module, except the Form_Load() Event Procedure, which works the way we planned. This is the first time we are implementing Event Procedure Code for more than one Textbox in the standalone Class Module and it works the way we wanted it now. With only three TextBoxes and three Events for each TextBox.

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

The Sub Class_Terminate () is a very special Subroutine that executes automatically when the Class Module Class3_1 is in the closing phase and is used for clearing the memory occupied by the Objects.

There is another Subroutine with the name Sub Class_Initialize(), if present in the Class Module, that also executes automatically, when the Class Module is loaded into memory, to instantiate some other Class Object or any other object like Collection, Dictionary in memory.

But, in totality it is crowded with Code in the Class3_1 Class Module, defining TextBox instances, Enabling Event Procedures, writing Event Procedure codes, and all these actions stuffed into a single Standalone Class Module. We need to be better organized for handling more than three Instances of the TextBoxes with ease.

For the Quantity TextBox, there were three Events OnExit, OnGotFocus, and OnLostFocus. All three Event Procedures are written in the Class3_1 Module. The UnitPrice TextBox has 3 Events, and TotalPrice 2 Events. Total 8 separate Event Procedures as we normally write in the Form Class Module. Okay, all of them are taken out from the Form Module and put in the Standalone class module. But, if we have 15 Textboxes on the Form having 3 Event Procedures each then what we will do? We will write all 45 Event Procedures on the Form Module, or we will transfer them to the standalone Class Module.

We must find a better way to organize the VBA Code of several other Control types on the Form. We are now considering only the TextBox objects. What about other Controls, like Command Buttons, combo boxes, and others?

But First, we will concentrate on refining our current Coding approach for our TextBox Objects.

We will make a detailed study of this aspect next week. When we are familiar with that procedure then we can handle other controls on the Form very easily.

Demo Database Download Link:

Streamline5.zip

Streamlining Form Module Code in Standalone Class Module.

Option Compare Database Option Explicit 'Decare an Object Variable C as Type Class1 Private C As Class1 Private Sub Form_Load() 'Instantiate Class1 Class Object Set C = New Class1 'Assign Quantity TextBox Object to C.Txt Property Set C.Txt = Me.Quantity 'Enable Event Procedures of Quantity TextBox 'This method replaces the empty 'Event Procedure stub used earlier. Me.Quantity.AfterUpdate = "[Event Procedure]" Me.Quantity.OnGotFocus = "[Event Procedure]" Me.Quantity.OnLostFocus = "[Event Procedure]" End Sub Private Sub Form_Unload(Cancel As Integer) 'Release Class1 Object instance C from memory Set C = Nothing End Sub

Private C As Class2 Private Sub Form_Load() Set C = New Class2 Set C.m_Frm = Me 'What is m_Frm member in Class2 Module? End Sub Private Sub Form_Unload(Cancel As Integer) Set C = Nothing End Sub

Option Compare Database Option Explicit Private Frm As Form Private WithEvents Txt As TextBox Public Property Get m_Frm() As Form 'GET Property Procedure Set m_Frm = Frm End Property Public Property Set m_Frm(ByVal mFrm As Form) 'SET Property Procedure If Left(TypeName(mFrm), 4) = "Form" Then Set Frm = mFrm Else MsgBox "Invalid Object " & mFrm & " passed as Form!" Exit Property End If Call Class_Init 'Run Class_Init() Subroutine End Property Private Sub Class_Init() Set Txt = frm.Quantity Txt.AfterUpdate = "[Event Procedure]" Txt.OnGotFocus = "[Event Procedure]" Txt.OnLostFocus = "[Event Procedure]" End Sub Private Sub txt_AfterUpdate() Dim i As Integer, msg As String Dim info As Integer i = Nz(Txt.Value, 0) If i < 1 Or i > 10 Then msg = "Valid Value Range 1 - 10 Only." info = vbCritical Else msg = "Quantity: " & i & " Valid." info = vbInformation End If MsgBox msg, vbOK + info, "txt_AfterUpdate()" End Sub Private Sub txt_GotFocus() With Txt .backcolor = &H20FFFF .forecolor = 0 End With End Sub Private Sub txt_LostFocus() With Txt .backcolor = &HFFFFFF .forecolor = 0 End With End Sub

Public Property Get m_Frm() As Form Set m_Frm = Frm End Property Public Property Set m_Frm(ByRef mFrm As Form) If Left(TypeName(mFrm), 4) = "Form" Then Set Frm = mFrm Else MsgBox "Invalid Object " & mFrm & " passed to Form" Exit Property End If Call Class_Init 'Run Class_Init() Subroutine End Property

Private Sub txt_AfterUpdate() Dim i As Integer, msg As String Dim info As Integer i = Nz(Txt.Value, 0) If i < 1 Or i > 10 Then msg = "Valid Value Range 1 - 10 Only." info = vbCritical Else msg = "Quantity: " & i & " Valid." info = vbInformation End If MsgBox msg, vbOK + info, "txt_AfterUpdate()" End Sub Private Sub txt_GotFocus() With Txt .backcolor = &H20FFFF .forecolor = 0 End With End Sub Private Sub txt_LostFocus() With Txt .backcolor = &HFFFFFF .forecolor = 0 End With End Sub