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

Introduction.

After working through the last eight episodes of this series, you are likely familiar with this new coding approach and its benefits. By using the stand-alone Class Module–based method, we can achieve greater functionality while writing significantly less VBA code.

Episode Seven demonstrated how to highlight an entire array of TextBoxes on the Form. The OnGotFocus() Subroutine highlights the active TextBox, while the LostFocus() Subroutine resets it to its original state. Remarkably, this was achieved with just six lines of VBA code in the WrapTextBox Class. The public functions GFColor() and 'LFColor()' in the Standard Module are called from the GotFocus() and LostFocus() Event Subroutines in the WrapTextBox Class, respectively.

Private Sub txt_GotFocus()
    GFColor frm, Txt 'Field Highlight
End Sub

Private Sub txt_LostFocus()
    LFColor frm, Txt 'Field Highlight
End Sub

If you add more TextBoxes on the Form or delete existing ones, and not necessary to make any changes to the VBA Code.

Similarly, the following code snippet tracks all TextBox controls on the Employees form, detecting any attempts to modify their values. This monitoring applies to every TextBox on both the main Employees form and the Orders subform.

The OnDirty Event Subroutine.

Private Sub txt_Dirty(Cancel As Integer)
    If MsgBox("Editing the " & UCase(Txt.Name) _
    & ": Value? " & Txt.Value, vbYesNo + vbQuestion, _
    Txt.Name & " DIRTY()") = vbNo Then
    
        Cancel = True
    End If
End Sub

The code snippet above monitors all TextBoxes on the main form, Employees, and its subform, Orders, to prevent unintended changes. When a user attempts to modify a field, a warning message appears, requiring confirmation before the edit can proceed. If the user recognizes the action as a mistake, they can cancel the event and restore the field to its original value. This approach helps prevent accidental modifications and maintains data integrity.

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

The TextBoxes on the Orders SubForm are also under the surveillance of this Code.

The BeforeUpdate Event Subroutine.

When data is modified and the Enter key is pressed, the BeforeUpdate event procedure is triggered, safeguarding all TextBox controls on the forms. These events are handled collectively within the single subroutine txt_BeforeUpdate(), as demonstrated in the code below.

'Data Update Reconfirm to Save the Change
Private Sub txt_BeforeUpdate(Cancel As Integer)

    If MsgBox("Field Name: " & Txt.Name & vbCr & _
        "Original Value '" & UCase(Txt.OldValue) & "'" & _
        vbCr & "Change to: '" _
        & UCase(Txt.Value) & "'", vbYesNo + vbQuestion, _
        UCase(Txt.Name) & " BeforeUpdate()?") = vbNo Then
    
        Cancel = True
    End If


End Sub

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

Again, the User has to reconfirm to save the data in the Field.

Reusability of Streamlined VBA Code Writing.

The key advantage of this streamlined VBA coding approach is reusability. Instead of writing separate event-handling code for each TextBox within the form module, you only need to define it once in a TextBox wrapper class module. This class module serves as a template for managing BeforeUpdate() events across all TextBox controls.

Here's how it works:

Write the BeforeUpdate event handling code in the TextBox Wrapper Class Module.
Instantiate the Wrapper Class Module for each TextBox in the Main Form and Subform.
Assign the references of the Form and TextBox controls to their corresponding properties in the Wrapper Class Module.
Enable the required event procedures (e.g., BeforeUpdate) in the Wrapper Class EmpObject_Init Module.
Store the instantiated Wrapper Class EmpTextBox in memory through the Collection Object.

By adopting this approach, you can efficiently manage events for multiple TextBoxes across different forms without duplicating code. It improves code organization, eliminates redundancy, and simplifies future maintenance and updates to the event-handling logic.

So far, we have not worked with an example involving a main form and subform together—specifically, how to reference controls on the subform, enable their events, and streamline the code for both forms.

In this episode, the Employees table will serve as the record source for the main form Employees. The subform Orders, based on the Orders table, is designed as a tabular form.

We will explore how to reference subform controls (both TextBox and ComboBox) within the EmpObject_Init wrapper class, enable their required events, and store the wrapper class instances in a collection object, just as we did earlier.

The Control Wrapper Class (EmpTextBox) instances, once enabled with their required events, remain in memory as items within the collection object. They capture events triggered by the TextBoxes on both the form and subform and execute the corresponding event subroutines.

