event triggered when any control changed

hoen · January 21st, 2006, 01:25 PM

I have an Access Form with many text boxes on the form: named "Text1", "Text2"....."Text80", etc. Is it possible to have a single event triggered when any of the text boxes is changed? I am trying to avoiding writing 80+ event handlers for each of the boxes. In other words, could a single event sense when anyone of the 80 boxes has been changed, and then pass the appropriate information as to which control has changed?
Thanks

Bob Bedell · January 21st, 2006, 04:53 PM

Hi hoen,

The concept you are describing is known as a control array. Technically speaking, VB is the only MS language that provides intrinsic support for them. If you place 3 TextBox controls on a VB form and give them all the same name, VB initializes them in an array of TextBoxes as:

Textbox(0)
Textbox(1)
Textbox(2)

You can then do stuff like:

Code:

Private Sub TextBox_Change(Index As Integer)
    â The textbox that fired the event gets its text property changed.
    TextBox (Index).Text = "something"
End Sub

Or:

Code:

For i = 1 To 6
     TextBox(i).Text = "TextBox"
Next

VB.Net abandoned this type of control array, but replaces it with the ability to create event handlers that handle multiple control events with syntax like:

Code:

Private Sub AllControlButtins_Click( _
    ByVal sender As System.Object, _
    ByVal e As System.EventArgs) _
      Handles Button1.Click, _
                  Button2.Click, _
                 Button3.Click

The "sender" argument identifies the control that fired the event.

VBA doesn't support control arrays either. To emulate this behavior, however, you can to design a class to store a reference to each TextBox control on your form in a module level object variable declared WithEvents. You declare and event handler in the class, like:

Private Sub m_TextBox_Change()

Then give the event handler the ability to notify a specific textboxâs parent form which textbox fired the event by calling a method on the form.

This is as close as you can get in VBA to having a single event sink for multiple controls:

Code:

=====================================================
Module For frmParent:

frmParent  has two TextBox controls on it:  txtTextBox1 and txtTextbox2
=====================================================

Option Compare Database

' Collection to store instances of clsTextBox
Private colTextBoxes As New Collection
Private ctlTextBox As clsTextBox

Public Sub WhichTextBox(ctl As TextBox)
    ' Notify user which TextBox fired the change event.
    MsgBox "ChangeEvent fired by: " & ctl.Name
End Sub

Private Sub Form_Load()

    ' Instantiate new clsTextBox objects.
    ' Each object holds a reference to one
    ' of the textbox controls on the form
    ' and a reference to the controls parent
    ' form. The newly instantiatied object is
    ' then added to the colTextBoxes collection.

    ' Create a class to reference txtTextBox1
    Set ctlTextBox = New clsTextBox
    ctlTextBox.Initialize txtTextBox1, Me
    colTextBoxes.Add ctlTextBox

    ' Create a class to reference txtTextBox2
    Set ctlTextBox = New clsTextBox
    ctlTextBox.Initialize txtTextBox2, Me
    colTextBoxes.Add ctlTextBox

End Sub

=====================================================
Class Module for clsTextBox:

Sinks Change events for all clsTextBox objects
=====================================================

Option Compare Database
Option Explicit

' Holds a reference to one TextBox control on
' frmParent. It is declared WithEvents so it
' can sink events for the TextBox control it
' refers to.
Private WithEvents m_TextBox As TextBox
Private Const mstrEventProcedure = "[Event Procedure]"

' Holds a reference to the parent form that hosts
' the TextBox control.
Private m_Form As Form_frmParent

' Initialize a new clsTextBox object, passing it
' references to a TextBox control and its parent
' form
Public Sub Initialize(ctl As TextBox, frm As Form_frmParent)
    Set m_TextBox = ctl

    'Set the OnChange property of the control to [Event Procedure]
     m_TextBox.OnChange = mstrEventProcedure
    Set m_Form = frm
End Sub

' This event handler will handle the Change event for each
' TextBox control referenced by a clsTextBox object in
' colTextBoxes.
Private Sub m_TextBox_Change()

    ' Call the WhichTextBox method of the parent form, passing
    ' it the instance of the TextBox that fired the Change event.
    m_Form.WhichTextBox m_TextBox

End Sub

' Garbage collection.
Private Sub Class_Terminate()
    Set m_TextBox = Nothing
    Set m_Form = Nothing
End Sub

NOTE:

You can also use a controls Tag Property to emulate some control array type behavior:

Code:

Private Sub cmdButton1_Click()
    Dim ctl As Control
    Dim i As Integer

    For Each ctl In Me.Controls
        If Not ctl.Tag = "" Then
            For i = 1 To 3 ' Effects controls whose tag property is in the range 1 to 3
                If ctl.Tag = i Then
                    ctl.Enabled = True
                End If
            Next
        End If
    Next

End Sub

HTH,

Bob

Bob Bedell · January 21st, 2006, 05:33 PM

Hi hoen,

Now change your Form_Load event to:

Code:

Private Sub Form_Load()

    ' Instantiate new clsTextBox objects.
    ' Each object holds a reference to one
    ' of the textbox controls on the form
    ' and a reference to the controls parent
    ' form. The newly instantiatied object is
    ' then added to the colTextBoxes collection.

    Dim ctl As Control
    Dim i As Integer

    For Each ctl In Me.Controls
        With ctl
            If .ControlType = acTextBox Then
                Set ctlTextBox = New clsTextBox
                ctlTextBox.Initialize ctl, Me
                colTextBoxes.Add ctlTextBox
            End If
        End With
    Next ctl

End Sub

And you won't have to cut and paste the following 80 times:

Code:

 
   ' Create a class to reference txtTextBox1
    Set ctlTextBox = New clsTextBox
    ctlTextBox.Initialize txtTextBox1, Me
    colTextBoxes.Add ctlTextBox

The loop will wire each TextBox control to the class event sink.

HTH,

Bob

Bob Bedell · January 21st, 2006, 05:59 PM

There are a lot of variations on this theme, of course. For example you can also create a form class with a ClassFactory method and have it generate all your TextBox classes, or you can just have the TextBox class itself modify a property of the TextBox without having the event sink call back to a method on the parent form, etc....

What do you want your generic Change event to do?

Bob

SerranoG · January 24th, 2006, 08:58 AM

You don't need to use each textbox's After Update event nor access an array. Simply put code on the FORM's After Update Event.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

kindler · January 24th, 2006, 11:26 AM

If the After Update event doesn't work, On Dirty might.

Bob Bedell · January 24th, 2006, 12:57 PM

Unfortunately, the form's After_Update event won't fire until the text box value is updated (i.e., the record is saved). It won't fire if you simply tab to a textbox and type a new value in the textbox. Only the textboxes Change event will repond to typing the new value.

The Dirty event won't help either. It will fire the first time you enter a new value in a textbox, but only for the first textbox. It won't fire for chnges made to subsequent textboxes. The dirty event juts sets a booleann value the first time a form is dirtied.

There isn't anyway to make Form events emulate a single Change event that all TextBoxes on a form are wired to. You need to use the Class approach.

Bob

Bob Bedell · January 24th, 2006, 01:44 PM

Of course, I'm assuming that since hoen's post includes the phrases:

"...when any control changed."
"...when any of the text boxes is changed?"
"...when any one of the 80 boxes has changed..."
"...as to which control has changed?"

that what he wants is a single textbox Change event and not a textbox AfterUpdate event.

Bob