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

Introduction.

After going through the last eight episodes of the Title Topic I assume that you are familiar with this new coding procedure and realized its advantages. By implementing this new stand-alone Class Module-based coding approach, we can achieve more functionality with significantly less VBA code.

Episode number Seven demonstrated how we could highlight the entire Array of TextBoxes on the Form, by running the OnGotFocus() Subroutine and resetting the highlight to the earlier state in the LostFocus() Event Subroutines, with only the following six lines of VBA Code in the WrapTextBox Class. The GFColor() and LFColor() public functions in the Standard Module are called from the GotFocus() and LostFocus() TextBox 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 small code snippet monitors all the TextBox controls on the Employees Form to monitor any attempt to modify the value in any one of the TextBoxes on the Form. This applies to all TextBoxes on the main form Employees and the SubForm Orders as well.

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 effectively monitors all TextBoxes within the Main Form "Employees" and the Sub-Form "Orders" to prevent inadvertent changes. When the user attempts to edit a field, a warning message is displayed, and the user must confirm his/her intention to change the field value before they can proceed with editing. If the user realizes that it was a mistake then they have the option to cancel the Event and revert the field to its original value. This implementation helps to prevent accidental modifications and ensures data integrity.

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

The BeforeUpdate Event Subroutine.

When you modify data and press the Enter key, the BeforeUpdate event procedure, responsible for safeguarding the entire array of TextBoxes on the forms, will be triggered. All these events are then captured within the same Sub txt_BeforeUpdate() event subroutine, as shown in the code provided 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

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 Code writing is its reusability. Instead of writing the event handling code for each TextBox individually in the Form Module, you can write it just once in a TextBox Wrapper Class Module. This Class Module acts as a template for handling BeforeUpdate() events for 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 following this approach, you can efficiently manage and handle events for multiple TextBoxes on different forms without duplicating code. It promotes code organization, reduces redundancy, and makes it easier to maintain and update the event-handling logic in the future.

So far we have not tried any example with the Main Form with a SubForm setting, how to reference the Controls on the Subform, enable their Events, and how to streamline the Code for both Forms.

In this episode, we will use the Employees Table as the record source for the Employees' Main Form. The sub-Form Orders has the Orders Table as a Record Source and is designed as a Tabular Form.

We will learn how to address the Subform Controls (TextBox and ComboBox) in the EmpObject_Init Wrapper Class. Enable their required Events and save the Wrapper Class instances into the Collection Object, as we did earlier.

The Control Wrapper Class (EmpTextBox) instances, enabled with their required Events stay in memory as the Collection Object Items and capture the Events fired from the TextBoxes on the Form and SubForm and execute the 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 track the Events fired from the ComboBoxes from the Main Form and Subform.

On the Employee Form's Footer Area, there is a TextBox Control to Search and Find the Employee Record by using EmployeeID as the search Key.

After the search operation, it will flash a Label Control (Label Animation) with a message a few times to announce whether the Search was successful or not and then disappear.

The big question here is, where we will write the Code for the Data Search and Find operations and run the Label Animations.

If you recollect the last episode (Part Eight) we used a Form-Close Countdown display on the Form running in the WrapCmdButton Class, within the Sub cmd_Click() Event Subroutine. The Countdown display was in a Label control on the Form. The digital clock was running in a Label Control on the Form, but the Form TimerInterval settings Code was running from the WrapObject_Init Wrapper Class.

Similarly, there is another common task to run from the Form footer area. There is an EmployeeID Search and Find unbound TextBox at the Footer of the Form. The EmployeeID value will be entered into the FindIDTextBox control, to find the record on the Employees Form. The question is in which Wrapper Class we will write the VBA Code for the search and find operations?

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

The EmployeeID will be entered into the FindID unbound TextBox and will press the Enter Key. This action should start searching for the EmployeeID in the Employees Form. The Code can run in the TextBox AfterUpdate() or LostFocus() Event Subroutine. However, the search for the record should be done on the RecordsetClone of the Form Object. Another option is to add a Command Button next to the Unbound TextBox to Click to start the search for the Record. In that case, we can write the Code in the Command Button Click Event Subroutine in Wrapper Class EmpCmdButton. We have a Label Control on the Footer of the Form running an animated display (On/Off) with the status of search success or failure.

Events that involve the Form Object.

Every Wrapper Class that we create, like EmpTextBox, EmpCmdButton, EmpCombo, and other upcoming ones will have a Form Class as Property, with or without the WithEvents declaration.

If it is with the WithEvents declaration (Private WithEvents frm as Form) then we can directly capture the Form Events in that Wrapper Class. Otherwise, it can be used for accessing any control on the Form to read/write values or to access the Control properties, when the need arises.

Whether we are using the Form Property for any purpose or not we are assigning the physical Form's reference to all the Wrapper Class Instance created in the intermediate WrapObject_Init or EmpObject_Init Class's Class_Init() Subroutine.

So, here we prefer to write the FindID AfterUpdate() Event Subroutine Code in the EmpTextBox Wrapper Class Module.

First, let us see how we can incorporate the FindID unbound TextBox's AfterUpdate() Event enabling Code in the EmpObject_Init Wrapper Class within the existing Code. The full VBA Code is given below. The Combobox wrapper Class Property with the name CBO and its Event enabling Code is also added in the Class_Init Subroutine. All new Code lines are highlighted with Red Color.

The WrapObject_Init Class was copied from the earlier Project, renamed as EmpObject_Init, and added with new Code lines marked with red. Similarly other Classes. This way you can create Wrapper Classes quickly and reuse existing Code for other Forms and if necessary with modifications.

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 of ComboBox and the GotFocus(), LostFocus() Event Subroutine VBA Code is given 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 Standalone Class Module.

If you are an experienced Access VBA Programmer, then you may be able to make an assessment of the following Points:

The amount of work and time required to write the Code for the OnDirty() Event Subroutines Code for each TextBox and ComboBox Controls on the Employees & Orders Subform in their Form Modules through the existing manual VBA Code writing procedure.
Similarly, the amount of Work and Time required for the AfterUpdate() Event Subroutines on Both Forms.
Normally we don't write these codes in the Form Module for all the TextBoxes, only for essential cases. How do you feel about this new method and the ease of its implementation time saving compared to existing Coding practices? Not necessary to quantify, the emphasis is on the reusability of part of the written Code in the Standalone Class Module and saving of development time for future Projects.
Even though the new concept, of Streamlining the Form Module Coding in Standalone Class Modules, it is difficult for beginners to make an assessment, what do you think about it as an Expert VBA Programmer?
All the Episodes are written in the style of Tutoring the Title-related examples so that non-experts can also attempt to try and learn. How easy/difficult to understand the concept.

Your feedback may be left in the Comment Section. If you have a Gmail ID you can log in and Comment. Thanks.

The forthcoming Demonstrations on the Title Topic: Implementation of other Access Class Objects like Tab-Control, ListBox, Option Group, and the Report Module Code streamlining examples will follow.

The Demo Database is attached for Download.

Streamline9.zip

Streamlining Form Module Code in Standalone Class Module.

2 comments:

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.