August 2019 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

So far, we have primarily worked with a single type of control — TextBoxes on a form — enabling specific events to be raised and captured in a Class Module using various methods. We have performed several demo runs to capture events, like AfterUpdate and LostFocus raised from multiple TextBoxes, and handled them in the Class Module.

For each TextBox on the form, a separate Class Object instance is created, enabled with the required event, and stored either in an Object Array, a Collection Object, or as items in a Dictionary Object.

For example, suppose the form has five TextBoxes, but only three require event handling to perform tasks such as validating the entered data. In that case, only three instances of the ClsText Class Module are created—one for each of the three active TextBoxes—while the remaining two are ignored.

When an enabled event fires, the corresponding Class Object instance captures the event and executes the associated event procedure code.

In our previous session, we extended this concept by adding Command Button controls and their own Class Module alongside the TextBoxes for our demo runs.

New Demo Form: frmControls_All

In this demo Run, we will include most of the frequently used controls on the Form. The image of the demo Form is given below:

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

In addition to TextBoxes and Command Buttons, forms can also include other controls such as Tab Controls, Combo Boxes, List Boxes, and Option Groups.

To handle events from these controls, we need to create a separate Class Module for each new control type on the demo form. We already have dedicated Class Modules set up for TextBoxes and Command Buttons.

The TextBox and CommandButton Class Modules.

The Text Box and Command Button Class Module code, along with their explanations, were presented in an earlier article. To keep this post concise, those details are not repeated here.

You can revisit those sections by using the bookmark links provided below:

Class Module: ClsText
Class Module: ClsCmdButton
Download: You may download the demo database from that post, run the sample form, and experiment with the controls to explore the implemented methods and VBA code.

What we are doing here is simply an extension of that work, now incorporating more control types on the form.

Tab-Control Class Module: ClsTabCtrl

The Tab-Control Class Module: ClsTabCtrl VBA Code is given below:

Option Compare Database
Option Explicit

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

Public Property Get p_frm() As Access.Form
  Set p_frm = frm
End Property

Public Property Set p_frm(ByRef frmValue As Access.Form)
  Set frm = frmValue
End Property

Public Property Get p_Tb() As Access.TabControl
   Set p_Tb = Tb
End Property

Public Property Set p_Tb(ByRef tbValue As Access.TabControl)
  Set Tb = tbValue
End Property

Public Sub Tb_Change()
Dim msg As String, title As String

Select Case Tb
    Case 0
        frm.Controls("Computer").Value = Environ("ComputerName")
        title = Tb.Pages(0).Name
        msg = "Tab Page Index = 0"
    Case 1
        frm.Controls("UserName").Value = Environ("UserName")
        title = Tb.Pages(1).Name
        msg = "Tab Page Index = 1"
End Select

MsgBox msg, , title

End Sub

The ClsTabCtrl Class Module declares two properties: a Form object (frm) and a Tab Control object (Tb). The Tb property is declared using the WithEvents keyword, enabling it to capture events occurring on the Tab Control.

The Set property procedure p_frm assigns the active form object to the frm property, which is declared with Private scope. The corresponding Get procedure allows access to the form reference, enabling you to address other controls on the form.

For example:

frm.p_frm.Controls("UserName").Value = Environ("UserName")

Similarly, the Set property procedure assigns the Tab Control object to the Tb property, and the Get property procedure returns this Tab Control reference to the calling program, as explained above.

Finally, the Tab Page Change event is captured in the Tb_Change() subroutine, which executes the desired code when the user selects a different tab page. Note that clicking a tab page does not trigger a Click event—instead, it triggers the Change event. This is why we handle it using the Tb_Change() procedure.

Two string variables, msg and title, are declared to hold the text values that will be displayed in a message box. When a Tab Page is clicked, the Tab Page Change event is triggered and captured in the Tb_Change() subroutine. This subroutine displays a message to confirm that the event has been successfully captured and the code within it is being executed.

The tab control contains two pages, with page index numbers 0 and 1, and each page has a TextBox control.

When the Change event fires, the code checks the selected tab page index number.

If the first page (index 0) is selected, the Text Box on that page (named Computer) is updated with the computer’s name, using the frm object reference to access the control.

