August 2019 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

So far we have worked extensively with only one type of Control: the TextBoxes on Form enabled with Events to Raise the Event and capture in the 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 TextBoxes.

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 in that text box, like validation checks on data. In that case, we need three instances 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 its Class Module along with 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:

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

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

The TextBox and CommandButton Class Modules.

Text Box and Command Button Class Module Code and their write-up were 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:

Class Module: ClsText
Class Module: ClsCmdButton
Download: You may download the Demo Database given there, run the sample Form, and try out the controls on the Form. 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 Keyword WithEvents to capture the Events taking place in 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 provide 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 variable names 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 in the code in the sub-routine.

There are two Pages, on the Tab-Control, with Page index numbers 0 and 1. There is a TextBox 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 update it 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 in 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 keyword 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, and others, you can write subroutines 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 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: 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 three work 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 give 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 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 are 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 types 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 control 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 types of controls' Class Modules as its 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 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

On the Global declaration area of Class Module ClsControls_All, we have added the 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 easier, and better option than the Class Object instance Arrays. The Array method needs separate index counters for each type of control Class Module Object. For every new instance of an object, we have to increment the index counters, re-dimension the Array's new Element, and so on.

Adding each instance of a different type of Control's Class Module to the 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() subroutine 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() subroutine declares a Control Object ctl and a string constant with the name Evented.

The Collection Object is instantiated as the Object Coll.

The controls on the 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's Object-Property, like Text Box, was declared with the WithEvents keyword, enabling it to capture the event, when it occurs on the Controls on the Form. When that event occurs, it is captured in its Class Module Object instance, in the Collection Object and executes the subroutine 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 control, to enable the required Event for that object. First, we check for the TextBox controls with the names Text2, Text4, and Text6. When one of these TextBox control is found the ClsText Class Module is instantiated as the tx Object. The Form object fom is passed to the tx.p_frm property of the tx object. The tx.p_txt object 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 the Collection Object Item.

The same process is repeated for Text4 and Text6. If each Text Box needs a 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 three TextBoxes 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 the 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 Options 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 any other Event from control, then add the sub-routine for that event in its 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 in the A.p_fom Property Procedure as a 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 the Tab Key again. This time the AfterUpdate Event fires and a message box is displayed with the changed text.

The next two TextBoxes also respond to these Events similarly.

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

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 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 a systematic and customizable Class Module Template 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 template 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 during 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 of the arrangement of all the pieces of the puzzle and how they are all related to each other in their functions then take a look closely at the diagram below.

I suggest you better take a second look at 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.