Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

WithEvents Textbox CommandButton Dictionary

Continued from Last Week's Post

So far we have used the Collection Object , to hold all the Class Object instances of the Form Controls as an alternative to Class Object Arrays.

If you have directly landed on this Page then please go through last Week's Post: WithEvents TextBox and CommandButton Control Arrays and then continue on this page.

Last week we have used Class Object Arrays of Text Box and Command Button Controls on the Form.

The difficulty of this method was to maintain separate Array Indexes for each type of control's Class Object instances, besides re-dimensioning each Array element every time.  When there are several type of Form Controls, like Combo Boxes, List Boxes, Option Groups, Tab Control and others this approach will grow into a very complex situation.  Earlier, we were able to alleviate this complexity by using Collection Object, as the container of all the controls Class Object Instances.

We have tried few examples with Collection Object in some of our earlier Post.  Links of all the Posts, on Form and Report Control's Event Capturing topic, are given at the end of this Post for your reference.

Usage of Dictionary Object, Replacing Array

Here, we are going to use the Dictionary Object, instead of Collection Object, to hold all the Form Controls' Class Object Instances.  Let us see how it works and what it takes to implement the Dictionary method.

Here, I will bring in Last week's Derived Class Object (ClsTxtBtn_Derived)  Code without any change.  The full VBA Code of last week's Class Module Derived Object Code is given below:

Option Compare Database
Option Explicit

Private T() As New ClsText
Private B() As New ClsCmdButton
Private m_Frm As Access.Form

Public Property Get mfrm() As Access.Form
  Set mfrm = m_Frm
End Property

Public Property Set mfrm(ByRef frmObj As Access.Form)
  Set m_Frm = frmObj
  init_Class
End Property

Private Sub init_Class()
   Dim ctl As Control
   Dim tCount As Long
   Dim bCount As Long
Const Evented = "[Event Procedure]"

tCount = 0 'counter for textbox controls
bCount = 0 'counter for Command Button controls
For Each ctl In m_Frm.Controls
   Select Case TypeName(ctl) 'Type name TextBox or CommandButton ?
     Case "TextBox"
         tCount = tCount + 1 'increment counter
         ReDim Preserve T(1 To tCount)  'redimension TextBox Class Objecct Array
         Set T(tCount).p_frm = m_Frm 'pass Form Object
         Set T(tCount).p_txt = ctl 'pass the TextBox control
         
         Select Case ctl.Name
            Case "Text2" ' enable AfterUpdate and OnLostFocus Events
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text4"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text6"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
        End Select
            
      Case "CommandButton"
         bCount = bCount + 1 'increment counter for CommandButton
         ReDim Preserve B(1 To bCount) 'redimension Button Class Object Array
         Set B(bCount).p_Btn = ctl 'pass CommandButton control
        
        Select Case ctl.Name
            Case "Command8" 'Enable Click Event
                B(bCount).p_Btn.OnClick = Evented
            Case "Command9"
                B(bCount).p_Btn.OnClick = Evented
        End Select
      End Select
    Next
End Sub

The ClsText and ClsCmdButton Classes are defined as Array Objects T() & B() Properties, with an un-declared number of array elements.  The Form Property m_Frm declared as third item followed by the Get/Set Property Procedures for the Form Object. 

After assigning the Form Object in the Set Property Procedure the Class_Init() Sub-Routine is called to enable the required Form controls' Event Procedures.

Three Text Boxes (Text2, Text4, Text6) are enabled with the AfterUpdate() and LostFocus() Events only, because we didn't set up any other Sub-Routine in the Class Module ClsText to capture other Events from the Text Boxes.

Note: Even if we have sub-routine code for, say BeforeUpdate,  GotFocus, KeyDown, KeyUp etc., in the ClsText Class Module we may not use all of them for a particular Project.  Whatever Event we need to capture in a particular Form will only be enabled in the Derived Class Module.  Other sub-routines will remain in the Class Module till we encounter a need for them in a particular Form or Report.

