August 2023 ~ LEARN MS-ACCESS TIPS AND TRICKS

Streamlining Form Module Code - Part Eleven

a.p.r. pillaiAugust 31, 2023 6 comments

Introduction.

In this Episode of Streamlining Form Module VBA Code, we will create Wrapper Classes for ComboBox and OptionGroup Controls. Having gone through the previous Episodes, you are now acquainted with the process of creating Wrapper Class Modules for other controls, such as TextBox, ListBox, CommandButton, and TabControl. You have also learned to write Event Subroutines within these modules instead of placing them in the Form Module.

ComboBox and OptionGroup Control.

In this session, we will focus on the usage of ComboBox and OptionGroup controls, as illustrated in the form image shown below.

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

The Order Detail data displayed in the ListBox is filtered through the query OrderDetailQ1, which is based on the Employee ID selected in the ComboBox control named cboEmp, located above the ListBox. The SQL statement for this query is shown below:

OrderDetailQ1 SQL.

SELECT Orders.EmployeeID, Employees.LastName, Orders.OrderID, Val(Format([OrderDate],"yyyy")) AS [Year], Orders.Freight
FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
WHERE (((Orders.EmployeeID)=[Forms]![Frm_OptionGroup]![cboEmp]));

The Freight values from OrderDetailQ1 are summarized by year in the query OrderSummaryQ1. This query provides the year-wise Freight totals, which serve as the source data for the Graph Chart displayed on the Form. The SQL statement for OrderSummaryQ1 is shown below:

OrderSummaryQ1 SQL.

SELECT [OrderDetailQ1].EmployeeID, [OrderDetailQ1].LastName, [OrderDetailQ1].Year, Sum([OrderDetailQ1].freight) AS Freight
FROM OrderDetailQ1
GROUP BY [OrderDetailQ1].EmployeeID, [OrderDetailQ1].LastName, [OrderDetailQ1].Year;

Freight Summary Data for the Chart.

Employee	Last Name	Year	Freight
Davolio, Nancy	Davolio	        1996	₹ 1,871.09
Davolio, Nancy	Davolio	        1997	₹ 4,584.47
Davolio, Nancy	Davolio	        1998	₹ 2,381.13

How are ComboBox, ListBox, and the Chart linked together?

When an Employee is selected in the ComboBox, the AfterUpdate event is triggered, and the statement cbofrm.List0.Requery refreshes the contents of the ListBox.

A hidden unbound TextBox on the Form is used to copy the Employee ID value from the cboEmp ComboBox. This TextBox serves as the Link Master Field for the Graph Chart, ensuring that the year-wise Freight summary updates automatically.

As a result, whenever the Employee selection changes (note that the ComboBox displays only the Employee Name, with the first column EmployeeID hidden by setting its column width to zero), both the OrderDetail ListBox and the year-wise Freight values in the Graph Chart are refreshed instantly.

The OptionGroup Control.

The OptionGroup control can contain a set of Radio Buttons, Check Boxes, or Toggle Buttons, all placed within a common Frame. In this example, we are using Radio Buttons inside an OptionGroup frame named Frame7. The Frame7 control holds three Radio Buttons, each with its own label, and is positioned below the ListBox and Graph objects on the Form.

There are three options to display the Employee's Freight Sales Values in three different categories.

- The highest Freight Sales Value of the Employee.
- The Lowest Freight Sales Value.
- The Total Fright Sales Value.

An Unbound TextBox, placed to the left of the OptionGroup control, displays the value of the selected Radio Button. Next to it, a Label control shows the selected option description. The label text is presented in an animated style, smoothly moving from right to left to highlight the current selection.

The Command Button Click will close the Form.

We already created wrapper classes for the ComboBox and ListBox in the earlier episodes. The OptionGroup Control is new in this Series of Tutorials and needs a Wrapper Class. When we place an OptionGroup Control on the Form, the default name used by Microsoft Access is something like Frame7. We will create a Wrapper Class named OptFrame for the OptionGroup Control.

The OptFrame Wrapper Class VBA Code.

Option Compare Database
Option Explicit

Private WithEvents Opt As Access.OptionGroup
Private frm As Access.Form

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

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

Public Property Set opt_Frm(ByRef ofrm As Form)
    Set frm = ofrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get o_opt() As OptionGroup
    Set o_opt = Opt
End Property

Public Property Set o_opt(ByRef mopt As OptionGroup)
    Set Opt = mopt
End Property

'Event Subroutines Code
Private Sub opt_Click()
Dim Rslt As Variant
Dim Cap(1 To 3) As String
Static strText As String

Cap(1) = "Highest Freight Value:"
Cap(2) = " Lowest Freight Value:"
Cap(3) = "  Total Freight Value:"

