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

Creating 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 see how we can organize the Wrapper Classes when there are several Forms and several Wrapper Classes for each Form in a database. They must be organized properly to avoid mix-ups like the wrapper class containing the Event Subroutines of Form A is added in the WrapObject_Init Class of Form B.

Wrapper Class Templates.

Create one set of Wrapper Class Templates for all frequently used controls like TextBoxes, CommandButtons, ComboBoxes, and others. Make Copies of Wrapper Classes and change their names, with some short name prefixes to the Wrapper Class Names like EmpTextBox (Emp for Employees Form) to relate the Wrapper Classes to the Employee's Form. All other controls ComboBoxes, Command Buttons and others for the Employees Form will have the same name Prefix Emp_TextBox format, or EmpTextBox format, Emp_CmdButton, Emp_ComboBox, and so on.

After going through the earlier nine Articles on the Streamlining of Form Module VBA Code topic you are now familiar with this new VBA Coding method and know how to create a new Wrapper Class if it is not available among the existing Templates when a new Control is added on the Form.

Forms With SubForms.

Form with SubForms don't need any separate Classes, need only one Wrapper Class for the Main Form. You can see an example of the Employees Main Form with the Orders Subform in episode nine. The TextBox and ComboBox controls are scanned in the EmpObject_Init Class in a separate For . . . Next Loop with the SubForm Reference and how the SubForm Control references are being mapped and added to the Collection Object. Their Event Subroutines can be written using the main Form Property like EmpTextBox wrapper Class Property name Txt for the TextBox on the Order SubForm like:

'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 have the correct reference of the SubForm TextBox and will execute the code for the TextBox in the Orders SubForm.

Let us see an example in the Sub Txt_GotFocus() Event Subroutine for not highlighting the 'OrderDate' TextBox on the Orders SubForm in the Employees Main Form.

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

We can address a control like any other control on the Main Form. The OrderDate field is not highlighted when it becomes current.

The next sample Code skips 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. But there are Event Subroutine Codes mentioned earlier (TextBox highlight, OnDirty, OnBeforeUpdate) or similar tasks you find can be used without change in other Projects.

In either case, the Backbone of this new streamlined Coding Procedure with less Code in the Wrapper Classes can be Exported into other Projects. Working with the Wrapper Classes for Coding independently and not mixing the Coding work with the Form design task will make the way for faster completion of Projects.

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

Access.TextBox
Access.CommandButton
Access.ComboBox and others

with reusable Event Subroutines, like TextBox, ComboBox, ListBox highlighting OnGotFocus, OnLostFocus Event Subroutines, TextBox OnDirty, OnBeforeUpdate Event Subroutines, for safeguarding against inadvertent changes, and similar reusable code you find can be included in the Wrapper Class Templates. Any Standard Module-based Common Functions in use in the Event Subroutines, like the GFColor, and LFColor Functions we used for highlighting the TextBox Controls must accompany the Wrapper Classes.

All Wrapper Classes we create will have a Form Property along with the Control Object Property like TextBox and the SET/GET Property Procedures for both the above objects as their body. The Wrapper Class Templates can be saved in a separate database. The Classes saved in this database can have the prefix .cls like:

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.

Even better, you can Export the Wrapper Class Templates from the VBA Window into a dedicated Folder in the Disk-Drive as separate Class Files with the file extension .cls, and you can Import them one by one into your 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) that will appear in the same place when selected by clicking on the Command Buttons given on the left side.

The Menu Design Image is given below.

There are three Pages in the Tab Control, with Page Names: Tables, Forms, and Reports.

Each Tab Page has a ListBox Control with the same dimension and is positioned on the same left and top values. The idea is to display all the Menu Pages: Tables, Forms, and Reports one after the other in the same position on the TabControl.

The following Property settings will change the display style of the Tab Control to the style shown in the first Image given above:

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

When the Tab Pages are hidden the Command Button Click will change the Pages and the Page-Change Event will Fire. The Click Event of the Command Button will highlight the Border of the Command Button to indicate the current selection. Besides that, the current Menu selection is announced by a female Voice.

Double-clicking on the List Item will display the Table or Form or Report on the screen. This action also will announce the type of object that is currently on display.

The ListBox Menu Source Items Employees, Orders, and Customers are added in the Value List in the format:

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

for all three Menus Tables, Forms & 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 Property declarations are made, followed by the Tab Control, Command Button, and ListBox Wrapper Class Property Declarations.

Next, the Set/Get Property Procedures for the Form. The Class_Init() Subroutine is Called From the Set Property Procedure of the Form object. In the For . . . Next Loop we look for our TabCtl, ListBox, and Command Button Controls.

The TabCtl control Wrapper Class TabLst_TabCtl Instance Properties tb_Frm and tb_Tab are assigned with the iFrm, ctl References. The TabCtl Control's Tab Page Change Event is enabled and then the TabLst_TabCtl Wrapper Class Instance is added to the Collection Object.

All three ListBox Properties are enabled with the DblClick() Event in the TabLst_ListBox Wrapper Class Instances and added to the Collection Object. Similarly, the Command Button 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 TabPage Change Event is fired will be captured here and display the Page Index Number in the 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 to the left side of the TabControl is the replacement of TabControl Page Buttons to hide the identity features of the TabControl. This is the secret of several layers of different Menus appearing one at a time in the same location. More layers of Menu Pages can be added to the TabControl with ListBoxes and Command Button.

The following Properties of the TabControl are set to hide the features of the TabControl:

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's Double-Click Event Subroutine, a few Array Variables are Initialised with the Menu Names and menu item names to compose the Speech Text to Announce the Menu item selection. This method also reduces the File opening statements from six to two lines each (excluding the array lines) for Tables, Forms, and Reports.

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.