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.
Demo Database Download
- Reusing Form Module VBA Code for New Projects.
- Streamlining Form Module Code - Part Two.
- Streamlining Form Module Code - Part Three
- Streamlining Form Module Code - Part Four
- Streamlining Form Module Code - Part Five
- Streamlining Form Module Code - Part Six
- Streamlining Form Module Code - Part Seven
- Streamlining Form Module Code - Part Eight
- Streamlining Form Module Code - Part Nine
- Streamlining Form Module Code - Part Ten
- Streamlining Form Module Code - Part Eleven
- Streamlining Report Module Code in Class Module
- Streamlining Module Code Report Line Hiding-13.
- Streamlining Form Module Code Part-14.
- Streamlining Custom Made Form Wizard-15.
- Streamlining VBA Custom Made Report Wizard-16.
- Streamlining VBA External Files List in Hyperlinks-17
- Streamlining Events VBA 3D Text Wizard-18
- Streamlining Events VBA RGB Color Wizard-19
- Streamlining Events Numbers to Words-20
- Access Users Group(Europe) Presentation-21
- The Event Firing Mechanism of MS Access-22
- One TextBox and Three Wrapper Class Instances-23
- Streamlining Code Synchronized Floating Popup Form-24
- Streamlining Code Compacting/Repair Database-25
- Streamlining Code Remainder Popup Form-26
- Streamlining Code Editing Data in Zoom-in Control-27
- Streamlining Code Filter By Character and Sort-28
- Table Query Records in Collection Object-29
- Class for All Data Entry Editing Forms-30
- Wrapper Class Module Creation Wizard-31
- wrapper-class-template-wizard-v2
No comments:
Post a Comment
Comments subject to moderation before publishing.