The Form and TextBox control references are passed from the Derived Object to the ClsText Class Object Properties through the following statements:

Set T(tCount).p_frm = m_Frm
Set T(tCount).p_txt = ctl

Command Button Control reference is passed to the ClsCmdButton Class Module.  The Command Button Click Event is enabled.

This procedure repeated for each Control on the Form in the Derived Class Object Module: ClsTxtBtn_Derived.

Logical Error in VBA Code

The ClsTxtBtn_Derived Class Module Code works perfectly for our earlier example.  But, there is a logical error in the code and we will correct it in the later versions of the Code. 

The placement of the following lines of Code under the Case "TextBox are not in the correct location to place, because there is a chance that the Class Module Object will occupy more memory space.

Case "TextBox"
         tCount = tCount + 1 'increment counter
         ReDim Preserve T(1 To tCount)  'redimension TextBox Class Objecct Array
         Set T(tCount).p_frm = m_Frm 'pass Form Object
         Set T(tCount).p_txt = ctl 'pass the TextBox control

The issue with the above code placement is that if there are some extra text boxes on the Form, without any Event enabled on them, even then the Class Object will be instantiated for those cases also and added to the Array,  occupying extra memory space.  It will happen silently without any side effects.

The above code lines must be placed immediately below all the Case statements Case "Text2", Case "Text4" and Case "Text6" structures for the correction of the logical error.  This change is required for the Command Button Case statements also.  If all the Text Boxes and Command Buttons on the Form are enabled with some Event then no need for any change.

In all our demo Forms we have introduced two or three Text Boxes or few Command Buttons and enabled some Event on all of them.  In those situations the above Code placement is correct and intentionally made that logical position to keep the code simplified, avoiding duplication of code, under each Case Statement..

The Dictionary Object

If you have not come across the Dictionary Object and it's usage so far then please visit the following links or at least the first two:

  1. Dictionary Object Basics
  2. dictionary-object-basics-2
  3. Sorting Dictionary Object Keys and Items
  4. Display Records from Dictionary to Form
  5. Add Class Objects as Dictionary Items
  6. Update Class Object Dictionary Item

By Comparing Collection Object with Dictionary, both have the Add method and takes two parameter values: ItemKey and Item.

Dictionary Object Syntax: Object.Add ItemKey, Item – both values mandatory.

Collection Object  Syntax:  Object.Add Item, ItemKey – second parameter  optional.

Unlike Dictionary Object the ItemKey is second parameter in Collection Object and is optional. Since,  all the control names on the Form are unique it can be used as Dictionary Object ItemKey.

Dictionary Object is part of Windows Scripting Language and need to create an Object in VBA with the following statement:

Dim D as Object
Set D = CreateObject("Scripting.Dictionary")

Alternatively you can select and add the Microsoft Scripting Runtime Library File to your Project from Tools - - > References Library List.  After that you can declare Dictionary Object as shown below:

Dim D as Dictionary

If you do this then it has an added advantage of displaying it's list of Properties and Methods, when you type the declared Object name followed by a dot ( say D.)

The new derived Class Module ClsTxtBtn_Dictionary VBA Code is given belowSo far we have used Collection Object as an alternative to Class Object Instance Arrays.  Here we will learn the usage of Dictionary Object as container of Class Object Instances (of TextBox and Command Button).

Option Compare Database Option Explicit Private T As ClsText Private B As ClsCmdButton Private m_Frm As Access.Form

Private D As Object Public Property Get mfrm() As Access.Form Set mfrm = m_Frm End Property Public Property Set mfrm(ByRef frmObj As Access.Form) Set m_Frm = frmObj init_Class End Property Private Sub init_Class() Dim ctl As Control Const Evented = "[Event Procedure]"

Set D = CreateObject("Scripting.Dictionary")

