Introduction

So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and capture in stand alone Class Module, in various ways.  We have done demo runs to capture Events Raised from Form, like: AfterUpdate and LostFocus, in Class Module from several Text Boxes. 

The Class Object instance, one for each Text Box on the Form, is created and enabled with the required Event and added into Object Array elements or to Collection Object or as Dictionary Object Items.

Assume that we have five text boxes on our Form.  Out of that three text boxes need some Event to run, to execute some task related to the data on that text box, like validation checks on data.  In that case we need three instance of the ClsText Class Module , one instance for each text box,  Other text boxes are left out.

When the enabled Event fires, from the Text Box on the Form, the related Class Object Array Element captures the Event and executes the Code within the Event Procedure. 

Last week we have added the Command Button control and it's Class Module along with Text Boxes 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:

Besides Text Boxes and Command Buttons, we have Tab Control, Combo Boxes, List Boxes and Option Group controls.

We need new Class Module for each type of new controls on the Demo Form.  We already have Class Modules for Text Boxes and Command Buttons.

TextBox and CommandButton Class Modules.

Text Box and Command Button Class Module Code and their write-up is already posted in an earlier Article.  To reduce the size of this Post I will omit those details here.

You may visit directly to those areas of the Post, to view the VBA Code and their write-up, by selecting the Bookmark links given below:

1. Class Module: ClsText
2. Class Module: ClsCmdButton
3. Download: You may download the Demo Database given there, run the sample Form and try out the controls on the From.  Explore the methods and the VBA Code implemented there.

Here, what we do is an extension of that with more controls 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 Access.Form and Access.Tab-Control Properties in Objects frm and Tb respectively.  The Tab Control property is declared with the Key word WithEvents to capture the Events taking place on the control.

The Set Property Procedures p_frm assigns the Form Object to the frm Property, declared with Private Scope.  The Get Property procedure provides the Form reference to address any other control on the Form, like frm.p_frm.Controls("UserName").value = Environ("UserName")

The next Set Property Procedures assigns the Tab Control Object to the Tb Property.  Similarly, the Get Property Procedures provides the Tab-Control Object reference to the calling Program, as explained in the above paragraph.

Next, the Tab Page Click Event is captured in the Tb_Change() Sub-Routine and executes the Code there.  When the User Clicks on a particular Tab Control Page the Click Event is not fired instead  the Change Event takes place.  That is the reason why we have added the Tb_Change() Procedure.

Two string variables msg and title are declared to assign some string values to display in the  Message Box. When the Tab Page Click occurs the Tab Page Change Event fires and this is captured in the Tb_Change() sub-routine.  The sub-routine displays a message, indicating that the Event is  captured and executed the code in the sub-routine.

There are two Pages, on the Tab-Control, with Page index number 0 and 1.  There is a Text Box on each Page of the Tab Control.

When the Page Change Event fires we check for the Tab Page index number. If the first Page is clicked (with page index number 0), then the Text Box on that page will be updated with the Computer's Name.  Here, we use the frm Object reference to address the Text Box (with the name Computer)  on the first Tab Page and updates with the Computer's name.

