Access Form Control Arrays and Event-3

Best Microsoft Access Programmer Portland OR

Access Form Control Arrays and Event-3

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

Last week we have stopped short of discussing how to move all the VBA Code from the Form_Load() Event Procedure into another Class Module and leave the Form Module almost empty of all Event Procedures.  The VBA Code that defines the Text Box Control Class Module Array and invoking 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 have 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 Text Boxes are the main controls used on Form.  Text Box have 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 one 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 Text Box Class Module Template with most frequently used Event Procedures.  Create a copy and customize it for 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 type of control Arrays on the same Form.

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

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 Form to modify the Code so that you will have both version 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.

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.  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 un-altered.  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 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 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 it’s Properties and Property Procedures are 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.

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

Open the Form frmTxtArray1_3Header in 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 with 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 Text Boxes and ensure that their Event sub-routines are performing as expected.

The TextBox Class Object Array method works fine for several Text Boxes.  But, to create an Array, it should have a counter variable, Re-dimension the array for 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, Commbo Boxes 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 situation is to use the Collection Object, in place of Array.  We will run a demo here itself with the Text Boxes to get a feel of 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 it’s Item.

This method is repeated by adding new instance of the TextBox Class Object for  each Text Box on the Form, with it’s 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 Text Boxes as before.

It should work as before.

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

  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

Microsoft Acess Developer Portland OR