Access Form Control Arrays and Event-3

Introduction

This is the continuation of last Week’s Topic on Text Box AfterUpdate and LostFocus Event capturing and performing Validation Checks on values, in the Class Module Array.

Last week we stopped short of discussing how to move all the VBA codes from the Form_Load() Event Procedure into another Class Module and leave the Form Module almost empty of all Event Procedures.  The VBA code defines the Text Box Control Class Module Array and invokes the required built-in Events in their respective Array elements.  This method will need only three or four lines of Code on the Form Module and will take care of all the actions in a Derived Class Module Object.

Earlier we created Derived Class Objects, using Class Module as Base Class to extend the functionality of the Base Class.  We will do that here too.

We have taken the Text Box Control first, instead of any other Control on the Form, for the Array-based examples because TextBoxes are the main controls used on Form.  The TextBox has several Events, like BeforeUpdate, AfterUpdate, OnLostFocus, OnEnter, OnExit, KeyDown, KeyUp, OnKey, and several others, and depending on the requirements we can invoke one or more of the above Events in the Derived Class Object. 

We can write some standard Event Procedures in the Class Module for all of the above sample Events in the Text Box’s Class Module.  But, invoke only those required ones with the statement  obj.txt.EventName = “[Event Procedure]” during the Array element declaration time. 

The Class Module Event Sub-Routines may require customization of Code for different Form’s Text Box control values.  The best approach is to create a TextBox Class Module Template with the most frequently used Event Procedures.  Create a copy and customize it for a specific requirement.

Other Controls on the Form, like Command Buttons, Combo Boxes, List Boxes, and others, mostly use the Click or Double-Click Events only. We will take up this topic of managing different types of control Arrays on the same Form.

We will explore the possibility of better ways than Arrays to manage instances of different types of  Controls on the Form.

Moving Form's Class Module Code to Derived Class Module

Coming back to today’s topic, moving the Form Module Code to a separate Class Module, we will create a Derived Class Module Object using the ClsTxtArray1_2 Class Module as Base Class.  Move the Form Module Form_Load() Event Procedure Code into the new Class Object.

If you have not downloaded last week’s Demo Database then download it from the following link, before continuing.  We will make copies of the Modules and Forms to modify the Code so that you will have both versions of the Code and Form on the same database.  After making changes to the Code and Form Module you can instantly run and see how it works.

Download TxtArray2_2007.zip

Download TxtArray2_2003.zip

After downloading the database Open it in Ms-Access.  You may open the Form Module and check the Code.

We have to copy the Class Module (ClsTxtArray1_2) Code into a new Class Module with a different name ClsTxtArray1_3 but without any change in the code.  The form also must be copied with a different name frmTxtArray1_3Header.  The changes, if any, will be done in the new Copy leaving the original safe and unaltered.  This way the earlier Form and Class module will remain safe.

We will use last week’s sample Form (the image is given below) and the Form Module VBA Code also reproduced below for reference.

Option Compare Database
Option Explicit

Private Ta() As New ClsTxtArray1_2

Private Sub Form_Load()
Dim cnt As Integer
Dim ctl As Control

For Each ctl In Me.Controls
  If TypeName(ctl) = "TextBox" Then
     cnt = cnt + 1
     ReDim Preserve Ta(1 To cnt)
     Set Ta(cnt).Txt = ctl
     
     If ctl.Name = "Text8" Then
       Ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     Else
       Ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     End If
     
  End If
Next
End Sub

Make a Copy of the above Form and name it as frmTxtArray1_3Header.

Create a new Class Module with the name ClsTxtArray1_3.  Copy the VBA Code from ClsTxtArray1_2 Class Module and Paste it into the new Module.

Last week’s Class Module ClsTxtArray1_2  Code is reproduced below for reference.

Option Compare Database
Option Explicit

Private WithEvents Txt As Access.TextBox

Public Property Get mTxt() As Access.TextBox
  Set mTxt = Txt
End Property

Public Property Set mTxt(ByRef txtNewValue As Access.TextBox)
  Set Txt = txtNewValue
End Property

