Streamlining Code Synchronized Forms ~ LEARN MS-ACCESS TIPS AND TRICKS

Streamlining Form Module Code in Standalone Class Module.

Synchronized Floating Popup Form.

The article on the Synchronized Floating Popup Form was originally published in February 2009. The notable difference is that once written in the Form Module, the Event Subroutine code is now executed from the Standalone Class Module.

All events triggered from the form and controls, such as TextBoxes and Command Buttons, are now captured in the Standalone Class Module, with the corresponding event subroutines being executed outside the Form Module. This exemplifies a paradigm shift where Form Module VBA codes are run from the Standalone Class Module, resulting in significantly reduced VBA code compared to what is typically written in the Form Module. At this point, the form serves solely for interface design purposes.

VBA Coding, Code Management, and debugging can be done independently without going into the Form Design View and Control's Event Property to get to the required Code in the Form Module. You can always find your Object group-level streamlined Event Subroutine Code, in one place in the Standalone Class Module, which is like one BeforeUpdate() Event Subroutine is only needed for 25 TextBoxes on the Form, even if all 25 of them need different sets of Code. If you already visited the earlier Episodes of this Topic you already know it by now.

Synchronized Floating Popup Forms.

The Employee record is structured into two distinct logical sets of information. The initial part encompasses Official Information, while the subsequent part entails Personal Information, including details, such as address and phone number. To enhance user experience, these two sets of information are displayed in two separate, independent forms. Notably, the Personal Information section is not mandated to remain visible at all times on the screen. Instead, it can be accessed and displayed dynamically by clicking the "Personal Info" command button.

Moreover, as the navigation control advances to the next record on the first Form, it triggers an immediate update of the corresponding data on the second Form, guaranteeing seamless synchronization between the two forms. This ensures that the information displayed on both forms remains consistently aligned as the user navigates through the records.

The second form can be kept from displaying on the Screen all the time, by Clicking on the Close Command Button. It can be displayed by selecting the Personal Info Command Button when needed.

The OnDirty and BeforeUpdate Events are enabled for all TextBoxes and ComboBoxes to safeguard the data integrity.

The first Form has several TextBoxes, two Comboboxes, and two Command Buttons. The second Form has several TextBoxes and a Command Button.

To achieve the desired functionality, we need three Wrapper Classes—one for TextBoxes, one for Command Buttons, and one for ComboBoxes—along with the Intermediary Class Module (Interface Class). The Interface Class is responsible for creating the Wrapper Class instances, initializing them with the corresponding Object references, and saving instances dynamically in a Collection Object in memory. This approach ensures that when the events are triggered on the form, they can be captured in the respective Wrapper Classes, allowing the execution of the required Event Procedures within the Wrapper Class Instances. This modular and organized structure enhances code manageability and promotes effective event handling across different types of controls on the form.

The TextBox Wrapper Class VBA Code.

Option Compare Database
Option Explicit

Private WithEvents Txt As Access.TextBox
Private tfrm As Access.Form
'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'TextBox Events
'Author: a.p.r. pillai
'Date  : 16/01/2024
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
'======== Form Object Property Procedure =========
Public Property Get t_Frm() As Access.Form
    Set t_Frm = tfrm
End Property

Public Property Set t_Frm(ByRef vFrm As Access.Form)
    Set tfrm = vFrm
End Property

'======== TextBox Object Property Procedure =========
Public Property Get t_txt() As Access.TextBox
    Set t_txt = Txt
End Property

Public Property Set t_txt(ByRef vtxt As Access.TextBox)
    Set Txt = vtxt
End Property

'======== Event Subroutine =========
Private Sub txt_Dirty(cancel As Integer)
If MsgBox("Are you Editing " & Txt.Name & " Field?", vbYesNo + vbCritical, Txt.Name & " Dirty()") = vbNo Then
    cancel = True
End If
End Sub

Private Sub txt_BeforeUpdate(cancel As Integer)
If MsgBox("Save the Changes " & Txt.Name & " Field?", vbYesNo + vbCritical, Txt.Name & " Dirty()") = vbNo Then
    cancel = True
End If
End Sub