In the last episode, we created a new Wrapper Class EmpCmdButton for the Command Button Class Object and added it as the Property of the EmpObject_Init Class.

This time, we need a new wrapper class, EmpCombo, for the ComboBox control to capture events triggered by ComboBoxes on both the main form and subform.

In the footer section of the Employees form, there is a TextBox control used to search for an employee record by EmployeeID as the search key.

After the search operation, a Label control will briefly flash (label animation) to indicate whether the search was successful, and then disappear.

The important question is: where should we place the code for the data search and record lookup, as well as the label animation?

If you recall from the previous episode (Part Eight), we implemented a form-close countdown display within the WrapCmdButton class, specifically inside the cmd_Click() event subroutine. The countdown was shown in a Label control on the form. Similarly, a digital clock was also displayed in a Label control, but the code that managed the form’s TimerInterval settings was executed from the WrapObject_Init wrapper class.

Similarly, another common task is handled in the footer section of the form. An unbound TextBox, FindIDTextBox, is provided for searching by EmployeeID. When a value is entered, it is used to locate the corresponding record on the Employees form. The key question is: in which wrapper class should we place the VBA code for the search and find operations?

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

Besides that, there is a Label Control below the FindID TextBox to display the result of the search operation.

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

The EmployeeID will be entered into the unbound FindID TextBox, followed by pressing the Enter key. This action should trigger a search for the matching EmployeeID record on the Employees form. The code can be placed in the TextBox AfterUpdate() or LostFocus() event subroutine. However, the record lookup itself should be performed on the form’s RecordsetClone.

Alternatively, a Command Button can be added next to the unbound TextBox to initiate the search. In that case, the code can be written in the Command Button’s Click event subroutine within the EmpCmdButton wrapper class.

To provide feedback, a Label control in the form’s footer will display an animated (On/Off) status message indicating whether the search was successful or not. Events that involve the Form Object.

Each wrapper class we create—such as EmpTextBox, EmpCmdButton, EmpCombo, and others—will include the form object as a property, defined either with or without the WithEvents declaration.

When declared with WithEvents (e.g., Private WithEvents frm As Form), the wrapper class can directly capture the form’s events. Without WithEvents, the form property can still be used to access its controls, allowing you to read or write values and work with control properties as needed.

Regardless of whether the form property is actively used, a reference to the physical form is assigned to all wrapper class instances within the Class_Init() subroutine of the intermediate classes, such as WrapObject_Init or EmpObject_Init.

For this reason, we prefer to place the FindID TextBox AfterUpdate() event subroutine code within the EmpTextBox wrapper class module.

First, let us see how to incorporate the FindID unbound TextBox’s AfterUpdate() event-enabling code into the EmpObject_Init wrapper class, within the existing implementation. The complete VBA code is provided below. In addition, the ComboBox wrapper class property (named CBO) and its event-enabling code have been added to the Class_Init subroutine. All newly added code lines are highlighted in red.

The WrapObject_Init class was copied from the earlier project, renamed as EmpObject_Init, and extended with the new code lines marked in red. The same approach applies to other classes. This method allows you to quickly create wrapper classes, reuse existing code for different forms, and apply modifications where necessary.

Option Compare Database
Option Explicit

Private WithEvents iFrm As Access.Form
Private WithEvents oFrm As Form

Private iTxt As EmpTextBox
Private wcbo As EmpCombo
Private wcmd As EmpCmdButton
Private Coll As New Collection

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Main Form SubForm with Data
'Author: a.p.r. pillai
'Date  : 06/08/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

'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

'Events Enabling Subroutine
Private Sub Class_Init()
Dim ctl As Control

Const EP = "[Event Procedure]"
iFrm.OnTimer = EP  'Enable TimerInverval for Label Annimation