Select Case Opt.Name
    Case "Frame7"
        Select Case Opt.Value
            Case 1
            'Repeated Clicks on the same option is ignored.
            If strText = Cap(1) Then Exit Sub
                Rslt = DMax("Freight", "OrderDetailQ1")
            Case 2
                If strText = Cap(2) Then Exit Sub
                Rslt = DMin("Freight", "OrderDetailQ1")
                
            Case 3
                If strText = Cap(3) Then Exit Sub
                Rslt = DSum("Freight", "OrderDetailQ1")
        End Select
End Select
       frm!Result = Rslt
       strText = Cap(Opt)
       
       Call Animate(strText) 'Label Animation
End Sub

Private Sub Animate(ByVal txt As String)
'Label Animation
Dim L As Double
Dim n As String
Dim T As Double
Dim j As Integer

L = Len(txt)
txt = Space(L) & txt

For j = 1 To Len(txt) - L
 n = Left(txt, 1)
 txt = Right(txt, Len(txt) - 1)
 txt = txt & n
 frm.lblResult.Caption = Left(txt, L)

    Delay 0.02 'delay 20 milliseconds

Next
End Sub

In the OptFrame Wrapper Class Module, the OptionGroup control object (Opt) is declared with the WithEvents keyword in the global section of the Class Module. Alongside it, a Form object property named frm is also declared. Corresponding Property Procedures are then defined to manage these global properties.

The Opt_Click() Event Subroutine.

Private Sub opt_Click()
Dim Rslt As Variant
Dim Cap(1 To 3) As String
Static strText As String

Cap(1) = "Highest Freight Value:"
Cap(2) = " Lowest Freight Value:"
Cap(3) = "  Total Freight Value:"

Select Case Opt.Name
    Case "Frame7"
        Select Case Opt.Value
            Case 1
                If strText = Cap(1) Then Exit Sub
                Rslt = DMax("Freight", "OrderDetailQ1")
            Case 2
                If strText = Cap(2) Then Exit Sub
                Rslt = DMin("Freight", "OrderDetailQ1")
                
            Case 3
                If strText = Cap(3) Then Exit Sub
                Rslt = DSum("Freight", "OrderDetailQ1")
        End Select
End Select
       frm!Result = Rslt
       strText = Cap(Opt)
       
       Call Animate(strText) 'Label Animation
End Sub

The OptionGroup control on the Form provides three options to extract the Highest, Lowest, and Total Freight Values from the Order Sales transactions. These calculations are applied to the records corresponding to the Employee ID selected in the cboEmp ComboBox.

When an OptionGroup button is clicked, the corresponding Freight value is retrieved from the filtered OrderDetailQ1 data using the appropriate domain aggregate function — DMax(), DMin(), or DSum(). The calculated result is then displayed in the Rslt TextBox, positioned to the left of the OptionGroup control.

The category description of the displayed Freight value is retrieved from the Cap() array, based on the selected OptionGroup control’s Radio Button index. This text is then passed as a parameter to the Animate() subroutine, which displays it on the Label control located to the left of the Rslt TextBox. The text is revealed in an animated style, character by character, moving from right to left until the full description is displayed.

The statement If strText = Cap(n) Then Exit Sub ignores the Animation from repeating when clicked on the Body of the OptionGroup Frame.

The Label Animation.

The Animate() Subroutine Code segment is given below:

Private Sub Animate(ByVal txt As String)
'Label Animation
Dim L As Double
Dim n As String
Dim T As Double
Dim j As Integer

L = Len(txt)
txt = Space(L) & txt 'Add spaces at the left side

For j = 1 To Len(txt) - L
 n = Left(txt, 1)
 txt = Right(txt, Len(txt) - 1)
 txt = txt & n
 frm.lblResult.Caption = Left(txt, L)
 
 Delay 0.02 ' Pause 20 Milliseconds
 
 Next
End Sub

The length of the Parameter value in the Variable txt is calculated and stored in variable L. The parameter variable txt content is modified by adding an equal number of spaces to its original length on the left side.

The Animation Sequence.

The For…Next loop runs up to the original string length stored in variable L. In each iteration, one character is removed from the left side of the string and appended to its right end. The leftmost L characters are then assigned to the Caption property of the lblResult Label control.

After updating the label, the loop introduces a 20-millisecond delay before proceeding to the next iteration. This process repeats until the full description is displayed on the Label control, creating the scrolling animation effect.

The Delay() Function VBA Code in the Standard Module is given below for information:

Public Sub Delay(ByVal Sleep As Double)
Dim T As Double

T = Timer
Do While Timer < T + Sleep
    DoEvents
Loop
End Sub

The OptObject_Init Class Module VBA Code.

Option Compare Database
Option Explicit

Private iFrm As Access.Form

Private LstB As OptListBox
Private txt As OptTextBox
Private Fram As OptFrame
Private wcbo As optCombo
Private wcmd As OptCmdButton

Private Coll As New Collection

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Combo and Option Group Controls
'Author: a.p.r. pillai
'Date  : 31/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
    
    iFrm.cboEmp.DefaultValue = iFrm.cboEmp.Column(0, 1)
    iFrm.List0.Requery