If the second page (index 1) is selected, the TextBox on that page is updated with the current Windows user name (Environ("UserName")).

Note: The exact procedure you write inside the Tb_Change() subroutine will depend on your project’s specific requirements for handling this event.

The Combo Box Class Module: ClsCombo

The Class Module Code for the Combo Box control is given below:

Option Compare Database
Option Explicit

Private WithEvents cbx As Access.ComboBox

Public Property Get p_cbx() As Access.ComboBox
 Set p_cbx = cbx
End Property

Public Property Set p_cbx(ByRef cbNewValue As Access.ComboBox)
 Set cbx = cbNewValue
End Property

Private Sub cbx_Click()
Dim vVal As Variant
Dim cboName As String

vVal = cbx.Value
cboName = cbx.Name

Select Case cbx
    Case "Combo10"
        'Code Goes here
    Case "Combo12"
        'Code Goes here
End Select

MsgBox "Clicked: " & vVal, , cboName
End Sub

The Combo Box Class Module declares a single property, cbx, with Private scope and the WithEvents keyword to capture any events fired from the Combo Box.

The Public Property Set procedure p_cbx() receives the Combo Box control reference from the Form and assigns it to the cbx property.

The Property Get procedure returns the Combo Box control reference to any calling procedure outside this class module.

The cbx_Click() subroutine captures the Click event of the Combo Box on the Form. Within this procedure, a Select Case structure determines which Combo Box triggered the event and then executes the appropriate block of code based on the control’s name.

If you want to capture other events—such as OnGotFocus, OnLostFocus, AfterUpdate, OnChange, and so on—you can create corresponding subroutines in the same class module (for example, cbx_GotFocus()) and write the required VBA code within them.

However, to make these events fire when they occur on the form, you must also enable them in the Derived Class Module.

Currently, the Click event subroutine simply displays a common message showing the item value selected from the Combo Box.

The List Box Class Module: ClsListBox

The List Box Class Module Code structure is similar to the Combo box Code and programmed to capture only the Click Event.

Option Compare Database
Option Explicit

Private WithEvents LstBox As Access.ListBox

Public Property Get p_LstBox() As Access.ListBox
 Set p_LstBox = LstBox
End Property

Public Property Set p_LstBox(ByRef pNewValue As Access.ListBox)
 Set LstBox = pNewValue
End Property

Private Sub LstBox_Click()
Dim vVal As Variant
Dim lst As String

vVal = LstBox.Value
lst = LstBox.Name

Select Case lst
    Case "List16"
        'Code
    Case "List18"
        'Code
End Select

  MsgBox "Clicked: " & vVal, , lst

End Sub

It can be modified with additional sub-routines to capture any other Event Raised on the List Box. The existing Code works on similar lines to the Combo Box and displays the selected item value in the message box.

The Class Module for Options Group: ClsOption

The Options Group has three different styles: Option Buttons, Check Boxes, and Toggle Buttons. In our Demo Form, we have used only two of these styles — Option Buttons and Check Boxes. All three, however, work in the same way; the only difference is in their display style. Their control names start with the word Frame followed by a number, just like other controls on the form (e.g., Frame18, Text2, Combo10, List12, etc.).

The ClsOption Class Module Code is given below:

Option Compare Database
Option Explicit

Private WithEvents Opts As Access.OptionGroup

Public Property Get p_Opts() As Access.OptionGroup
  Set Opts = Opts
End Property

Public Property Set p_Opts(ByRef pNewValue As Access.OptionGroup)
  Set Opts = pNewValue
End Property

Private Sub Opts_Click()
Dim txtName As String, intVal As Integer
Dim msg As String, strVal As String

intVal = Opts.Value
strVal = Opts.Name

Select Case strVal
    Case "Frame25"
        Select Case intVal
            Case 1
                'code
            Case 2
                'Code
            Case 3
                'Code
        End Select
    Case "Frame34"
        Select Case intVal
            Case 1
                'Code
            Case 2
                'Code
            Case 3
                'Code
        End Select
End Select

msg = msg & " Click :" & intVal