For Each ctl In m_Frm.Controls Select Case TypeName(ctl) 'Type name TextBox or CommandButton ? Case "TextBox" Select Case ctl.Name Case "Text2" Set T = New ClsText 'create new instance of Class Module Set T.p_frm = m_Frm 'pass Form Object Set T.p_txt = ctl 'pass the TextBox control T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item Set T = Nothing 'erase ClsText instance Case "Text4" Set T = New ClsText Set T.p_frm = m_Frm Set T.p_txt = ctl T.p_txt.AfterUpdate = Evented T.p_txt.OnLostFocus = Evented 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T Set T = Nothing Case "Text6" Set T = New ClsText Set T.p_frm = m_Frm Set T.p_txt = ctl T.p_txt.AfterUpdate = Evented T.p_txt.OnLostFocus = Evented 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T Set T = Nothing End Select Case "CommandButton" Select Case ctl.Name Case "Command8" Set B = New ClsCmdButton 'create new instance of ClsCmdButton Set B.p_Btn = ctl 'pass CommandButton control to Class Module B.p_Btn.OnClick = Evented 'Enable Click Event 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item Set B = Nothing 'erase ClsCmdBtn instance Case "Command9" Set B = New ClsCmdButton Set B.p_Btn = ctl B.p_Btn.OnClick = Evented 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B Set B = Nothing End Select End Select Next End Sub

Preparing for a Trial Run

  1. Create a new Class Module in the Demo database, you have downloaded from last week's Post, with the name ClsTxtBtn_Dictionary.
  2. Copy and Paste the above Code into the Class Module and save.
  3. Select Compile . . . from the Debug Menu to recompile the database and ensure that no errors encountered.
  4. Make a Copy of the Form frmClassArray with a new name frmClass_Dictionary.
  5. Open the Form frmClass_Dictionary in design view.
  6. Display the Form's Code Module and change the Code to match the Code lines given below:
    Option Compare Database
    Option Explicit
    
    Private A As New ClsTxtBtn_Dictionary
    
    Private Sub Form_Load()
       Set A.mfrm = Me
    End Sub
    
    
  7. Save the Form with the changed code.
  8. Open the Form in Normal View and try the Text Boxes and Command Buttons to test the LostFocus, AfterUpdate and Command Button Clicks works as before.

Revised Code Segments.

Since, all the three Text Boxes (Text2, Text4 and Text6) are enabled with the same set of Events (AfterUpdate & LostFocus) the Case statements can be clubbed into one line and can avoid duplication of Code as given below:

Select Case ctl.Name
            Case "Text2", "Text4", "Text6"
                Set T = New ClsText 'create new instance of Class Module
                Set T.p_frm = m_Frm 'pass Form Object
                Set T.p_txt = ctl 'pass the TextBox control

                    T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event
                    T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event
                
          'Add ClsTxt Class Object as Dictionary Object Item
                    D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item
                Set T = Nothing 'erase ClsText instance
        End Select

Similarly both the Command buttons have only one common Event, the Click Event.  Hence, their Code also can be combined into a single step, like the following code segment:

Select Case ctl.Name
            Case "Command8", "Command9"
                Set B = New ClsCmdButton 'create new instance of ClsCmdButton
                Set B.p_Btn = ctl 'pass CommandButton control to Class Module
           
                    B.p_Btn.OnClick = Evented 'Enable Click Event
            'Add ClsCmdButton Class Instance as Dictionary Item
                    D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item
                Set B = Nothing 'erase ClsCmdBtn instance
        End Select

The Revised ClsTxtBtn_Dictionary Code.

The full Class Module Code with the above change is given below:

Option Compare Database Option Explicit Private T As ClsText Private B As ClsCmdButton Private m_Frm As Access.Form Private D As Object Public Property Get mfrm() As Access.Form Set mfrm = m_Frm End Property Public Property Set mfrm(ByRef frmObj As Access.Form) Set m_Frm = frmObj init_Class End Property Private Sub init_Class() Dim ctl As Control Const Evented = "[Event Procedure]"