Call Class_Init
End Property

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

Const EP = "[Event Procedure]"


For Each ctl In iFrm.Controls 'Scan for Controls

    Select Case TypeName(ctl)
            Case "OptionGroup"
              Select Case ctl.Name
                Case "Frame7" 'Option Group Name
                    Set Fram = New OptFrame 'Create Instance
                    Set Fram.opt_Frm = iFrm 'Assign Form Object
                    Set Fram.o_opt = ctl   'TextBox
                    
                        Fram.o_opt.OnClick = EP
                        
                    Coll.Add Fram 'Save EmpTextBox Class
                    Set Fram = Nothing 'Erase temp Instance
              End Select


        Case "ComboBox"
                    Set wcbo = New optCombo
                    Set wcbo.cbo_Frm = iFrm
                    Set wcbo.c_cbo = ctl
                    
                   wcbo.c_cbo.AfterUpdate = EP
                   wcbo.c_cbo.OnGotFocus = EP
                   wcbo.c_cbo.OnLostFocus = EP
                    
                    Coll.Add wcbo
                    Set wcbo = Nothing

        Case "TextBox"
                    Set txt = New OptTextBox
                    Set txt.tx_Frm = iFrm
                    Set txt.t_Txt = ctl
                    
                        txt.t_Txt.OnGotFocus = EP
                        txt.t_Txt.OnLostFocus = EP
                    
                    Coll.Add txt
                    Set txt = Nothing
                    
        Case "ListBox"
                    Set LstB = New OptListBox
                    Set LstB.lst_Frm = iFrm
                    Set LstB.m_Lst = ctl
                    
                        LstB.m_Lst.OnGotFocus = EP
                        LstB.m_Lst.OnLostFocus = EP
                    
                    Coll.Add LstB
                    Set LstB = Nothing
                    
        Case "CommandButton"
                    Set wcmd = New OptCmdButton
                    Set wcmd.cmd_Frm = iFrm
                    Set wcmd.c_cmd = ctl
                    
                   wcmd.c_cmd.OnClick = EP
                    
                    Coll.Add wcmd
                    Set wcmd = 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
End Sub

As with the other Access Object Wrapper Classes, all required objects are declared as Properties in the global area of the Module, followed by their respective Property Procedures.

Once the Form object is assigned to the internal iFrm object in the Set m_frm() Property Procedure, two initialization statements are executed to refresh the ComboBox and ListBox controls, ensuring they load their default values when the form is opened.

After these initializations, the Class_Init() A subroutine is called to complete the setup of the wrapper class.

The procedures written there are explained in detail in earlier episodes, and I am sure you are well-versed in the procedure.

It is worth noting that on the Form, there is only one instance of each control type: ComboBox, ListBox, TextBox, CommandButton, OptionGroup Control, and Chart Object.

Although the Chart object also exposes several events, just like the other controls, it is usually not given as much attention. However, if required, these events can be captured and managed in the same way by creating a dedicated Wrapper Class Module for the Chart control. This ensures the Chart’s interactions and behaviors can also be centralized, making the coding style fully consistent across all Form objects.

When to create a Wrapper Class?

There may be multiple instances of a particular control type on a Form (for example, several TextBox controls). In such cases, using Wrapper Classes becomes essential to capture and manage their individual events in a structured manner.

However, if there is only a single instance of a control type on the Form that requires one or more event procedures, then creating a dedicated Wrapper Class is not strictly necessary. Instead, you can:

Declare the control as a property in the OptObject_Init Class Module (the intermediate class), qualified with the WithEvents keyword.
Assign the control’s reference from the Form within the initialization routine.
Enable its events and write the required event procedures directly in the OptObject_Init Class Module.

This approach keeps the codebase cleaner by avoiding unnecessary Wrapper Classes, while still maintaining event-driven consistency for all controls.

Since the OptionGroup Control (Frame7) is a new addition in the Streamlining Form Module Code series, we created a dedicated Wrapper Class (OptFrame) for it.

For the other controls on the Form (ComboBox, ListBox, TextBox, and Command Button), only a single instance of each exists. Therefore, instead of creating separate Wrapper Classes, we declare them as single instances in the OptObject_Init Class Module, qualified with the WithEvents keyword.

These controls are then enabled with their required events, and their event subroutines are written directly in the intermediate class module (OptObject_Init), keeping the design lightweight and avoiding unnecessary Wrapper Classes.

Even though we have already created and used Wrapper Classes for these Objects earlier, using them in this case involves more VBA Code than necessary for a single instance of those Objects.

So, I created two Forms for Demo purposes:

frm_OptionGroup - All Control's Wrapper Classes are used in the OptObject_Init Class

frm_OptionGroup2 - Only Opt_Frame Wrapper Class is in the Opt_Object_Init2 Class.

The Opt_Object_Init2 Class Module Code.

The frm_OptionGroup2 Form's Intermediate Class Module (Opt_Object_Init2) Code is given below:

Option Compare Database
Option Explicit

Private WithEvents txt As Access.TextBox
Private WithEvents cmd As Access.CommandButton
Private WithEvents cbo As Access.ComboBox
Private WithEvents Lst As Access.ListBox

Private Fram As Opt_Frame2
Private iFrm As Access.Form
Private Coll As New Collection

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Combo and Option Group Controls
'Author: a.p.r. pillai
'Date  : 31/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
    
    iFrm.cboEmp.DefaultValue = iFrm.cboEmp.Column(0, 1)
    iFrm.List0.Requery
    
    Set txt = iFrm.Result
    Set cmd = iFrm.cmdClose
    Set cbo = iFrm.cboEmp
    Set Lst = iFrm.List0
    
Call Class_Init
End Property

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

Const EP = "[Event Procedure]"

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

    Select Case TypeName(ctl)
            Case "OptionGroup"
              Select Case ctl.Name
                Case "Frame7" 'Option Group Name
                    Set Fram = New Opt_Frame2 'Create Instance
                    Set Fram.opt_Frm = iFrm 'Assign Form Object
                    Set Fram.o_opt = ctl   'TextBox
                    
                        Fram.o_opt.OnClick = EP
                        
                    Coll.Add Fram 'Save EmpTextBox Class
                    Set Fram = Nothing 'Erase temp Instance
              End Select

        Case "CommandButton"
                   cmd.OnClick = EP
                    

        Case "ComboBox"
                   cbo.AfterUpdate = EP
                   cbo.OnGotFocus = EP
                   cbo.OnLostFocus = EP
          
        Case "TextBox"
            Select Case ctl.Name
                Case "Result"
                   txt.OnGotFocus = EP
                   txt.OnLostFocus = EP
            End Select
                    
        Case "ListBox"
                Lst.OnGotFocus = EP
                Lst.OnLostFocus = EP
                        
    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
End Sub

'Event Subroutines
Private Sub txt_GotFocus()
        GFColor iFrm, txt 'Field Highlight
End Sub

Private Sub txt_LostFocus()
    LFColor iFrm, txt 'Field Highlight
End Sub

Private Sub cmd_Click()
    If MsgBox("Close " & iFrm.Name & " Form?", vbYesNo + vbQuestion, "cmd_Click") = vbYes Then
        DoCmd.Close acForm, iFrm.Name
        Exit Sub
    End If
End Sub

Private Sub cbo_GotFocus()
    GFColor iFrm, cbo 'ComboBox highlight
    
    'Reset OptionGroup to default settings
    iFrm.Frame7 = Null 'Reset earlier selection of OptionGroup option
    iFrm!lblResult.Caption = "Result"
    iFrm.Result.Value = 0
    
End Sub

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

Private Sub cbo_AfterUpdate()
  iFrm.List0.Requery
  
End Sub

'Event Subroutines Code
Private Sub lst_GotFocus()
    GFColor iFrm, Lst 'ListBox highlight
End Sub

Private Sub lst_LostFocus()
    LFColor iFrm, Lst 'ListBox highlight
End Sub

Segmentwise Review of the VBA Code.

The Global Declaration Code segment is given below for review:

Option Compare Database
Option Explicit

Private WithEvents txt As Access.TextBox
Private WithEvents cmd As Access.CommandButton
Private WithEvents cbo As Access.ComboBox
Private WithEvents Lst As Access.ListBox

Private Fram As Opt_Frame2
Private iFrm As Access.Form
Private Coll As New Collection

All single-instance control declarations are placed in the global area of the module, just as we did at the beginning of this tutorial series.

Since the OptionGroup Control is a new addition, its dedicated Wrapper Class is included here.

Following this, the Form and Collection Object declarations are defined. The Collection Object is required only for the OptionGroup Control (Frame7), as it manages its wrapper class instances.

The Property Procedure Segment.

Next, the iFrm's Property Procedure Code Segment is given below:

'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
    
    'Set the ComboBox EmployeeID first item as default value
    iFrm.cboEmp.DefaultValue = iFrm.cboEmp.Column(0, 1)
    iFrm.List0.Requery 'Refresh the Order Details ListBox
    
    Set txt = iFrm.Result
    Set cmd = iFrm.cmdClose
    Set cbo = iFrm.cboEmp
    Set Lst = iFrm.List0
    
Call Class_Init
End Property

In the Set m_Frm() Property Procedure, the ComboBox and ListBox are initialized with their default values when the Form is opened.

After that, references for all the single-instance controls on the Form are assigned to the corresponding object variables declared in the global area. Finally, the Class_Init() Subroutine is called to complete the setup.

The Class_Init() Subroutine.

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