MsgBox msg, , Opts.Name

End Sub

Option Group items have labels that indicate their actual purpose and meaning, and each item is assigned an index number starting from 1. In the Click event procedure, we check the selected item's index number to indicate what action to perform, such as opening a form, displaying a report, running a macro, or any other task.

Whenever an item is clicked, the frame’s events are triggered, and we use the selected index number to run the corresponding action.

Now, the Class Modules for all the controls on the Demo Form are ready.

Note: Some controls on the form may not be associated with any events and therefore will never fire an event. In such cases, we do not create Class Module instances for them. However, we can still read from or update the values of these controls from within the event procedures of other controls that do have Class Module instances.
Example: On the Tab Control pages, we have two Text Boxes that display the computer name and the Windows user name. These values are updated from the Tab Control’s Class Module, even though the Text Boxes themselves do not raise any events.
At this stage, we now have sample Class Modules (we can refer to them as Class Module Templates) for all types of controls on the form. Depending on the needs of a specific form in your project, you can create copies of the required Class Module Templates and customize their existing subroutines—or add new ones—to capture the necessary events and execute the related code.

The Derived Class Module: ClsControls_All

We now need an intermediary Class Module to act as a bridge between the stand-alone Class Modules for each type of control and the Form’s own Class Module. This intermediary will help organize all the control-specific Class Modules used on the form and also enable the required events for each control.

To achieve this, we will create a Derived Class Module that contains properties for all the different control-type Class Modules. It will scan the controls on the form, match them by their control names, and then enable the corresponding events by linking each control to its appropriate Class Module instance.

The Derived Class Module ClsControls_All Code is given below:

Option Compare Database
Option Explicit

Private tx As ClsText
Private cmd As ClsCmdButton
Private cbo As Clscombo
Private lst As ClsListBox
Private opt As ClsOption
Private tbc As ClsTabCtrl
Private Coll As Collection
Private fom As Access.Form

Public Property Get p_fom() As Access.Form
    Set p_fom = fom
End Property

Public Property Set p_fom(ByRef objFrm As Access.Form)
    Set fom = objFrm
    Class_init
End Property

Private Sub Class_init()
Dim ctl As Control
Const Evented = "[Event Procedure]"

Set Coll = New Collection

For Each ctl In fom.Controls 'check through Form controls

    Select Case TypeName(ctl) 'pick only the required control type
           Case "TextBox"
                Select Case ctl.Name
                    Case "Text2", "Text4", "Text6"
                        Set tx = New ClsText 'create new instance
                        Set tx.p_frm = fom 'assign Form Object to property
                        Set tx.p_txt = ctl 'assign control to p_txt Property
                        
                        tx.p_txt.AfterUpdate = Evented 'enable AfterUpdate Event
                        tx.p_txt.OnLostFocus = Evented 'enable LostFocus Event
                        
                'Add ClsText Object instance tx to Collection
                        Coll.Add tx
                'Release ClsText Object tx from memory
                        Set tx = Nothing
                End Select
                
            Case "TabControl"
                Set tbc = New ClsTabCtrl 'create instance of ClsTabCtrl
                Set tbc.p_frm = fom 'pass Form Object to p_frm Property
                Set tbc.p_Tb = ctl 'pass Tab Control to p_Tb Property
                
                tbc.p_Tb.OnChange = Evented 'enable OnChange Event
                
            'Add ClsTabCtrl instance tbc to Collection Object
                    Coll.Add tbc
            'Release tbc instance from memory
                Set tbc = Nothing
            
            Case "CommandButton"
                Select Case ctl.Name
                    Case "Command8", "Command9"
                    
                       Set cmd = New ClsCmdButton 'create new instance of ClsCmdButton
                       Set cmd.p_Btn = ctl ' pass Command Button control to p_Btn Property
                       
                       cmd.p_Btn.OnClick = Evented 'enable OnClick Event
                       
            'Add ClsCmdButton instance cmd to Collection Object
                      Coll.Add cmd
            'Release cmd instance from memory
                       Set cmd = Nothing
                End Select
                
            Case "ComboBox"
                Select Case ctl.Name
                    Case "Combo10", "Combo12"
                        Set cbo = New Clscombo ' create new instance of ClsCombo Class
                        Set cbo.p_cbx = ctl ' pass control (Combo10 or Combo12) to CB Property
                        
                        cbo.p_cbx.OnClick = Evented ' enable OnClick Event
                        
            'Add ClsCombo instance cbo to Collection Object
                       Coll.Add cbo
            'Release cbo instance from memory
                      Set cbo = Nothing
                End Select
                
            Case "ListBox"
                Select Case ctl.Name
                    Case "List14", "List16"
                      Set lst = New ClsListBox ' create new instance of ClsListBox
                        Set lst.p_LstBox = ctl ' pass the control to lst.LB Property of instance.
                        lst.p_LstBox.OnClick = Evented ' enable OnClick Event
                        
            'Add lst instance to Collection Object
                       Coll.Add lst
            'Release lst instance from memory
                      Set lst = Nothing
                End Select
                
            Case "OptionGroup"
                Select Case ctl.Name
                    Case "Frame25", "Frame34"
                      Set opt = New ClsOption ' create new instance
                        Set opt.p_Opts = ctl  ' pass control to opt.OB Property
                        
                        opt.p_Opts.OnClick = Evented ' enable OnClick Event
                        
            'Add opt instance to Collection Object
                       Coll.Add opt
            'Release lst instance from memory
                      Set opt = Nothing
                End Select
    End Select
    