The second page click will update the Text Box with the User Name (Window's UserName).

NB: The actual procedure you write on the Tb_Change() sub-routine depends on what you want to do on that Event in your Project.

The Combo Box Class Module: ClsCombo

The Class Module Code for 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 Class Module for Combo Box declares a single Property cbx Object, with Private Scope and with the key word WithEvents to capture the Events fired from the Combo Box.

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

The Get Property Procedure passes the reference of the control to the calling program outside this module.

The next sub-routine cbx_Click() captures the Click Event of the Combo Box on the Form. The Select Case structure tests for the source of the Click Event, fired from which Combo Box and accordingly the Code executes under that combo box name. 

If you would like to capture any other Events, like OnGotFocus, OnLostFocus, AfterUpdate, OnChange etc. you can write sub-routines in the same Class Module, like cbx_GotFocus() and write the required VBA Code.  Need to enable the Event in the Derived Class Module to fire the Event as and when it occurs on the Form.

The Click Event sub-routine displays a common message, with 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 of the Combo Box and displays the selected item value in the message box.

The Class Module for Option Group: ClsOption

The Option Group have three different styles: 1. Option Buttons, 2. Check Boxes, 3. Toggle Buttons.  In our Demo Form we have used only two styles: Option Buttons and Check Boxes.  But, all the three works the same way only the display style is different and their Control names start with the name Frame followed by a number, like any other control on the Form: 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 gives their actual purpose and meaning but all items have index numbers starting with 1.  In the Click Event Procedure we check for the Item index number to determine what to do, like Open a Form or Display Report or Run a Macro or whatever you want to do. 

It always fires the Frame Events and we check for the selected index number.to run a related action.

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

Note:  There may be controls on the Form which are not enabled with any Event and doesn't fire any Event.  In those cases we don't create the Class Module instances for them.  But, we may read or update those control's values from other Control's Class Module instances. 

Example: We have two Text Boxes on the Tab Control Pages.  These Text Boxes are updated with Computer Name and Windows User Name, from the Tab Control's Class Module.

Now, we have all the sample Class Modules (let us call them the Class Module Templates) for all type of Controls on the Form. Depending on the requirement of a particular Form in your Project create a copy of the required Class Module Templates and customize their existing sub-routine or add new ones to capture required Events and run the related Code.

The Derived Class Module: ClsControls_All

We need an intermediary Class Module, between the stand alone Class Modules of each type of controls and the Form's Class Module,  to organize the Class Modules for the Controls on the Form and  to enable the required Events for them.

We will create a Derived Class Module with all type of controls' Class Modules as it's Properties.  The required Events will be enabled by testing their control names on the Form.

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 Dictionary with control name as Key
                        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 Dictionary 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 Dictionary 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 Dictionary 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 Dictionary 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 Dictionary Object
                       Coll.Add opt
            'Release lst instance from memory
                      Set opt = Nothing
                End Select
    End Select

Next

End Sub

On the Global declaration area of Class Module ClsControls_All  we have added Class Module of all controls on the Form as Objects with Private Scope.  We have declared a Collection Object and a Form Object as well.

With our earlier trial run experience we have learned that the Collection Object is the easiest and better option than the Class Object instance Arrays.  The Array method  needs separate index counters for each type of control Class Module Objects.  For every new instance of an Object we have to increment the index counters, re-dimension the Array for new Element and so on.

Adding each instance of different type of Control's Class Module to Collection Object is easier and alleviates the need for all the extra steps  mentioned above. 

The Set Property Procedures assigns the Form Object, passed from the active Form, to the fom Property. 

The Class_Init() sub-routine is called from the Set property procedure to enable the Events on each required control on the Form, so that when the Event fires it is captured in their Class Module Sub-Routines.

The Get Property procedure services the outside request for the Form Object. 

The Class_init() sub-routine declares a Control Object ctl and a string constant Evented.

The Collection Object is instantiated as the Object Coll.

The controls on Form, like Text Box, Tab Control etc. will be enabled with required Events, like AfterUpdate, LostFocus, Click or any other on those control's Class Module Object and then add those instances to the Collection Object Item. 

Remember, the Form Control Object Property, like Text Box was declared with the WithEvents key word, enabling it to capture the Event, when it occurs on the Controls on the Form.  When those Event occurs it is captured in it's Class Module Object instance in the Collection Object and executes the sub-routine code, related to that event.

The For Each . . .Next Loop takes each control on the Form and tests whether it is the required type, like TextBox, TabControl, ComboBox and others.  Controls like Labels, images, activex controls etc., if any, are ignored.

Further the control Name check is performed, within a particular Type of Controls, to enable the required Event for that object.  First we check for the Text Box controls with the names Text2, Text4, Text6. When one of these Text Box control is found the ClsText Class Module is instantiated as tx Object.  The Form object fom is passed to the tx.p_frm property of  tx object. The tx.p_txt  object property is assigned with the Text Control ctl.

In the next step Text2 Text Box is enabled with the AfterUpdate and LostFocus Events. After these initialization steps the ClsText Class Object tx is added to Collection Object Item.

The same process is repeated for Text4 and Text6.  If each Text Box needs different Event to be enabled then they must be put under different Case statements and enable the required Event and add a new instance of the Class Object to the Collection Object.

Since, all the three Text Boxes are enabled with the same AfterUpdate and OnLostFocus Events all their names are put within the same Case statement. 

Note:  There are two more Text Boxes, one each on both Tab Pages.  Even though they are part of the Form controls we have not enabled them with any Events.  They will be used for displaying some values during the execution of Tab Page Change() Event procedure.

The Tab Control's Page Click action runs the Change Event, not Click Event.  By default the first TabCtl18.pages(0) will be current.  When you click on the second TabCtl18.pages(1) the Text Box (with the name UserName) will be updated with the Windows User Name.  When the first tab page is clicked the Text Box on it will be updated with the Computer's Name, with the statement frm.Controls("Computer").Value = Environ("ComputerName") in the ClsTabCtrl.  To address these text box controls directly we have added a Form object frm Property to the Class Module ClsTabCtrl.

All other controls on the Form, Command Buttons,  Combo Boxes, List Boxes and Option Group are enabled with the Click Event only.  Their Class Modules have only Click Event Sub-Routines to capture and display a message for demo purposes. 

If you would like to capture some other Event from a control then add the sub-routine for that event in it's Class Module and enable it 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 Object A.

The current Form Object is passed to the A.p_fom Property Procedure as it's parameter.  That is the only Code required on the Form's Class Module.

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 frmControlls_All opens in normal View, by default.

Testing Text Box – AfterUpdate, LostFocus Events

Tap on the Tab Key when the insertion point is on the first Text Box, to fire the LostFocus Event.  The Text Box will be inserted with the Text: msaccesstips.com.  This method is good for inserting some default text, if the data field rule is 'not to leave the Text Field empty'.

Make some changes to the text, by adding/removing some text, and then press Tab Key again.  This time the AfterUpdate Event fires and a message box is displayed with the changed text.

The Next two Text Boxes also responds to these Events similarly.

When you press the Tab Key in the Text Box the default text msaccesstips.com is inserted, only when you leave it empty.  If you type some value into the Text Box and press Tab Key then both AfterUpdate and LostFocus Event fires one after other.  But the lostfocus Event will be silent.

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 Text Box 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 these controls, on the Form frmControls_All , are enabled with the Click Event through the Derived Class Module ClsControls_All and clicking on them will display the selected item in a Message Box.

All the above Class Module based Event enabled Sub-Routine Code is for demonstration of the programming approach only. 

We have developed systematic and customizable Class Module Templates to make VBA Coding much easier than before.  When you start working on a new Project you can make a copy of this Class Module templates and customize it quickly, as per the current project's requirement.  It is easier to debug the code and you know where to look for issues that you may encounter on the field testing stage or while debugging logical errors of your Project.

The Functional Diagram

But, before that if you have not properly understood the intricacies or the arrangement of all the pieces of the puzzle and how they are all related each other in their functions then take a look closely at the diagram below.

I suggest you better take a second look from the beginning Pages of this Series.  The links are given at the bottom of this page. 

If you have a ready to use Access Database then make a copy of it and try to restructure the coding based on what you have learned here.  You will know the difference, when it becomes better organized and easily manageable.

Demo Database

You may download the demo database from the link given below and try out the Form as explained above.

1. WithEvents Ms-Access Class Module Tutorial
2. WithEvents and Defining Your Own Events
3. withevents Button Combo List TextBox Tab
4. Access Form Control Arrays and Event Capturing
5. Access Form Control Arrays and Event-2
6. Access Form Control Arrays and Event-3
7. WithEvents in Class Module for Sub-Form TextBox Events
8. WithEvents in Class Module and Data Entry
9. WithEvents and Access Report Event Sink
10. WithEvents and Report Lines Hiding
11. WithEvents and Report Lines Highlighting
12. Withevents TextBox and Command Button Arrays
13. Withevents TextBox CommandButton Dictionary