The TextBox Wrapper Class streamlines the process with just two Event Procedures, each containing a few lines of reusable code. This significantly simplifies the handling of all TextBoxes on both forms. Contrast this with the scenario where you'd have to repetitively write these concise lines of code for each TextBox in both Form Modules. The Wrapper Class approach promotes code efficiency and enhances maintainability by consolidating common functionality in a centralized location.

A crucial aspect worth highlighting is the utilization of the same TextBoxWrapper Class Instances for all TextBoxes across two distinct forms. This showcases the versatility of the new coding approach. This flexibility is particularly beneficial in scenarios involving one or more SubForms within the Main Form, as it allows for a unified and efficient management of TextBox events and functionality, promoting a cohesive and streamlined development process.

The Command Button Wrapper Class VBA Code.

Option Compare Database
Option Explicit

Private WithEvents cmd As Access.CommandButton
Private cFrm As Access.Form
Private uFrm As Access.Form
Private strSQL As String

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'CommandButton Events
'Author: a.p.r. pillai
'Date  : 16/01/2024
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Public Property Get c_Frm() As Access.Form
    Set c_Frm = cFrm
End Property

Public Property Set c_Frm(ByRef vFrm As Access.Form)
    Set cFrm = vFrm
    Set uFrm = Forms("Employee_Sub")
End Property

Public Property Get c_cmd() As Access.CommandButton
    Set c_cmd = cmd
End Property

Public Property Set c_cmd(ByRef vcmd As Access.CommandButton)
    Set cmd = vcmd
End Property

'====Event Subroutines====
Private Sub cmd_Click()
    Select Case cmd.Name
        Case "cmdClose"
            If MsgBox("Close the Main Form?", _
            vbYesNo + vbCritical, cmd.Name & "_Click()") = vbNo Then
                'Do Nothing
            Else
                DoCmd.Close acForm, "Employee_Sub"
                DoCmd.Close acForm, cFrm.Name
            End If
        
        Case "cmdPersonalInfo"
                strSQL = "SELECT Employees.* FROM Employees "
                strSQL = strSQL & "WHERE ([EmployeeID] = " & cFrm![EmployeeID] & ");"
                uFrm.RecordSource = strSQL
                uFrm.Requery
                uFrm.Visible = True
                cFrm.ActiveControl.SetFocus
        
        Case "cmdCloseSub"
                uFrm.Visible = False
    End Select
End Sub

Having two Command Buttons on the Main Form, and an additional one on the Second Form to close it is common. Writing the Command Button Click Event Subroutines in the same Wrapper Class Module as demonstrated above, streamlines the code organization. Each Command Button corresponds to a dedicated Wrapper Class Instance, and these instances are assigned their respective Control References from both forms.

Using control references as keys is crucial for the system to accurately identify the correct instance of the Wrapper Class, ensuring that the appropriate Event Subroutine is executed for each Command Button click. This approach enhances code clarity, maintainability, and reusability across different forms and controls.

The ComboBox Wrapper Class VBA Code.

Option Compare Database
Option Explicit

Private WithEvents cbo As Access.ComboBox
Private bFrm As Access.Form

'-------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'-------------------------------------------------------
'CommandButton Events
'Author: a.p.r. pillai
'Date  : 16/01/2024
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'-------------------------------------------------------
Public Property Get b_Frm() As Access.Form
    Set b_Frm = bFrm
End Property

Public Property Set b_Frm(ByRef vFrm As Access.Form)
    Set bFrm = vFrm
End Property

Public Property Get b_cbo() As Access.ComboBox
    Set b_cbo = cbo
End Property

Public Property Set b_cbo(ByRef vcbo As Access.ComboBox)
    Set cbo = vcbo
End Property

'==== ComboBox Event Subroutine ====
Private Sub cbo_Dirty(cancel As Integer)
If MsgBox("Are you Editing " & cbo.Name & " Control?", _
vbYesNo + vbCritical, cbo.Name & " Dirty()") = vbNo Then
    cancel = True
End If
End Sub

Private Sub cbo_BeforeUpdate(cancel As Integer)
If MsgBox("Save the Changes " & cbo.Name & " Control?", _
vbYesNo + vbCritical, cbo.Name & " Dirty()") = vbNo Then
    cancel = True
End If
End Sub