Next

End Sub

In the global declaration area of the ClsControls_All Class Module, we have declared the Class Module objects of all the controls on the form with Private scope. We have also declared a Collection object and a Form object as properties of this class.

From our earlier trial runs, we learned that using a Collection object is a simpler and more efficient approach than using arrays of Class Module instances. The array method requires maintaining separate index counters for each control type, incrementing them for each new object instance, and repeatedly resizing the arrays to add new elements.

By contrast, adding each instance of a control-specific Class Module directly to a single Collection object is much easier and eliminates all extra steps.

The Set property procedure assigns the Form object reference, passed from the active form, to the Fom property in this class.

The Class_Init() subroutine is called from the Set property procedure to enable the events for each required control on the form. This ensures that when an event occurs, it is captured by the corresponding subroutines in their respective Class Modules.

The Get property procedure handles external requests for the Form object reference stored in this class.

Inside Class_Init(), a Control object (ctl) is declared to iterate through the form’s controls, and a string constant named Evented is set to the value "[Event Procedure]", which is used when assigning event procedures to control properties.

The Collection object (Coll) is then instantiated to store the Class Module instances of the controls.

Each control on the form—such as Text Boxes, Tab Controls, and others—is checked, and if it requires event handling (like AfterUpdate, LostFocus, or Click), a new instance of its corresponding Class Module is created, events are enabled, and the instance is added to the Coll collection object.

Remember, each Form control’s Object Property—such as a TextBox—was declared with the WithEvents keyword in its corresponding Class Module. This allows the Class Module instance to capture and handle events that occur on the control at runtime. When an event occurs, it is caught by the Class Module object instance stored in the Collection, and the relevant subroutine code is executed.

In the Class_Init() subroutine, a For Each ... Next loop iterates through all controls on the form. Each control is tested for its type—such as TextBox, TabControl, ComboBox, and so on. Controls that are not event-driven, like Labels, Images, or ActiveX objects, are skipped.

Within each control type, an additional check is performed on the control’s Name property to identify which controls should be wired to events.

For example, when a control of type TextBox is found, its Name is checked against "Text2", "Text4", and "Text6".

If the control matches one of these names, a new ClsText Class Module instance (e.g., tx) is created.
The active form reference (fom) is assigned to the tx.p_frm property, and the TextBox control reference (ctl) is assigned to the tx.p_txt property.
The specific events for that control—such as AfterUpdate and LostFocus—are then enabled.

Finally, this initialized ClsText object (tx) is added as an item to the Collection object, so that when any of these events fire, they are captured and handled through their Class Module instance.

The same process is repeated for Text4 and Text6. If each TextBox requires different events to be enabled, then they must be handled in separate Case statements. Each control is configured with its specific events, and a new instance of the corresponding Class Object is created and added to the Collection.