Set D = CreateObject("Scripting.Dictionary")

For Each ctl In m_Frm.Controls Select Case TypeName(ctl) 'Type name TextBox or CommandButton ? Case "TextBox" Select Case ctl.Name Case "Text2", "Text4", "Text6" Set T = New ClsText 'create new instance of Class Module Set T.p_frm = m_Frm 'pass Form Object Set T.p_txt = ctl 'pass the TextBox control T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item Set T = Nothing 'erase ClsText instance End Select Case "CommandButton" Select Case ctl.Name 'Both Command Buttons have only the same Click Event Case "Command8", "Command9" Set B = New ClsCmdButton 'create new instance of ClsCmdButton Set B.p_Btn = ctl 'pass CommandButton control to Class Module B.p_Btn.OnClick = Evented 'Enable Click Event 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item Set B = Nothing 'erase ClsCmdBtn instance End Select End Select Next End Sub

You may create a new Class Module, Copy and Paste the above Code and save.  Change the Form Module Code to incorporate the new name of this Module and try out the Form controls, to test whether all of them works as before.

We have tried only with two type of controls, the Text Box and Command Button class Modules here.  How it will be when we have to deal with all type controls  Combo, List, Tab Control, Option Group on the Form.  All of them needs separate Class Modules, like ClsText and ClsCmdButton.  We will conclude this topic with one or two posts with almost all type of controls on the Form. 

If you have progressed through the earlier Posts (the links: No. 1 to 12) presented below then you will not find any difficulty in defining and setting up Class Modules for those controls mentioned above in a similar way, with Click Event enabled for each one of the yet to be tested form controls.


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
Share:

WithEvents TexBox and CommandButton Control Arrays

Introduction.

Hope that you have gone through last three Posts on Report based Event Trapping in Class Module and modifying Report controls in real-time.  The links of those Posts are given below for your ready reference, if needed:

  1. WithEvents and Access Report Event Sink
  2. WithEvents and Report Lines Hiding
  3. WithEvents and Report Lines Highlighting

So far we have worked with only Text Box Arrays to capture built-in Events from Form in Class Module  and executes the Sub-Routines, which handles the required validation checks or other needed functions.

TextBoxes Event Capturing Route Map.

  1. Creates a Class Module with a Text Box Control and Form Properties.
  2. Add Sub-Routine(s) for AfterUpdate() and LostFocus(), to handle these built-in Events from Text Box on the Form.  The Text Box name that triggered the Event is identified in the Sub-routine and the Code is executed under the Text Box Name.
  3. Creates a Derived Class Module using the Text Box Class Module as Base Class. 
  4. Creates an Array of the Base Class enabling the required Text Box Events in each Array element, within the Derived Class Object. 
  5. This will eliminate the need for keeping the AfterUpdate() and LostFocus() empty Sub-routine stubs on the Form Module, to Raise those Events on the Form Module, when it occurs.
  6. An Array of Class Module with Text Box Property txt , one array element for each Text Box on the Form, is required because there is only one txt Property in the Base Class Module.   It can hold only one Text Box reference at a time.
  7. Instead of Class Object Arrays,  adding Class Object instances as Collection Object Item is proved as a better Option to hold all the Text Box Class instances.

In brief our technique is to create Class Module, with required Event Procedures, for each type of  control, Text Box, Combo Box, List Box, Tab Control, Option Group separately.  We insert the required Class Module Objects as Base class Properties in the Derived Class Object, depending on the control types on the Form.

Another point to remember is that, if you need to refer to a different control of the same type, other than the one triggered the Event, you must add an Access.Form Object as Property in the Base Class Module and assign the Form Object to that Property through the Derived Class Object.

TextBox and CommandButton Arrays.

So far we have worked with Arrays of Class Module with TextBox Property only.  We will create few Text Boxes and Command Buttons on a Form and learn as how their references are being assigned to instances of  Text Box and Command Button Base Class Module Arrays, in the Derived Class Module.

