CODEWITHSUNDEEP
vbams-access
Albert Perrien
Albert Perrien

Reputation: 1153

Programmatically Determining the Event Handler for an OnClick Event in VBA

I'm working on an application that traces execution paths in MS Access VBA. However, I've run into a problem. It seems that you can dynamically assign handlers to control OnClick and and other such events in VBA, but I don't see a way to read events from those controls. For example:

With Forms("Order Entry").Controls("OK") 
 If .OnClick = "" Then 
 .OnClick = "fnProcessOrder" 
 End If 
End With

This dynamically assigns the function "fnProcessOrder" to the "OK" button. However, if you try to get the value of that button, you get this:

With Forms("Order Entry").Controls("OK") 
  Debug.Print "My Handler -->" + .OnClick
EndWith

Results in:

--> [Event Procedure]

And no further detail. This does seem to be the behavior stated by Microsoft in here: https://msdn.microsoft.com/en-us/vba/access-vba/articles/commandbutton-onclick-property-access

But I'm wondering if there is anyway to go further than that.

I would have thought that that information would be in the MSysObjects and MSysQueries tables, but I don't see it there. I've tried extracting my test database using the Database Documenter, and I get that value from the generated documentation as well.

Is there a way to find the VBA function that was assigned to the control?

Upvotes: 1

Views: 2877

Answers (2)

Erik A
Erik A

Reputation: 32682

This will return the full code for the event. If you just use one sub inside that event, it should work.

Public Sub TestModulePrinter()
    Dim subName As String
    With Forms("Order Entry").Controls("OK")
      subName = "Private Sub " & .Name & "_OnClick()"
    End With
    Dim i As Long
    Dim IsPrinting As Boolean
    With Forms("Order Entry").Module
        For i = 1 To .CountOfLines
            If Not IsPrinting Then
                If Trim(.Lines(i, 1)) = subName Then
                    IsPrinting = True
                End If
            Else
                If Trim(.Lines(i, 1)) = "End Sub" Then
                    IsPrinting = False
                Else
                    Debug.Print .Lines(i, 1)
                End If
            End If
        Next i
    End With
End Sub

Upvotes: 1

this
this

Reputation: 1426

Actually, the code does not look right to me. If you want to assign a VBA function to an event procedure, you have to use syntax similar to this: .OnClick = "=fnProcess()" Note the literal = and the () -- those are mandatory for making Access recognize you want to call a function.

As additional consideration, you can have a class module (let's call it Observer) that has code similar to this:

Private WithEvents ctlInterest As Access.Textbox

Public Sub Init(TargetControl As Access.Control)
  Set ctlInterest = TargetControl
  With ctlInterst
    If .OnClick = "" Then
      .OnClick = "[Event Procedure]"
    End If
  End With
End Sub

Private ctlInterest_OnClick()
  fnProcess()
End Sub

and you can then deploy the class in a form like so....

Private objObserver As Observer

Private Sub Form_Load()
  Set objObserver = New Observer
  objObserver.Init(Me.txtInterest)
End Sub

which will yield the same result, and you end up with multiple event handlers.

For more information this may be of help.

In conjunction with the fact that function expressions are possible and the fact that it can be dynamically assigned at any time, it pretty much require code-path analysis to identify possible entry points for all events handlers in a general. Which means you need Rubberduck's parser for that. Note that static code path analysis is planned but not implemented yet, AIUI.

Also, I want to be sure -- if your intention is to build a call stack trace for error handling, you might need to look at third party add-in. For example, vbWatchDog, a commerical product, can provide that information in VBA.

Upvotes: 1

Related Questions