'Scan for TextBox, CommandButton & Combobox Controls on Employees Form
'and Enable the required Event Procedures
For Each ctl In iFrm.Controls 'Find TextBox, ComboBox & CommandButtons

    Select Case TypeName(ctl)
            Case "TextBox"
              Select Case ctl.Name
                Case "FindID" 'Employee ID Search TextBox
                    Set iTxt = New EmpTextBox 'Create Instance
                    Set iTxt.tx_Frm = iFrm 'Assign Form Object
                    Set iTxt.t_Txt = ctl   'TextBox
                    
                        iTxt.t_Txt.OnGotFocus = EP
                        iTxt.t_Txt.OnLostFocus = EP
                        iTxt.t_Txt.AfterUpdate = EP 'For EmployeeID Search
                        
                    Coll.Add iTxt 'Save EmpTextBox Class
                    Set iTxt = Nothing 'Erase temp Instance
                  GoTo CmdButton
              End Select

                    Set iTxt = New EmpTextBox '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
                         iTxt.t_Txt.OnDirty = EP      'To warn against Data Change
                         iTxt.t_Txt.BeforeUpdate = EP 'Reconfirm Data Change to update
                        
                    Coll.Add iTxt 'Save EmpTextBox Class in Collection Object
                    Set iTxt = Nothing 'Erase temp Instance
CmdButton:
        Case "CommandButton"
            Select Case ctl.Name
                Case "cmdClose"
                    Set wcmd = New EmpCmdButton
                    Set wcmd.cmd_Frm = iFrm
                    Set wcmd.c_cmd = ctl
                    
                    wcmd.c_cmd.OnClick = EP
                    
                    Coll.Add wcmd
                    Set wcmd = Nothing
            End Select
        
        Case "ComboBox"
                    Set wcbo = New EmpCombo
                    Set wcbo.cbo_Frm = iFrm
                    Set wcbo.c_cbo = ctl
                    
                   wcbo.c_cbo.OnGotFocus = EP
                   wcbo.c_cbo.OnLostFocus = EP
                    
                    Coll.Add wcbo
                    Set wcbo = Nothing
    End Select
Next

'Order Sub-Form
  Set oFrm = iFrm.Orders.Form

For Each ctl In oFrm.Controls 'Scan for SubForm Control and enable Events
Select Case TypeName(ctl)
    Case "TextBox"

            Set iTxt = New EmpTextBox 'Create Instance
            Set iTxt.tx_Frm = oFrm
            Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form

               iTxt.t_Txt.OnGotFocus = EP 'To highlght TextBox
               iTxt.t_Txt.OnLostFocus = EP 'Reset Highlight
               
               iTxt.t_Txt.OnDirty = EP
               iTxt.t_Txt.BeforeUpdate = EP
               
                Coll.Add iTxt 'Save EmpTextBox Class
            Set iTxt = Nothing 'Erase temp Instance

        Case "ComboBox"
             Set wcbo = New EmpCombo
             Set wcbo.cbo_Frm = oFrm
             Set wcbo.c_cbo = ctl

                 wcbo.c_cbo.OnGotFocus = EP 'To highlght ComboBox
                 wcbo.c_cbo.OnLostFocus = EP 'Reset Highlight
                     
                   Coll.Add wcbo
                Set wcbo = Nothing
    End Select
Next

End Sub

Private Sub Class_Terminate()
'Delete Collection Object contents
    Do While Coll.Count > 0
        Coll.Remove 1
    Loop
    
    Set iFrm = Nothing
    Set oFrm = Nothing
End Sub

The EmpTextBox Wrapper Class EmployeeID Search VBA Code is given below:

Option Compare Database
Option Explicit

Private WithEvents frm As Form
Private subFrm As Form

Private WithEvents Txt As TextBox 'TextBox object
Dim L As Integer
Dim ForeColor As Long
'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Main Form SubForm with Data
'Author: a.p.r. pillai
'Date  : 06/08/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
'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
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

'Evbent Subroutines
'===================

Private Sub txt_GotFocus()
    GFColor frm, Txt 'Field Highlight
    
    If Txt.Name = "FindID" Then
        Txt.Value = Null
    End If
End Sub

Private Sub txt_LostFocus()
    LFColor frm, Txt 'Field Highlight
End Sub

Private Sub txt_Dirty(Cancel As Integer)
    If MsgBox("Editing the " & UCase(Txt.Name) _
    & ": Value? " & Txt.Value, vbYesNo + vbQuestion, _
    Txt.Name & " DIRTY()") = vbNo Then
    
        Cancel = True
    End If
End Sub

'Data Update Reconfirm to Save the Change
Private Sub txt_BeforeUpdate(Cancel As Integer)
Dim msg As String

msg = "Field Name: " & Txt.Name & vbCr & _
        "Original Value '" & UCase(Txt.OldValue) & "'" & _
        vbCr & "Change to: '" & UCase(Txt.Value) & "'"
    
    If MsgBox(msg, vbYesNo + vbQuestion, _
        Txt.Name & "_BeforeUpdate()") = vbNo Then
        Cancel = True
    End If

