Creating a Class to Handle Access Form Control Events

Question

I'm trying to create a Class which will handle multiple Control Events in Access. This is to save the repetition of typing out many lines of identical code.

I've followed the answer located on the following page, but with a few adjustments to tailor it to Access rahter than Excel.

How to assign a common procedure for multiple buttons?

My Class code below:

Option Compare Database

Public WithEvents ct As Access.CommandButton 'Changed object type to something recognised by Access

Public Sub ct_Click()
    MsgBox ct.Name & " clicked!"
End Sub

My Form code below:

Option Compare Database
Private listenerCollection As New Collection
Private Sub Form_Load()
Dim ctItem
Dim listener As clListener

For Each ctItem In Me.Controls
    If ctItem.ControlType = acCommandButton Then 'Changed to test whether control is a Command Button
        Set listener = New clListener
        Set listener.ct = ctItem
        listenerCollection.Add listener
    End If
Next

End Sub

I have noted with comments where I have made changes to the (working) Excel code. I think the problem comes with the object declaration in the Class. Note: no errors are thrown during this procedure; it simply doesn't trigger the event.

Thanks in advance!

Edit:

I've since narrowed the problem down to there being no '[Event Procedure]' in the 'On Click' Event. If I add it manually, the Class works as expected. Obviously, I don't want to have to add these manually - it defeats the object. Any ideas how I would go about this?

Solution 1

In your OnLoad event you can add this line

Dim ctItem
Dim listener As clListener

For Each ctItem In Me.Controls
    If ctItem.ControlType = acCommandButton Then 'Changed to test whether control is a Command Button
        Set listener = New clListener
        Set listener.ct = ctItem
        listener.ct.OnClick = "[Event Procedure]"  '<------- Assigned the event handler
        listenerCollection.Add listener
    End If
Next

Although I'm not sure if this is more is less code than just double clicking in the OnClick in the designer and pasting in a method call. It's cool regardless.

Edit: You could change your class like this

Public WithEvents ct As Access.CommandButton 'Changed object type to something recognised by Access

Public Function AddControl(ctrl as Access.CommandButton) as Access.CommandButton
    set ct = ctrl
    ct.OnClick = "[Event Procedure]"
    Set AddControl = ct
End Function

Public Sub ct_Click()
    MsgBox ct.Name & " clicked!"
End Sub

Then in your form you can add a ct like this

For Each ctItem In Me.Controls
    If ctItem.ControlType = acCommandButton Then 'Changed to test whether control is a Command Button
        Set listener = New clListener
        listener.AddControl ctItem
        listenerCollection.Add listener
    End If
Next

Now the event handler is added in the class.

Solution 2

A Generic Approach to handling Access Form Controls input with a class module:

This code was crafted to handle an application written within a popup window. The Main Form contains a tab control where each tab contains its own subform to either a linked child table or an independent table. The use or non-use of a tab control shouldn't make any difference to the class module processing.

The code can be trimmed to meet your application's needs. For example one could remove controls that one is not using from the class module. Likewise, the controls collection subroutine can be selective by using the TypeName(Ctl) statement to filter the controls that get added to the collection.

In a class module called clsMultipleControls put the following code.

Option Compare Database
Option Explicit

Private m_PassedControl As Control

Private WithEvents atch As Attachment
Private WithEvents bfrm As BoundObjectFrame
Private WithEvents chk As CheckBox
Private WithEvents cbo As ComboBox
Private WithEvents btn As CommandButton
Private WithEvents cctl As CustomControl
Private WithEvents img As Image
Private WithEvents lbl As Label
Private WithEvents lin As Line
Private WithEvents Lst As ListBox
Private WithEvents frm As ObjectFrame
Private WithEvents optb As OptionButton
Private WithEvents optg As OptionGroup
Private WithEvents pg As Page
Private WithEvents pgb As PageBreak
Private WithEvents Rec As Rectangle
Private WithEvents sfm As SubForm
Private WithEvents tctl As TabControl
Private WithEvents txt As TextBox
Private WithEvents tgl As ToggleButton

Property Set ctl(PassedControl As Control)
Set m_PassedControl = PassedControl