A particular Class Object Array element captures the built-in Events of a particular Text Box or Command Button and executes the Event Procedure from within the Array Element.

Sample Image of the Form, with the name frmClassArray in Design View is given below:


There are three Text Box Controls on the Form, with the names Text2, Text4, Text6 (need three singly dimensioned array with three elements) and two Command Buttons with the name Command8 and Command9 (an Array of two elements) of their respective Class Modules.

Class Module for TextBox: ClsText.

The Class Module: ClsText Code for Text Box control is given below:

Option Compare Database
Option Explicit

Private frm As Access.Form
Private WithEvents txt As Access.TextBox

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

Public Property Set p_Frm(ByRef pNewValue As Access.Form)
   Set frm = pNewValue
End Property

Public Property Get p_txt() As Access.TextBox
   Set p_txt = txt
End Property

Public Property Set p_txt(ByRef ptxtValue As Access.TextBox)
   Set txt = ptxtValue
End Property

Private Sub txt_AfterUpdate() 'Capture AfterUpdate Event here
Dim txtName As String, varVal As Variant
Dim msg As String

txtName = txt.Name 'save TextBox name
msg = txtName 'save the same name in msg variable for msgbox
varVal = txt.Value 'read the value, if any, from TextBox

'write required code under textbox name
Select Case txtName
    Case "Text2"
        'Code
    Case "Text4"
        'code
    Case "Text6"
        'Code
End Select
'a common message displays for
'all TextBoxes for demo purposes.
msg = msg & ": " & varVal
MsgBox msg, , "AfterUpdate()"

End Sub

Private Sub txt_LostFocus() 'LostFocus actions from TextBoxes are captured here.
Dim txtName As String, varVal As Variant
Dim msg As String, strVal As String
Dim ctl As Control

txtName = txt.Name
varVal = txt.Value
strVal = Nz(varVal, "")

'if any textbox is empty
'then inserts the text: MSAccessTips.com
Select Case txtName
    Case "Text2" ' inserts MSAccessTips.com when empty.
        GoSub Check
    Case "Text4"
        GoSub Check
    Case "Text6"
        GoSub Check
End Select

Exit Sub

Check:
   If Len(strVal) = 0 Then
     txt.Value = "MsAccessTips.com"
   End If
Return

End Sub

The Access.Form Property frm is declared at the beginning of the Module followed by the Access.TextBox Property txt in the next line. 

The txt Property is declared with the key word WithEvents that enables to capture the Events (User-Defined or Built-in Events) taking place on the Form.

The frm Object Property is required to refer to a Text Box control, other than the Event Triggered one, if needed.  Both Properties are declared with Private Scope preventing direct access to the Properties from outside the Class Module.

The Get and Set Property Procedures p_Frm()   for frm Property and p_txt() for txt Property are next steps. The Active Form Object is assigned to the frm Property, through the Form_Load() Event Procedure, not directly but through the Derived Class Module ClsTxtBtn_Derived.

The p_txt() Property Procedures are called from the Derived Class Module (ClsTxtBtn_Derived – the VBA Code is given after the Command Button Class Module ClsCmdButton Code) and the Text Box controls are assigned to each array element of the ClsTxt Class Module.

Once the frm Property is assigned with the Form Object we can set a reference to any control on the Form to read or write values. 

Through the Event capturing Sub-Routines you can set reference to a different Text Box control on the Form to Read/Write value in the following manner:

'Assume Text2 is the active control now
Set mytxt = frm.Controls("Text6")

'Read value from mytxt
    mytxtvalue = mytxt.Value

'Write value to mytxt
    mytxt = 25

The txt_AfterUpdate() Subroutine handles the Text2, Text4, Text6 controls' Events in their respective array elements separately.  You may write validation checks on values in the textbox and display an appropriate message or replace a different value etc.  In this sample subroutine a common message is displayed with the Text Box name, to know that the Sub-Routine is executed on Event Triggering on the Form.