End Sub

Private Sub txt_AfterUpdate()
Select Case Txt.Name
    Case "FindID"
        Dim rst As Recordset
        Dim ToFind As Integer
        Dim msg As String
        Dim max As Integer
        
        'max = DCount("*", "Employees")
        ToFind = Nz(frm!FindID, 0)
        If ToFind < 1 Then
            msg = "Employee ID: < 1 Invalid!"
            MsgBox msg, vbOK + vbCritical, Txt.Name & "_AfterUpdate()"
        Else
            Set rst = frm.RecordsetClone
            rst.FindFirst "EmployeeID=" & ToFind
            If Not rst.NoMatch Then
                frm.Bookmark = rst.Bookmark
        
                With frm.Result
                    .Caption = "*** Successful ***"
                    ForeColor = 16711680
                    .ForeColor = ForeColor
                End With
            Else
                With frm.Result
                    .Caption = "**Sorry, Not found!"
                    ForeColor = 255
                End With
            
            End If
            L = 0
            frm.TimerInterval = 250 'Enable Timer
        End If
    End Select
End Sub

'Label Animation Code.
Private Sub frm_Timer()
L = L + 1
Select Case L
    Case 1, 3, 5, 7, 9, 11, 13, 15, 17
        frm.Result.Visible = True
    Case 2, 4, 6, 8, 10, 12, 14, 16, 18
        frm.Result.Visible = False
    Case 19
       frm.Result.ForeColor = ForeColor
       frm.Result.Visible = False
       frm.TimerInterval = 0
End Select
End Sub

The EmpCombo is a new wrapper class for the ComboBox control. Its GotFocus() and LostFocus() event subroutine VBA code is shown below:

Option Compare Database
Option Explicit

Private cbofrm As Access.Form
Private WithEvents cbo As Access.ComboBox 'ComboBox object

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'ComboBox Wrapper Class
'Author: a.p.r. pillai
'Date  : 06/08/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

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

Public Property Set cbo_Frm(ByRef cfrm As Form)
    Set cbofrm = cfrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get c_cbo() As ComboBox
    Set c_cbo = cbo
End Property

Public Property Set c_cbo(ByRef pcbo As ComboBox)
    Set cbo = pcbo
End Property

'Event Subroutines Code
Private Sub cbo_GotFocus()
    GFColor cbofrm, cbo 'ComboBox highlight
End Sub

Private Sub cbo_LostFocus()
    LFColor cbofrm, cbo 'ComboBox highlight
End Sub

Your Assessment of the Streamlined Coding Procedure in the Standalone Class Module.

If you are an experienced Access VBA programmer, you may be able to assess the following points:

The amount of work and time required to write OnDirty() event subroutines for each TextBox and ComboBox control on the Employees form and the Orders subform using the traditional, manual VBA coding approach.
Similarly, the effort is needed to implement the AfterUpdate() event subroutines across both forms.

In practice, we typically write such code in the form module only for essential controls. Considering this, how do you feel about the new method in terms of ease of implementation and time savings compared to conventional coding practices? The focus here is not on quantifying the savings but on the reusability of code in standalone class modules and the potential reduction in development time for future projects.

Although this new approach—streamlining form module coding into standalone class modules—may be harder for beginners to fully evaluate, as an expert VBA programmer, what is your perspective on its effectiveness?

All episodes are written in a tutoring style, with title-related examples, so that non-experts can follow along and try them out. From your experience, how easy or difficult is it for non-experts to understand these concepts?

You are welcome to leave your feedback in the comment section. If you have a Gmail account, you can log in to post your comments. Thank you!

The upcoming demonstrations on this topic will cover the implementation of other Access class objects—such as Tab Control, ListBox, and Option Group—as well as examples of streamlining report module code.

The Demo Database is attached for Download.

Streamline9.zip

Streamlining Form Module Code in Standalone Class Module.

XeniOctober 9, 2023 at 6:30 PM
I'm reading all the articles about Streamlining, and I find them very interesting and useful. Every chapter goes deeper and it's explained step by step so it's easy to follow. Thanks and good job.
a.p.r. pillaiOctober 9, 2023 at 6:41 PM
Thank you Xeni.