Const EP = "[Event Procedure]"

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

    Select Case TypeName(ctl)
            Case "OptionGroup"
              Select Case ctl.Name
                Case "Frame7" 'Option Group Name
                    Set Fram = New Opt_Frame2 'Create Instance
                    Set Fram.opt_Frm = iFrm 'Assign Form Object
                    Set Fram.o_opt = ctl   'TextBox
                    
                        Fram.o_opt.OnClick = EP
                        
                    Coll.Add Fram 'Save EmpTextBox Class
                    Set Fram = Nothing 'Erase temp Instance
              End Select

        Case "CommandButton"
                   cmd.OnClick = EP
                    

        Case "ComboBox"
                   cbo.AfterUpdate = EP
                   cbo.OnGotFocus = EP
                   cbo.OnLostFocus = EP
          
        Case "TextBox"
            Select Case ctl.Name
                Case "Result"
                   txt.OnGotFocus = EP
                   txt.OnLostFocus = EP
            End Select
                   
        Case "ListBox"
                Lst.OnGotFocus = EP
                Lst.OnLostFocus = EP
                        
    End Select
Next

End Sub

The Class_Init() Subroutine begins in the usual way by scanning the Form for its controls. When it identifies the OptionGroup control (Frame7), the Click Event is enabled, and the control is then added to the Collection Object.

The references for the other Form controls have already been assigned in the Set m_frm() Property Procedure. Their Events are also enabled at this stage, and the corresponding Event Subroutine code will be written directly in this Class Module.

In the case of the TextBox, there is another control named EID, which is kept hidden on the Form. Although hidden, it still appears during the scanning cycle and is enabled with Events. Since no Event Procedure code exists for this TextBox in the current Module, it has no impact on functionality. However, for clarity, we explicitly check for the specific Result TextBox and enable its Events, thereby ignoring the hidden EID TextBox. Following this, the ListBox is also enabled with the required Events.

The Event Subroutines of Single Control Instance Cases.

Next, the Event Subroutines Segment Code, which runs in the Opt_Object_Init2 Class.

'Event Runs automatically when the Form is Closed.
Private Sub Class_Terminate()
'Delete Collection Object contents
    Do While Coll.Count > 0
        Coll.Remove 1
    Loop
    
    Set iFrm = Nothing
End Sub

'TextBox Event Subroutines for highlighting the control
Private Sub txt_GotFocus()
        GFColor iFrm, txt 'Field Highlight
End Sub

Private Sub txt_LostFocus()
    LFColor iFrm, txt 'Field Highlight
End Sub

'Command Button Subroutines
Private Sub cmd_Click()
    If MsgBox("Close " & iFrm.Name & " Form?", vbYesNo + vbQuestion, "cmd_Click") = vbYes Then
        DoCmd.Close acForm, iFrm.Name
        Exit Sub
    End If
End Sub

'ComboBox Subroutines
Private Sub cbo_GotFocus()
    GFColor iFrm, cbo 'ComboBox highlight
    
    'Reset OptionGroup to default settings
    iFrm.Frame7 = Null 'Reset earlier selection of OptionGroup option
    iFrm!lblResult.Caption = "Result"
    iFrm.Result.Value = 0
    
End Sub


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

Private Sub cbo_AfterUpdate()
  iFrm.List0.Requery
  
End Sub

'ListBox Event Subroutines Code
Private Sub lst_GotFocus()
    GFColor iFrm, Lst 'ListBox highlight
End Sub

Private Sub lst_LostFocus()
    LFColor iFrm, Lst 'ListBox highlight
End Sub

All Event Subroutines are written with the Object Name declared in the Global Declaration Area in the Opt_Object_Init2 Class Module.

Demo Database Download

Streamline11.zip

Streamlining Form Module Code in Standalone Class Module.

Streamlining Form Module Code - Part Ten

a.p.r. pillaiAugust 19, 2023 2 comments

Creating an Access Menu using TabControl, ListBox, and Command Buttons.

We will take up the TabControl-based Menu design task after a few minutes.

Organizing the Wrapper Classes for Different Forms.

Let us now look at how to organize wrapper classes when working with multiple forms, each requiring several wrapper classes within the same database. Proper organization is essential to avoid mistakes—for example, accidentally adding a wrapper class that contains the event subroutines of Form A into the WrapObject_Init class of Form B. Wrapper Class Templates.

Create a standard set of wrapper class templates for all frequently used controls, such as TextBoxes, CommandButtons, ComboBoxes, and others. When implementing them for a specific form, make copies of these classes and rename them using a short, meaningful prefix that identifies the form. For example, for the Employees form, the prefix Emp can be used: EmpTextBox, EmpCmdButton, EmpComboBox, and so on. This naming convention clearly associates each wrapper class with its corresponding form, helping to maintain consistency across the project.

After reviewing the previous nine articles on streamlining form module VBA code, you should now be familiar with this new coding approach and understand how to create a new wrapper class whenever a required template is not already available—such as when a new control is added to the form.

Forms With SubForms.