The txt_LostFocus() Event Procedure inserts the text msaccesstips.com, if the text box is left empty otherwise it will not show any message.

Note: When you try the demo run of the attached database tab through the text fields to insert the default text automatically then edit them and press Tab or Enter Key to trigger the AfterUpdate() Event.

Class Module of Command Button: ClsCmdButton.

The Command Button Class Module: ClsCmdButton VBA Code is given below:

Option Compare Database
Option Explicit

Private WithEvents Btn As Access.CommandButton

Public Property Get p_Btn() As Access.CommandButton
  Set p_Btn = Btn
End Property

Public Property Set p_Btn(pBtnValue As Access.CommandButton)
   Set Btn = pBtnValue
End Property

Private Sub Btn_Click()
Dim BtnName As String

BtnName = Btn.Name

Select Case BtnName
    Case "Command8"
        DoCmd.OpenForm "Form1", acNormal
    Case "Command9"
        MsgBox "Thank you, " & BtnName & " Clicked.", , BtnName
End Select

End Sub

The Btn Property is declared with Private Scope and with the key word WithEvents to capture Events, when they fire from the Command Button on the Form.

The Get/Set Property Procedures (p_Btn()) retrieves/assigns the Command Button Object to the Property.

In the Btn_Click() Event Procedure we check the name of the Command Button to determine what to do if a particular Command Button is clicked.

Here, Command8 button click will open a form: Form1.

Command9 button click will only display a message.

The Derived Class Module: ClsTxtBtn_Derived

.We plan to monitor and capture the enabled built-in Events from Text Boxes and Command Buttons on the Form and execute the Event Procedures in the Class Module Object instance. 

We have a Class Module for Text Box controls and another one for Command Buttons.  We will use both as Base Classes for the Derived Object ClsTxtBtn_Derived.

The derived Class Module's VBA Code is given below:

Option Compare Database
Option Explicit

Private T() As New ClsText
Private B() As New ClsCmdButton
Private m_Frm As Access.Form

Public Property Get mfrm() As Access.Form
  Set mfrm = m_Frm
End Property

Public Property Set mfrm(ByRef frmObj As Access.Form)
  Set m_Frm = frmObj
  init_Class
End Property

Private Sub init_Class()
   Dim ctl As Control
   Dim tCount As Long
   Dim bCount As Long
Const Evented = "[Event Procedure]"

tCount = 0 'counter for textbox controls
bCount = 0 'counter for Command Button controls
For Each ctl In m_Frm.Controls
   Select Case TypeName(ctl) 'Type name TextBox or CommandButton ?
     Case "TextBox"
         tCount = tCount + 1 'increment counter
         ReDim Preserve T(1 To tCount) 'redimension TextBox Class Objecct Array
         Set T(tCount).p_Frm = m_Frm 'pass Form Object
         Set T(tCount).p_txt = ctl 'pass the TextBox control
         
         Select Case ctl.Name
            Case "Text2" ' enable AfterUpdate and OnLostFocus Events
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text4"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text6"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
        End Select
            
      Case "CommandButton"
         bCount = bCount + 1 'increment counter for CommandButton
         ReDim Preserve B(1 To bCount) 'redimension Button Class Object Array
         Set B(bCount).p_Btn = ctl 'pass CommandButton control
        
        Select Case ctl.Name
            Case "Command8" 'Enable Click Event
                B(bCount).p_Btn.OnClick = Evented
            Case "Command9"
                B(bCount).p_Btn.OnClick = Evented
        End Select
      End Select
    Next
End Sub

The TextBox Class Module ClsText is instantiated as an Array Object with an undeclared number of elements.  Likewise the ClsCmdButton Class Module is also declared in the second line.  The Access.Form Object m_frm is declared to receive the active Form Object passed from the Form_Load() Event Procedure and act as an intermediary to pass the reference to each array element of the ClsText Class Object.