However, since all three TextBoxes (Text2, Text4, and Text6) are enabled with the same AfterUpdate and LostFocus events, their names are grouped within a single Case statement for convenience.

Note: There are two additional TextBoxes, one on each Tab Page. Although they are part of the form’s controls, they are not enabled with any events. Instead, they are used to display values dynamically during the execution of the Tab Control’s Change() event procedure.

The Tab Control does not raise a Click event when its pages are selected; instead, it triggers a Change event. By default, TabCtl18.Pages(0) is the current page.

When the user switches to 'TabCtl18.Pages(1)', the TextBox named UserName is updated with the Windows user name.

When switching back to the first page, the TextBox named Computer is updated with the computer’s name, using the statement:

frm.Controls("Computer").Value = Environ("ComputerName")

To enable this, the ClsTabCtrl Class Module includes a Form object property (frm) that allows the class to directly reference and update these TextBox controls on the form.

All other controls on the form—Command Buttons, Combo Boxes, List Boxes, and Option Groups—are currently enabled only for the click event. Their respective Class Modules contain only Click event procedures to capture the event and display a message for demonstration purposes.

If you want to capture any additional events from these controls, simply add the corresponding event procedures (e.g., GotFocus, AfterUpdate, Change) in their respective Class Modules, and then enable those events in the derived Class Module ClsControls_All.

The Form: frmControls_All's Class Module Code

Option Compare Database
Option Explicit

Private A As New ClsControls_All

Private Sub Form_Load()
    Set A.p_fom = Me
End Sub

The derived class object ClsControls_All is declared and instantiated as the object A.

The current form object is then passed as a parameter to the A.p_fom property procedure.

This is the only code required in the form’s class module to initialize and connect all the individual control class modules through the ClsControls_All class.

The Demo Run

Download the Demo database from the download link given at the end of this Article.

When you open the Demo Database, the Form frmControls_All opens in normal View by default.

Testing Text Box – AfterUpdate, LostFocus Events.

Press the Tab key while the insertion point is in the first TextBox to trigger its LostFocus event. When this event fires, the TextBox is automatically filled with the text “msaccesstips.com”. This approach is useful for inserting default text when a field should not be left empty.

Next, modify the text by adding or removing some characters, and then press Tab again. This time, the AfterUpdate event will fire, and a message box will appear displaying the updated text.

The other two TextBoxes on the form respond to these events in the same way.

Note: The default text “msaccesstips.com” is inserted only if the TextBox is left empty when it loses focus. If you type any value into the TextBox and then press Tab, both the AfterUpdate and LostFocus events will fire sequentially.

Testing Tab Control Page Click Event.

By default, the first Tab Control Page will be the active page. Click on the second Tab Control Page. The Change Event fires, and the TextBox on the page is updated with the Windows User Name.

Click on the first Tab Page. The text box on the first tab page is updated with the Computer Name.

**Command Button Click Event.**

Click on the top Command Button. This will open Form1 displaying some text, with hyperlinks to this Website.

The second Command Button Click displays a message from the ClsText Class Module instance Item from the Collection Object.

Click Events of ComboBox, ListBox, Option Group

All the controls on the frmControls_All form are enabled with the Click event through the Derived Class Module (ClsControls_All). Clicking any of these controls will display the selected item in a message box.

The event-enabled subroutines in these class modules are provided purely to demonstrate the programming approach.

We have developed a systematic and customizable Class Module template that makes VBA coding much easier than before. When starting a new project, you can simply copy this template and customize it to fit your project’s requirements. This approach not only simplifies debugging but also helps you quickly locate and fix any issues during field testing or while resolving logical errors in your project.

The Functional Diagram.

But before moving on, if you haven’t fully understood how all the pieces of this puzzle fit together and how they interact in their respective roles, take a moment to closely examine the diagram below.

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

I suggest revisiting the opening pages of this series; the links are provided at the bottom of this page.

If you have a ready-to-use Access database, make a copy of it and try restructuring the code based on what you’ve learned here. You’ll notice the difference when your project becomes better organized and easier to manage.