vba - 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?

Answer 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.