Streamlining Code Synchronized Floating POPup Form. ~ 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 raised by the form and its controls—such as TextBoxes and Command Buttons—are now handled within the standalone class module, with their corresponding event subroutines executed outside the form module.

This represents a paradigm shift: VBA code that would normally reside in the form module is instead executed from a standalone class module, significantly reducing the amount of code required in the form module. As a result, the Form primarily serves as the user interface for designing and displaying controls.

VBA coding, code management, and debugging can now be performed independently, without opening Forms in Design View or navigating through control event properties to access 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. There will be only one BeforeUpdate() Event Subroutine needed for 25 or more TextBoxes on the Form, even if all 25 of them need different sets of Code. If you have 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 removed from the Screen 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 data integrity.

There are several TextBoxes, two Combo boxes, and two Command Buttons on the Form. The second Form has several text boxes and a Command Button.

To achieve the desired functionality, we need three Wrapper Classes—one for TextBoxes, one for Command Buttons, and one for Combo Boxes—along with the Intermediary Class Module (Interface Class). The Interface Class is responsible for creating Wrapper Class instances, initializing them with the corresponding Object references, and saving them dynamically in a Collection Object in memory. This approach ensures that when the events are fired from the form, they can be captured in the respective Wrapper Classes and run the required Event Procedures. 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 utilisation of the same TextBoxWrapper Class Instances for all Text Boxes 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 a unified, 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 is common. Writing the Command Button Click Event Subroutines in the same Wrapper Class Module as demonstrated above streamlines 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 Form 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 to create the Wrapper Class Instances and Initialize Procedures.

At the beginning of the Class_Init() Subroutine, the Employee_Sub Form is opened in Hidden Mode and kept in memory. The sFrm Form object is assigned 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 checks the presence 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 the BackColor and BackStyle properties of TextBox and ComboBox Controls enhances user interaction on the form. Specifically, these properties are adjusted to highlight the backcolor in yellow when the control is active. The BackStyle property is set to Transparent, ensuring 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.

'Assign the Form Reference to sFrm Object
Set sFrm = Forms("Employee_Sub")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 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 SQL query is formulated using the EmployeeID value as a key to filter the data from the employee record. Subsequently, this Query utilises the second form Record Source of Employee_Sub, ensuring personal info Data synchronization 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. 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 Form's 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 the 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.