Forms with subforms do not require separate wrapper classes; a single wrapper class for the main form is sufficient. For example, in Episode Nine, the Employees main form with the Orders subform demonstrates this approach. In the EmpObject_Init class, the TextBox and ComboBox controls on the subform are scanned within a separate For…Next loop using the subform reference. These subform controls are then mapped and added to the collection object.

Their event subroutines can be accessed through the main form property. For instance, the EmpTextBox wrapper class property name Txt can be used to reference a TextBox on the Orders subform, as shown below:

'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

The Txt object will hold the correct reference to the subform TextBox and will execute the corresponding code for that control in the Orders subform.

For example, consider the Txt_GotFocus() event subroutine. The following demonstration shows how to prevent the OrderDate TextBox on the Orders subform (within the Employees main form) from being highlighted when it receives focus.

Private Sub txt_GotFocus()
    If Txt.Name = "OrderDate" Then
         'No highlight
    Else
          GFColor frm, Txt
    End If
End Sub

We can reference a subform control just like any other control on the main form. In this case, the OrderDate field is excluded from highlighting when it becomes the current control.

The next sample code demonstrates how to skip highlighting all TextBoxes on the Orders subform.

Private Sub txt_GotFocus()
'https://learn.microsoft.com/en-us/office/vba/api/access.subform.parent
   Select Case Txt.Parent.Name         
    	Case "Orders"   'Orders SubForm has the parent Property set
          'Do Nothing
    	Case Else
           GFColor frm, Txt 'Field Highlight
   End Select
End Sub

Some examples of Wrapper Class Names are given below.

Wrapper Classes for the Employees Form.

EmpObject_Init
EmpTextBox
EmpCmdButton
EmpComboBox
EmpOptionGrp

Wrapper Classes for Orders Main Form.

Order_Object_Init
Order_TextBox
Order_CmdButton
Order_ListBox
Order_TabCtl

Reusing Streamlined Coding Procedures in Other Projects.

Now the Question of how to Reuse the Classes in another Database?

Different Projects and different requirements. Most of the Subroutines written for a particular Form, based on its specific requirements, cannot be used without changes in another Project. However, Event Subroutine Codes mentioned earlier (TextBox highlight, OnDirty, OnBeforeUpdate) or similar tasks can be used without modification in other Projects.

In both cases, the backbone of this new streamlined coding approach—using minimal code within wrapper classes—can be easily exported and reused in other projects. By handling coding through wrapper classes independently, without mixing it with form design tasks, development becomes more organized, and projects can be completed much faster.

You can create a set of Wrapper Class Templates for frequently used Form Controls, like:

Access.TextBox
Access.CommandButton
Access.ComboBox and others

Reusable event subroutines—such as OnGotFocus and OnLostFocus for highlighting controls, or OnDirty and BeforeUpdate for safeguarding against unintended changes—can be incorporated directly into the wrapper class templates for TextBoxes, ComboBoxes, ListBoxes, and other controls. Additionally, any common functions defined in standard modules that are used within these event subroutines (for example, the GFColor and LFColor functions for highlighting TextBox controls) should be included alongside the wrapper classes to ensure they work seamlessly.

Each wrapper class we create will include both a Form property and a Control object property (such as a TextBox), along with the corresponding Set, Get property procedures for these objects as the core of the class.

The wrapper class templates can be stored in a separate database for reuse. Classes saved in this database can follow the standard .cls prefix naming convention, for example:

clsObject_Init
clsTextBox
clsCmdButton and others.

How to use the Code from the Template Database.

Attach this database to your New Project as a Library database.
Create a new Class Module in the new Database.
Change its name to match the first Wrapper Class in the attached database.
Right-click on the Wrapper Class Module of the attached Database and select View Code from the displayed shortcut Menu. The Code will be displayed in the VBA Window.
Copy the Code and paste it into the new Class Module and save it.
Repeat this procedure to transfer all the Wrapper Class Templates to the New Database.
Then remove the attached database from the Reference Library.

Alternative Method.

An even better approach is to export the wrapper class templates from the VBA editor into a dedicated folder on your disk as individual class files with the .cls extension. These files can then be imported into new projects.

Access Menu with TabControl.

The finished Menu View Image is given below:

There are three layers of menu options—Tables, Forms, and Reports—which appear in the same area when selected by clicking the corresponding command buttons on the left side. The Menu Design Image is given below.

Each tab page includes a ListBox control of identical size, positioned at the same left and top coordinates. The purpose of this design is to display the menu pages, Tables, Forms, and Reports, one after another in the same location within the Tab Control.

The following property settings adjust the appearance of the Tab Control to match the style shown in the first image above:

Tab Style: None
Back Style: Transparent
Border Style: Transparent

When the tab pages are hidden, clicking a command button switches the active page, triggering the PageChange event. The button’s Click event also highlights its border to indicate the current selection. In addition, the selected menu option is announced by a female voice.

Double-clicking a list item will open the corresponding Table, Form, or Report on the screen, while also announcing the type of object currently being displayed.

The ListBox menu items, Employees, Orders, and Customers, are added to the value list in the following format:

1;"Employees";2;"Orders";3;"Customers"

for all three Menus, Tables, Forms, and Reports.

The TabLst_Object_Init Class

The TabLst_Objectr_Init Class Module Code is given below:

Option Compare Database
Option Explicit

Private iFrm As Access.Form
Private Coll As New Collection

Private tbc  As TabLst_TabCtl
Private wcmd As TabLst_CmdButton
Private lst  As TabLst_ListBox

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Tab Control Class_Init Class
'Author: a.p.r. pillai
'Date  : 16/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]"

'Scan for Tab Control, CommandButton & ListBox Controls
'and Enable the required Event Procedures
For Each ctl In iFrm.Controls

    Select Case ctl.ControlType
        Case acTabCtl
            Set tbc = New TabLst_TabCtl
            Set tbc.tb_Frm = iFrm
            Set tbc.tb_tab = ctl
                tbc.tb_tab.OnChange = EP
                
                Coll.Add tbc
            Set tbc = Nothing
    
        Case acListBox
            Set lst = New TabLst_ListBox
            Set lst.lst_Frm = iFrm
            Set lst.m_lst = ctl
            
            lst.m_lst.OnDblClick = EP
           
            Coll.Add lst
            Set lst = Nothing

        Case acCommandButton
            Select Case ctl.Name
                Case "cmdTables", "cmdForms", "cmdReports", "cmdExit"
                    Set wcmd = New TabLst_CmdButton
                    Set wcmd.cmd_Frm = iFrm
                    Set wcmd.c_cmd = ctl
                    
                    wcmd.c_cmd.OnClick = EP
                    Coll.Add wcmd
                    Set wcmd = Nothing
            End Select
            
   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
End Sub

In the global declaration area, the Form and Collection properties are declared first, followed by the property declarations for the Tab Control, Command Button, and ListBox wrapper classes.

Next come the Set and Get property procedures for the form. The Class_Init() subroutine is called from the Set property procedure of the form object. Within the For… Next loop, the code identifies the TabCtl, ListBox, and CommandButton controls.

The TabCtl control wrapper class (TabLst_TabCtl) instance properties—tb_Frm and tb_Tab—are assigned the references iFrm and ctl, respectively. The TabCtl control’s TabPageChange event is then enabled, and the TabLst_TabCtl wrapper class instance is added to the collection object.

All three ListBox properties are enabled with the DblClick() event in their respective TabLst_ListBox wrapper class instances and then added to the collection object. Similarly, the CommandButton wrapper class (TabLst_CmdButton) instances are enabled with the Click event and added to the collection object.

The TabLst_TabCtl Class

The TabLst_TabCtl Wrapper Class Module Code is given below.

Option Compare Database
Option Explicit

Private tbfrm As Access.Form
Private WithEvents Tb As Access.TabControl

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

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

Public Property Set tb_Frm(ByRef tabfrm As Form)
    Set tbfrm = tabfrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get tb_tab() As Access.TabControl
    Set tb_tab = Tb
End Property

Public Property Set tb_tab(ByRef ptab As Access.TabControl)
    Set Tb = ptab
End Property

'Event Subroutines Code
Private Sub tb_Change()
   Select Case Tb.Value
        Case 0
            MsgBox "Change Event: Page(0)"
        Case 1
            MsgBox "Change Event: Page(1)"
        Case 2
            MsgBox "Change Event: Page(2)"
    End Select
End Sub

When the TabControl TabPageChange event is fired, it is captured here, and the page index number is displayed in a MsgBox.

The Command Button Wrapper Class.

 
Option Compare Database
Option Explicit

Private WithEvents cmdfrm As Form
Private WithEvents cmd As CommandButton 'CommandButton object
Dim L As Integer
Dim ForeColor As Long

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

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

Public Property Set cmd_Frm(ByRef cfrm As Form)
    Set cmdfrm = cfrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get c_cmd() As CommandButton
    Set c_cmd = cmd
End Property

Public Property Set c_cmd(ByRef pcmd As CommandButton)
    Set cmd = pcmd
End Property

'Event Subroutines
Private Sub cmd_Click()
Select Case cmd.Name
  Case "cmdExit"
  Announce "Close the Form Now?"
    If MsgBox("Close the Form Now?", vbOKCancel + vbQuestion, "cmd_Click") = vbOK Then
        DoCmd.Close acForm, cmdfrm.Name
        Exit Sub
    End If
    
  Case "cmdTables"
    'Command Button Border Highlight
      cmd.BorderWidth = 2
      cmdfrm.cmdForms.BorderWidth = 0
      cmdfrm.cmdReports.BorderWidth = 0
      
    cmdfrm.TabCtl0.Pages(0).SetFocus
    Announce "Tables Menu Active."
    
  Case "cmdForms"
    'Command Button Border Highlight
    cmd.BorderWidth = 2
    cmdfrm.cmdTables.BorderWidth = 0
    cmdfrm.cmdReports.BorderWidth = 0
    
    cmdfrm.TabCtl0.Pages(1).SetFocus
        Announce "Forms Menu Active."

  Case "cmdReports"
  'Command Button Border Highlight
    cmd.BorderWidth = 2
    cmdfrm.cmdForms.BorderWidth = 0
    cmdfrm.cmdTables.BorderWidth = 0
    
    cmdfrm.TabCtl0.Pages(2).SetFocus
        Announce "Reports Menu Active."