Next the Get/Set Property Procedures mFrm() for the m_frm Form Object controls the Form object retrieval and assignment actions.

In the Set Property Procedure the Class_Init() (this is not the Class_Initialize() sub-routine) sub-routine is called to create separate Class Object Arrays for each TextBox Object and Command Button.

One Control (ctl) Object and two counter variables tcount for TextBoxes and bcount for Command Button Count.  They are used as class objects array indexes during re-dimensioning the ClsText and ClsCmdButton Class Object Arrays.

The Constant Variable Evented is assigned with the string "[Event Procedure]".

Next both counter variables are initialized to zero.

The For Each . . . Next loop takes each control on the Form, checks it's type name whether it is  TextBox or CommandButton control.  The TypeName(ctl) Function gives the type name of the control.

If the control is a text box it's counter variable tcount is incremented by one.  The Text Box Class Module Object T is re-dimensioned for 1 to tcount elements, preserving the earlier array elements' data, with the statement: ReDim Preserve T(1 to tcount). 

Note: In the re-dimension statement, it never says ReDim Preserve T(1 to tcount) As ClsText , as we normally do for normal variables,  like Re-Dim Preserve Qty(1 to cnt) as Single.

Next, the reference of the Form Object copied into the Text Box Class  Module Property in the statement Set T(tcount).p_Frm = m_Frm to the tcountth  Array element object instance.

Next, Set T(tcount).p_txt = ctl passes the Text Box reference to the same object element.

In the next step, we check for the Text Box name to determine which Event to be enabled.  Each Text Box may need different Event to run depending on the requirement of that particular data Field.  Here, we apply a blanket rule of enabling the AfterUpdate and OnLostFocus Events for all Text Boxes for demo purposes.

Hence, the following statements are added for all Text Box controls:

T(tCount).p_txt.AfterUpdate = Evented
T(tCount).p_txt.OnLostFocus = Evented

When the Command Button control is identified it's counter variable bcount is incremented by one and it's Class Object B instance is re-dimensioned for 1 to bcount elements, preserving the earlier elements data, if any.

The Command Button Class Module doesn't have the Form Property.

The current Command Button control reference is passed to the B Object bcountth element through the statement Set B(bCount).p_Btn = ctl.

Next, we check for the Command Button Name and enables the Click Event for each Command Button. 

Note: If all the command Buttons need only the Click Event then we don't need to check for their individual names but the statement B(bCount).p_Btn.OnClick = "[Event Procedure]" immediately after the statement Set B(bCount).p_Btn = ctl is enough.  But, the detailed Code for each Command Button is presented for clarity.

This process is repeated for all Text Boxes and Command Buttons on the Form. Other controls like Labels or any other control on the Form is ignored.

The Form frmClassArray Class Module Code.

Option Compare Database
Option Explicit

Private D As New ClsTxtBtn_Derived

Private Sub Form_Load()
   Set D.mfrm = Me
End Sub

The Form_Load() Event Procedure passes the Form Object to the D.mfrm() Property Procedure of the Derived Class Object ClsTxtBtn_Derived.  The Form Object reference is copied to each  ClsText Class Object Array Element, from the derived Class object.

Downloads

Download the Demo Database from the link(s) below and try out and study the Code.  You may run the Code in Debug Mode by pressing F8 key to step through each executable line of Code.



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
Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports Class Module msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array External Links Queries msaccess reports msaccess tips Accesstips Downloads Objects Property Collection Object Event Menus and Toolbars Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Dictionary Object Form Report Calculation Command Button Data Emails and Alerts Query RaiseEvent Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item msaccessprocess security advanced Access Security Add Auto-Number Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Opening Access Objects from Desktop

Frequently Used Methods. Set the Form Name in Display Form Option of Current Database in Access Options . BIG DEAL!, this is the first t...

Labels

Blog Archive

Recent Posts