Select Case TypeName(PassedControl)
Case "Attachment"
    Set atch = PassedControl
Case "BoundObjectFrame"
    Set bfrm = PassedControl
Case "CheckBox"
    Set chk = PassedControl
Case "ComboBox"
    Set cbo = PassedControl
Case "CommandButton"
    Set btn = PassedControl
Case "CustomControl"
    Set cctl = PassedControl
Case "Image"
    Set img = PassedControl
Case "Label"
    Set lbl = PassedControl
Case "Line"
    Set lin = PassedControl
Case "ListBox"
    Set Lst = PassedControl
Case "ObjectFrame"
    Set frm = PassedControl
Case "OptionButton"
    Set optb = PassedControl
Case "OptionGroup"
    Set optg = PassedControl
Case "Page"
    Set pg = PassedControl
Case "PageBreak"
    Set pgb = PassedControl
Case "Rectangle"
    Set Rec = PassedControl
Case "SubForm"
    Set sfm = PassedControl
Case "TabControl"
    Set tctl = PassedControl
Case "TextBox"
    Set txt = PassedControl
Case "ToggleButton"
    Set tgl = PassedControl
End Select

End Property

At the top of the Main Form module place the following code.

Public collControls As Collection
Public cMultipleControls As clsMultipleControls

In the Load event of the Main Form place the following code.

GetCollection Me

At the bottom of the Main Form code place the following recursive public subroutine:

Public Sub GetCollection(frm As Form)
    Dim ctl As Control

    On Error Resume Next
    Set collControls = collControls
    On Error GoTo 0

    If collControls Is Nothing Then
        Set collControls = New Collection
    End If

    For Each ctl In frm.Controls
        If ctl.ControlType = acSubform Then
            GetCollection ctl.Form
        Else
            Set cMultipleControls = New clsMultipleControls
            Set cMultipleControls.ctl = ctl
            collControls.Add cMultipleControls
        End If
    Next ctl

end sub

I'd advise giving each control in the form and its subforms a unique name so you can easily utilize the Select statement based on the control name to effectuate processing control in each class module event. For example, each textbox change event will be sent to the txt_change event in the class module where you can use the m_PassedControl.name property in a select statement to direct which code will be executed on the passed control.

The select event is quite useful if you have multiple controls that will receive the same post entry processing.

I use the Main Form Load event rather than the Activate event because a popup form (and its subforms) do not fire the Activate or Deactivate events.

One can also pass the m_PassedControl on to a subroutine in a regular module if you have you have some lengthy processing to accommodate.

Unfortunately, Access does not automatically fire VBA events unless you actually set the event up in the VBA module. So if you want to use a textbox change event you have to make sure the textbox change event is actually set up in applicable vba module. You don't need to add any code to the event, but the empty event must be there or the event and its class module equivalent will not fire. If anyone knows of a work around for this I'd be glad to hear about it.

I found this basic class module structure in an Excel userform code example at http://yoursumbuddy.com/userform-event-class-multiple-control-types/. It's a flexible structure. I have created versions that work with Excel userforms, Excel worksheets with activex controls, and now for Access forms.

Followup Note: The above code works fine with 64 bit Access 2013 on 64 bit Windows 10. But it fails on 64 bit Access 2013 on 64 bit Windows 7 when you try to close the main form. The solution is to move the following code from the main form to a VBA module.

Public collControls As Collection
Public cMultipleControls As clsMultipleControls

Comments

[Jiminy Cricket]

Thanks Brad, I had actually already went down this route, but I'd much rather set the property in the class - I'm going to try AVG's suggestion above. +1 for providing a working solution.

[Brad]

@JiminyCricket I made an edit that lets you do all the property setting in the class

[Jiminy Cricket]

this is hugely helpful! I had been trying to get a propery in the Class, I never even considered unsing a Function. Although adding the property during load works okay, this is much better as it's all self contained. And so my adventure with classes begins... Something I'd wanted to get into for a long time!

[Mindflux]

Sorry for a 5 year old reply, but if your class module sets up the events eg: cbo.OnClick = "[Event Procedure]" you do NOT have to set the form event at all. The class will "programmatically" enable the event.