End Select
End Sub

The three command buttons on the left side of the TabControl serve as replacements for the default TabControl page buttons, effectively hiding the TabControl’s built-in navigation features. This technique allows multiple layers of different menus to appear one at a time in the same location. Additional menu layers can also be added to the TabControl using ListBoxes and command buttons.

The following TabControl property settings are applied to hide its default features:

Style: None
Back Style: Transparent
Border Style: Transparent

The ListBox Wrapper Class.

Option Compare Database
Option Explicit

Private lstfrm As Access.Form
Private WithEvents lst As Access.ListBox

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

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

Public Property Set lst_Frm(ByRef mFrm As Form)
    Set lstfrm = mFrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get m_lst() As ListBox
    Set m_lst = lst
End Property

Public Property Set m_lst(ByRef mLst As ListBox)
    Set lst = mLst
End Property

Private Sub lst_DblClick(Cancel As Integer)
Dim i As Integer
Dim Menu(1 To 3) As String
Dim Obj(1 To 3) As String

i = Nz(lst.Value, 0)

Const Opn = "Opening "

Menu(1) = "Table "
Menu(2) = "Form "
Menu(3) = "Report "

Obj(1) = "Employees"
Obj(2) = "Orders"
Obj(3) = "Customers"

Select Case lst.Name
    Case "LstTables"
        Select Case i
            Case 1, 2, 3
                Announce Opn & Menu(1) & Obj(i) 'Speak
                DoCmd.OpenTable Obj(i), acViewNormal
        End Select
        
    Case "LstForms"
        Select Case i
            Case 1, 2, 3
                Announce Opn & Menu(2) & Obj(i) 'Speak
                DoCmd.OpenForm Obj(i), acViewNormal
        End Select
        
    Case "LstReports"
        Select Case i
            Case 1, 2, 3
                Announce Opn & Menu(3) & Obj(i) 'Speak
                DoCmd.OpenReport Obj(i), acViewReport
        End Select
End Select
End Sub

At the beginning of the ListBox Double-Click Event Subroutine, a few array variables are initialized with the menu names and corresponding menu item names. These arrays are then used to compose the speech text that announces the selected menu item. This approach also simplifies the code by reducing the number of file-opening statements from six lines to just two for each object type—Tables, Forms, and Reports (excluding the array initialization lines).

The normal Coding will look like the following:

Select Case lst.Name
    Case "LstTables"
        Select Case i
            Case 1
                Announce "Opening Table Employees" 'Speak
                DoCmd.OpenTable "Employees", acViewNormal
            Case 2
                Announce "Opening Table Orders" 'Speak
                DoCmd.OpenTable "Orders", acViewNormal
            Case 3
                Announce "Opening Table Customers" 'speak
                DoCmd.OpenTable "Customers", acViewNormal
        End Select
        
    Case "LstForms"
        Select Case i
            Case 1
                Announce "Opening Form Employees" 'Speak
                DoCmd.OpenForm "Employees", acViewNormal
            Case 2
                Announce "Opening Form Orders" 'Speak
                DoCmd.OpenForm "Orders", acViewNormal
            Case 3
                Announce "Opening Form Customers" 'speak
                DoCmd.OpenForm "Customers", acViewNormal
        End Select
        
    Case "LstReports"
        Select Case i
            Case 1
                Announce "Opening Report Employees" 'Speak
                DoCmd.OpenReport "Employees", acViewReport
            Case 2
                Announce "Opening Report Orders" 'Speak
                DoCmd.OpenReport "Orders", acViewReport
            Case 3
                Announce "Opening Report Customers" 'speak
                DoCmd.OpenReport "Customers", acViewReport

        End Select
End Select

Microsoft Speech-Service VBA Code is given below. The Subroutine Code is in the Standard Module.

Sub Announce(ByVal Txt As String, Optional Gender As String = "Female")
'https://learn.microsoft.com/en-us/azure/ai-services/speech-service/
    Dim obj As Object
    Set obj = CreateObject("SAPI.SpVoice")
    Set obj.Voice = obj.GetVoices("Gender = " & Gender).Item(0)
    obj.Speak Txt
End Sub

The TabLst_ListBox Wrapper Class DblClick() Event Subroutines Runs the Menu Options, Tables, Forms, and Reports, opening activities.

Streamline10.zip

Streamlining Form Module Code in Standalone Class Module.

Streamlining Form Module Code - Part Nine

a.p.r. pillaiAugust 07, 2023 2 comments

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