There are two Combo Boxes on the Main Form. The Comboboxes are also enabled with the OnDirty() and BeforeUpdate() Event Subroutines to safeguard the data from unintentional changes or to apply changes with the User's consent.

The Interface Class Module VBA Code.

Option Compare Database
Option Explicit

Private ocmd As ClsCmdButton 'Wrapper Class
Private oTxt As ClsTextBox   'Wrapper Class
Private ocbo As ClsCombo     'Wrapper Class

Private WithEvents Frm As Access.Form 'Employees_Main
Private sFrm As Access.Form 'Employee_Sub

Private coll As New Collection
Private strSQL As String

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Wrapper Classes Initialization
'Author: a.p.r. pillai
'Date  : 16/01/2024
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

Public Property Get i_Frm() As Access.Form
    Set i_Frm = Frm
End Property

Public Property Set i_Frm(ByRef vFrm As Access.Form)
    Set Frm = vFrm
    
    Call Class_Init
End Property

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

'Open the 2nd Form
DoCmd.OpenForm "Employee_Sub", , , , , acHidden

'Assign the Form Reference to sFrm Object
Set sFrm = Forms("Employee_Sub")

'Enable the OnCurrent Event of the Employees_Main Form
Frm.OnCurrent = EP

For Each ctl In Frm.Controls 'Employees_Main Form Controls
    Select Case TypeName(ctl)
        Case "CommandButton"
        
          Select Case ctl.Name '
            Case "cmdClose", "cmdPersonalInfo"
                Set ocmd = New ClsCmdButton 'Create new instance
                Set ocmd.c_Frm = Frm 'Assign Main Form Reference
                Set ocmd.c_cmd = ctl 'Pass current CommandButton Object Reference
                    ocmd.c_cmd.OnClick = EP 'Enable OnClick Event
                    coll.Add ocmd 'Add the instance to Collection Object
                Set ocmd = Nothing 'Reset Wrapper Class instance
          End Select
          
            Case "TextBox"
                Set oTxt = New ClsTextBox
                Set oTxt.t_Frm = Frm
                Set oTxt.t_txt = ctl
                    oTxt.t_txt.OnDirty = EP
                    oTxt.t_txt.BeforeUpdate = EP
                    oTxt.t_txt.BackColor = RGB(&HFF, &HF2, &H0)
                    oTxt.t_txt.BackStyle = 0
                    coll.Add oTxt
                Set oTxt = Nothing
                
            Case "ComboBox"
                Set ocbo = New ClsCombo
                Set ocbo.b_Frm = Frm
                Set ocbo.b_cbo = ctl
                    ocbo.b_cbo.OnDirty = EP
                    ocbo.b_cbo.BeforeUpdate = EP
                    ocbo.b_cbo.BackColor = RGB(&HFF, &HF2, &H0)
                    ocbo.b_cbo.BackStyle = 0
                    coll.Add ocbo
                Set ocbo = Nothing

    End Select
Next

For Each ctl In sFrm.Controls 'Employees_Sub Form Controls
    Select Case TypeName(ctl)
        Case "CommandButton"
        
          Select Case ctl.Name
            Case "cmdCloseSub"
                Set ocmd = New ClsCmdButton 'Instantiate
                Set ocmd.c_Frm = sFrm
                Set ocmd.c_cmd = ctl
                    ocmd.c_cmd.OnClick = EP
                    
                    coll.Add ocmd           'Add to Collection
                Set ocmd = Nothing
          End Select
          
            Case "TextBox"
                Set oTxt = New ClsTextBox   'Instantiate
                Set oTxt.t_Frm = sFrm
                Set oTxt.t_txt = ctl
                    oTxt.t_txt.OnDirty = EP
                    oTxt.t_txt.BeforeUpdate = EP
                    oTxt.t_txt.BackColor = RGB(&HFF, &HF2, &H0)
                    oTxt.t_txt.BackStyle = 0
                    coll.Add oTxt           'Add to Collection
                Set oTxt = Nothing
    End Select
Next

End Sub

Private Sub frm_Current()
If IsLoaded("Employee_Sub") Then
   strSQL = "SELECT Employees.* FROM Employees "
   strSQL = strSQL & "WHERE ([EmployeeID] = " & Frm![EmployeeID] & ");"
   sFrm.RecordSource = strSQL
   Frm.SetFocus
