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

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.

XeniOctober 9, 2023 at 8:28 PM
Another useful method you can use for copying classes & modules from one project to another one is open both databases in different Access instances, open vba code for aplications in both of them and then you can drag and drop classes & modules from one project to other. I came across with this approach few weeks ago and it saves me a lot of time.
a.p.r. pillaiOctober 10, 2023 at 9:32 AM
Thanks for the useful Tips.

When professionals implement new concepts in real-world projects, it often leads to significant improvements. I am sure, the "Streamlining of Form Module VBA Coding in Stand-alone Class Module" will pave the way for better coding methods, accelerating project completion. Moreover, embracing automation not only streamlines work, but also enables achieving more with less Code, ultimately enhancing efficiency and productivity.