Access 2013 VBA - Setting New Click Event for Controls

Question

I have searched everywhere for this, and it seems like a simple fix, but I can't seem to find the solution. I have several Rectangle controls in my Access 2013 form, and I'm creating an OnClick event that handles them all. I've worked on a few different methods, and I think I found the simplest/cleanest way to do it. I put the controls in a collection and change the OnClick event for each control. Here's my problem: Access opens the form and recognizes that I changed the event for the control, but once I click the control, it throws an error and will not execute the event.

The Error:

"The expression On Click entered as the event property setting produced the following error: The expression you entered has a function name that Microsoft Access can't find."

The Code:

Private Sub Form_Load()
Dim m_colRectangle As Collection
Dim ctl As Access.CONTROL

Set m_colRectangle = New Collection
For Each ctl In Me.Controls
    If ctl.ControlType = acRectangle Then
        If ctl.Name = "shpTest" Then
            m_colRectangle.Add ctl, ctl.Name

            ctl.OnClick = "=TestClick()" ' <--- Error on this line

        End If
    End If
Next ctl
End Sub

Private Sub TestClick()
    MsgBox "Test"
End Sub

Alternatively, I tried a simple shpTest.OnClick = "=TestClick()" in the Form_Load event, and this produced the same error. Anyone have any ideas?

Answer 1

The error message is telling you Access can't find a function named TestClick. Your TestClick is a subroutine, not a function.

Here is a simpler example, tested in Access 2010 and 2013, which demonstrates that using a function for a control's .OnClick property can work ... but you need a function. :-)

Private Sub Form_Load()
    Dim ctl As Control
    Set ctl = Me.Controls("txtMathExpresson")
    ctl.OnClick = "=TestClick()"
    Set ctl = Nothing
End Sub

Private Function TestClick()
    MsgBox "Test"
End Function

Note my Access 2013 test was with a traditional desktop application. If you're working with an Access 2013 WebApp, I don't know what will happen.

Answer 2

You don't get to specify which procedure runs in VBA. The procedure that runs will always be ControlName_Click. The OnClick property that you're trying to set only lets you switch between Access Macro and [Event Procedure] vba code. It does not work like event delegates do in the .Net platform.

Please see the OnClick Property documentation on MSDN.

The solution here is to use the Microsoft Visual Basic for Applications Extensibilty Library to write snippets of code into the modules. I'll leave that as as exercise for you.