End If

Private Sub Class_Terminate()
Set Frm = Nothing
Set sFrm = Nothing
Do While coll.Count > 0
    coll.Remove 1
Loop

End Sub

The Wrapper Classes are declared as Properties of the Interface Class. Two Form Object Properties are declared to scan for Controls on Employees_Main and Employee_Sub Forms.

The Frm Object is qualified with the Keyword WithEvents to enable the OnCurrent Event of the Employees_Main Form to update the Employee_Sub Form data based on the movement of Records on the Main Form.

The Collection Object and a String Variable strSQL are Properties declared in the Global Area.

The Property Procedures for the Frm Object are declared next to receive the Main From Object Reference from the Form_Load() Event Procedure of the Employees_Main Form. After assigning the Main Form Reference to the Frm Object the Class_Init Subroutine is called for creating the Wrapper Class Instances and Initializing Procedures.

At the beginning of the Class_Init() Subroutine the Employee_Sub Form is open in Hidden Mode and kept in memory. The sFrm Form object is assigned with the Reference of the Employee_Sub Form.

The following statement enables the Main Form's OnCurrent Event to fire the Event when the record is moved from one to the other through the Record Navigation Control:

'Enable the OnCurrent Event of the Employees_Main Form
Frm.OnCurrent = EP

There are three sets of Objects on the Employees_Main Form: TextBoxes, Command Buttons, and Combo Boxes. The Employee_Sub Form has a few TextBoxes and a single Command Button to create Wrapper Class Instances and assign their related Object References.

The following VBA Code Segment scans the Employees_Main Form for Textboxes, Command Buttons, and ComboBoxes:

For Each ctl In Frm.Controls 'Employees_Main Form Controls
    Select Case TypeName(ctl)
        Case "CommandButton"
        
          Select Case ctl.Name '
            Case "cmdClose", "cmdPersonalInfo"
                Set ocmd = New ClsCmdButton     'Create new instance
                Set ocmd.c_Frm = Frm            'Assign Main Form Reference
                Set ocmd.c_cmd = ctl            'Pass current CommandButton Object Reference
                    ocmd.c_cmd.OnClick = EP     'Enable OnClick Event
                    coll.Add ocmd               'Add the instance to Collection Object
                Set ocmd = Nothing              'Reset Wrapper Class instance
          End Select
          
            Case "TextBox"
                Set oTxt = New ClsTextBox
                Set oTxt.t_Frm = Frm
                Set oTxt.t_txt = ctl
                    oTxt.t_txt.OnDirty = EP
                    oTxt.t_txt.BeforeUpdate = EP
                    
                    oTxt.t_txt.BackColor = RGB(255, 242,0) 'Yellow Color
                    oTxt.t_txt.BackStyle = 0
                    
                    coll.Add oTxt
                Set oTxt = Nothing
                
            Case "ComboBox"
                Set ocbo = New ClsCombo
                Set ocbo.b_Frm = Frm
                Set ocbo.b_cbo = ctl
                    ocbo.b_cbo.OnDirty = EP
                    ocbo.b_cbo.BeforeUpdate = EP
                    
                    ocbo.b_cbo.BackColor = RGB(255, 242, 0) 'Yellow Color
                    ocbo.b_cbo.BackStyle = 0
                    
                    coll.Add ocbo
                Set ocbo = Nothing

    End Select
Next

The provided code segment examines the existence of Command Button, TextBox, and ComboBox controls on the Employees_Main Form. It creates instances of the corresponding Wrapper Classes, sets the Wrapper Class property values, enables the controls' OnDirty and BeforeUpdate Events, and then adds these instances to the Collection Object in memory. This systematic approach ensures that the necessary controls are properly encapsulated within their respective Wrapper Class Instances and facilitates organized event handling through the centralized Collection.

The dynamic modification of TextBox and ComboBox controls' BackColor and BackStyle property values enhances user interaction on the form. Specifically, these properties are adjusted to highlight the background of the controls in yellow when they become active. The BackStyle property is set to Transparent, ensuring that the yellow background color is only displayed when the TextBox or ComboBox is in focus, contributing to a visually intuitive and user-friendly design.