Private Sub Txt_AfterUpdate()
Dim txtName As String, varVal As Variant
Dim msg As String

txtName = Txt.Name
msg = ""
Select Case txtName
    Case "Text0"
        'Valid value range 1 to 5 only
        varVal = Nz(Txt.Value, 0)
        If varVal < 1 Or varVal > 5 Then
           msg = "Valid Value Range 1-5 only: " & varVal
        End If
    Case "Text8"
        'validates in LostFocus Event
    Case "Text10"
        'valid value 10 characters or less
        'Removes extra characters, if entered
        varVal = Nz(Txt.Value, "")
        If Len(varVal) > 10 Then
           msg = "Max 10 Characters Only. " & varVal
           Txt.Value = Left(varVal, 10)
        End If
    Case "Text12"
        'Date must be <= today
        'Future date will be replaced with Today's date
        varVal = DateValue(Txt.Value)
        If varVal > Date Then
          msg = "Future Date Invalid. " & varVal & vbCr & "Corrected to Today's Date."
          Txt.Value = Date
        End If
    Case "Text14"
        'A 10 digit number only valid
        varVal = Trim(Str(Nz(Txt.Value, 0)))
        If Len(varVal) <> 10 Then
          msg = "Invalid Mobile Number: " & varVal
        End If
End Select

If Len(msg) > 0 Then
    MsgBox msg, vbInformation, Txt.Name
End If

End Sub

Private Sub Txt_LostFocus()
Dim tbx As Variant, msg As String

tbx = Nz(Txt.Value, "")

msg = ""
If Len(tbx) = 0 Then
  msg = Txt.Name & " cannot be left Empty."
  Txt.Value = "XXXXXXXXXX"
End If

If Len(msg) > 0 Then
   MsgBox msg, vbInformation, Txt.Name
End If

End Sub

The Derived Class: ClsTxtArray1_3Header

The ClsTxtArray1_3 Class Module will be used as the Base Class for our new Derived Class Module, we will name it as ClsTxtArray1_3Header, with extended functionality.

Create a new Class Module with the name ClsTxtArray1_3Header. The Derived Class Module, with its Properties and Property Procedures, is given below:

Option Compare Database
Option Explicit

Private Ta() As New ClsTxtArray1_3
Private frm As Access.Form

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

Public Property Set mFrm(vFrm As Access.Form)
  Set frm = vFrm
  Call Class_Init
End Property

Private Sub Class_Init()
 'Form Module Code goes here
End Sub

Copy and paste the above code into the new Header Class Module you have created.

Check the first two Property declarations.  First Property ClsTxtArray1_3 Class Object is instantiated as an undefined Array: Ta() – Ta stands for TextBox-Array.

The next Property frm is to take control of the Form, from where we plan to transfer the VBA Code here and whatever we did there will be done here. 

Next Get/Set Property Procedure will take care of the Form’s references.  It is  Set Property Procedure not Let, because we are passing a Form Object to it.

Immediately after the Form’s reference is received in the Set Property Procedure we call the Class_Init() (this is not the same as Class_Initialize() that runs automatically when a Class Object is instantiated) sub-routine to run the same code moved here from the Form’s Module.

Now, we will transfer the following Code from the Form_Load() Event Procedure into the Class_Init() sub-routine and make changes in the Form Module.

Copy and Paste the following lines of code from the Form Module into the Class_init() sub-routine, replacing the Comment line:

Dim cnt As Integer
Dim ctl As Control

For Each ctl In frm.Controls
  If TypeName(ctl) = "TextBox" Then
     cnt = cnt + 1
     ReDim Preserve Ta(1 To cnt)
     Set Ta(cnt).Txt = ctl
     
     Select Case ctl.Name
        Case "Text8"
            'Only LostFocus Event
            Ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     Case Else
            'All other text Boxes wiil trigger AfterUpdate Event
            'i.e. entering/editing value in textbox
            Ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     End Select
     
  End If
Next

Form's Class Module Code

Open the Form frmTxtArray1_3Header in the design view. Display the Code Module. Copy and Paste the following Code into the Form's Module, overwriting the existing Code:

Option Compare Database
Option Explicit

Private T As New ClsTxtArray1_3Header

Private Sub Form_Load()
  Set T.mFrm = Me
End Sub

We have instantiated the Derived Class ClsTxtArray1_3Header in Object Variable T,. With the statement Set T.mFrm = Me the active form's reference is passed to the T.mFrm() Set Property Procedure.

Immediately after this action, on the form_Load() Event procedure, the Class_Init() sub-routine runs in the ClsTxtArray1_3Header Class and the txtArray1_3 Class Object array elements are created by invoking Events for each Text Box on the Form.  Hope you are clear with the Code above.

If you are ready with modifying the Form Module, Compile the database to ensure that everything is in order.

Save and close the Form, Open it in Normal View and try out each TextBox and ensure that their Event sub-routines are performing as expected.

Replacing Class Object Array with Collection Object Items

The TextBox Class Object Array method works fine for several TextBoxes.  But, to create an Array, it should have a counter variable, Re-dimension the array for a new element preserving the data in earlier array elements.  Increment the counter variable for the next Text Box on the Form and so on. 

When there are several controls of the other types also on the Form (like Command Buttons, CommboBoxes, etc.)  we need to create Arrays of that Type’s Class Objects separately, with separate counter and re-dimension those controls Array too in the Class Module.  We will try these sometime later, to learn how to do it.

But, a better way to manage these complex situations is to use the Collection Object, in place of the Array.  We will run a demo here itself with the TextBoxes to get a feel for this method.

1. Create a new Derived Class Module with the name ClsTxtArray1_3Coll.
2. Copy and Paste the following Code into the Class Module:

Option Compare Database
Option Explicit

Private C As New Collection
Private Ta As ClsTxtArray1_3
Private frm As Access.Form

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

Public Property Set mFrm(vFrm As Access.Form)
  Set frm = vFrm
  Call Class_Init
End Property

Private Sub Class_Init()
'-----------------------------
'Usage of Collection Object, replacing Arrays
'-----------------------------
Dim ctl As Control

For Each ctl In frm.Controls
  If TypeName(ctl) = "TextBox" Then
     
     Set Ta = New ClsTxtArray1_3  'instantiate TextBox Class
     Set Ta.Txt = ctl 'pass control to Public Class Property
     
     Select Case ctl.Name
        Case "Text8"
            'Only LostFocus Event
            Ta.Txt.OnLostFocus = "[Event Procedure]"
     Case Else
            'All other text Boxes wiil trigger AfterUpdate Event
            'i.e. entering/editing value in textbox
            Ta.Txt.AfterUpdate = "[Event Procedure]"
     End Select
     C.Add Ta 'add to Collection Object
  End If
Next

End Sub

A Collection Object Property is declared and instantiated at the beginning. 

The TextBox Class Module is defined, not instantiated, in Object Variable Ta.

The TextBox Class Ta Object is instantiated within the Control Type Test condition.  A new Ta Object instance is created for each TextBox on the Form.

After enabling the Events the Ta Class Object is added to the Collection Object as its Item.

This method is repeated by adding a new instance of the TextBox class Object for each Text Box on the Form, with its required Events enabled, as a new Item to the Collection Object.  The Code is cleaner than the Array method.

Make a copy of the Form frmTxtArray1_3Header with the name frmTxtArray1_3Coll. 

1. Open it in Design View and display the Form's Code Module.
2. Copy and Paste the Following Code into the Form Module, replacing the existing Code.

Option Compare Database
Option Explicit

Private Ta As New ClsTxtArray1_3Coll

Private Sub Form_Load()
  Set Ta.mFrm = Me
End Sub

The only change here is the Derived Object's name change to ClstxtArray1_3Coll. Compile the database again.

Save the Form, Open it in normal View. Test the TextBoxes as before.

It should work as before.

Downloads

You may download the database with all the Modules and Forms with all the suggested changes.

Download TxtArrayHeader_2007.zip

Download TxtArrayHeader_2003.zip

Links to WithEvents ...Tutorials.

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
14. Withevents and all Form Control Types