The Employee_Sub Form Controls VBA Code Segment.

For Each ctl In sFrm.Controls 'Employees_Sub Form Controls
    Select Case TypeName(ctl)
        Case "CommandButton"
        
          Select Case ctl.Name
            Case "cmdCloseSub"
                Set ocmd = New ClsCmdButton 'Instantiate
                Set ocmd.c_Frm = sFrm
                Set ocmd.c_cmd = ctl
                    ocmd.c_cmd.OnClick = EP
                    
                    coll.Add ocmd           'Add to Collection
                Set ocmd = Nothing
          End Select
          
            Case "TextBox"
                Set oTxt = New ClsTextBox   'Instantiate
                Set oTxt.t_Frm = sFrm
                Set oTxt.t_txt = ctl
                    oTxt.t_txt.OnDirty = EP
                    oTxt.t_txt.BeforeUpdate = EP
                    
                    oTxt.t_txt.BackColor = RGB(&HFF, &HF2, &H0)
                    oTxt.t_txt.BackStyle = 0
                    
                    coll.Add oTxt           'Add to Collection
                Set oTxt = Nothing
    End Select
Next

The second form exclusively contains TextBoxes, each equipped with OnDirty() and BeforeUpdate() Event Procedures, akin to the Main Form. If additional Event Subroutines are deemed necessary for these TextBoxes, beyond OnDirty() and BeforeUpdate(), they can be conveniently authored within the same Wrapper Class. The beauty of this approach lies in the uniqueness of references assigned to each TextBox. This ensures that the references are tied to the respective objects, enabling the system to accurately locate and execute the correct Event Procedure for each TextBox through the associated Wrapper Class Instances.

The Form_Current() Event Subroutine.

Private Sub frm_Current()
If IsLoaded("Employee_Sub") Then
   strSQL = "SELECT Employees.* FROM Employees "
   strSQL = strSQL & "WHERE ([EmployeeID] = " & Frm![EmployeeID] & ");"
   sFrm.RecordSource = strSQL
   Frm.SetFocus
End If
End Sub

Private Sub Class_Terminate()
Set Frm = Nothing
Set sFrm = Nothing
Do While coll.Count > 0
    coll.Remove 1
Loop
End Sub

When the Record Navigation Button is employed to navigate to the next or previous record on the Employees_Main Form, the Form_Current() Event is triggered and captured by the Subroutine frm_Current() provided above. In response, a Query SQL is formulated using the EmployeeID value as a key to filter the data from the employee record. Subsequently, this Query is utilized as the Record Source of the second form, Employee_Sub, ensuring the synchronization of the Personal Info Data with the record currently displayed on the Employees_Main Form. This approach facilitates seamless coordination between the two forms, providing updated and synchronized information based on the selected record.

The Sub Class_Terminate() Subroutine works like the Form_Unload() Event Subroutine on the Form. When you close the Form, the Form_Unload() Event Procedure fires, if it is present on the Form before the Form is actually closed. This Subroutine clears the Forms declarations, removes all the Wrapper Class Instances from the Collection Object, and finally removes the Collection Object itself from memory.

The Employees_Main Form Module Code.

Option Compare Database
Option Explicit

Private obj As New ClsObject_Init

Private Sub Form_Load()
    Set obj.i_Frm = Me
End Sub

Private Sub Form_Unload(cancel As Integer)
    Set obj = Nothing
If IsLoaded("Employee_Sub") Then
  DoCmd.Close acForm, "Employee_Sub"
End If
End Sub

Declares the Interface Object ClsObj_Init Class Module and Instantiates it with the Object Name obj in the Global Declaration area of the Form Module.

In the Form_Load() Event Subroutine the Form Object Me is passed to the Frm Property of ClsObj_Init Interface Class through the Property Procedure i_Frm() in this statement Set obj.i_frm = Me.

In the Form_Unload() Event Procedure the statement Set obj = Nothing attempts to clear ClsObj_Init Interface Class from memory. This will trigger the Sub Class_Terminate() Subroutine we discussed above.

Hope you enjoyed the new way of Coding and your feedback is highly appreciated.