List an MS Access form's controls with their events

Question

I have a humongous Access form with hundreds of controls, all with some events. One of them is throwing an error crying about "Validation or After Update event". No control name, nothing more to go by.

So I figured I'd list all the forms with all their validation rules and all their events (not all of them are "[Event procedure]", some are custom function calls).

Is there any way to get a list of a control's events?

My code so far:

Sub ListAllControlsAndTheirEvents(FormToCheck As Form)
 Dim ctrlCurrent As Control
 For Each ctrlCurrent In FormToCheck.Controls
   Debug.Print "Name: " & ctrlCurrent.Name
   On Error Resume Next
     Debug.Print "Validation: " & ctrlCurrent.ValidationRule 'Not all controls have a ValidationRule property.
   On Error GoTo 0
   For Each eventCurrent In ctrlCurrent.events 'this thing doesn't exist :(
     Debug.Print "Event: " & eventCurrent.Name & "; value: " & eventCurrent.whatever
   Next
 Next
End Sub

So, is there a way to implement this part?

For Each eventCurrent In ctrlCurrent.events 'this thing doesn't exist :(
  Debug.Print "Event: " & eventCurrent.Name & "; value: " & eventCurrent.whatever
Next

Digging through the code module won't give me the custom function event calls, just the [Event Procedure] codes.

You could, list the controls as you have done, then use the form name to derive the VBA module name, then instr search the test for the control name, like "Button1" and "_" so "Button1 _" then you can get the rest, I.e. Click() — Nathan_Sav, May 13 '16 at 08:52
@Nathan_Sav Yes, that is true. Disgusting, but true. :) The only problem is that it *won't list events that are not `[Event Procedures]` but custom functions.* You know, when I type `=GenericBeforeUpdateFunction()` in the `Before Update` property. I have no idea what function is used in which commad's which event! I want to list those too. — vacip, May 13 '16 at 08:57
You could use typename to determine the type of control, then set so something like if typename(x) ="CommanButton" then set cb=x, then loop the properties, for property names beginning with On, then if there is an [Event Procedure] you know there's an event, also you can then narrow down your search to x_Click — Nathan_Sav, May 13 '16 at 08:58
So do what I said, search the VBA code of the module FormName by VBA the search for "ControlName" and underscore, then seach for the next End Function/Sub, then you'll have the form, control, event and the code. — Nathan_Sav, May 13 '16 at 08:59
@Nathan_Sav Properties it is. In the VBA code, one Function can be tied to many many controls, so just going through there won't give me the associations. Let me try fiddling with the properties. Thank you! — vacip, May 13 '16 at 09:03
@Nathan_Sav Listing all the properties with "On" did what I was looking for. If you post it as an answer, I'll accept it. Thanks for the help! :) — vacip, May 13 '16 at 09:08

score 2 · Accepted Answer · answered May 13 '16 at 09:23

You could use typename to determine the type of control, then set so something like if typename(x) ="CommanButton" then set cb=x, then loop the properties, for property names beginning with On, then if there is an [Event Procedure] you know there's an event, also you can then narrow down your search to x_Click

score 2 · Answer 2 · answered Apr 10 '17 at 20:08

The code below should identify and list all events (whether blank, [Event Procedure], or a custom function) for each control on the form. I tested it on Access 2010 by creating a blank form and adding several controls. I made sure to assign some standard events and custom functions to a few controls in order to validate.

Public Sub ListAllControlsAndTheirEvents(FormToCheck As Access.Form)

    Dim ctrlProp    As Object
    Dim ctrlCurrent As Access.Control

    For Each ctrlCurrent In FormToCheck.Controls

        Debug.Print "Name: " & ctrlCurrent.Name

        If PropertyExists(ctrlCurrent, ctrlProp, "ValidationRule") Then

            Debug.Print "Validation: " & ctrlProp.Value

        End If

        For Each ctrlProp In ctrlCurrent.Properties

            ' Category 4 type 8 identifies an event property
            If ctrlProp.Category = 4 And ctrlProp.Type = 8 Then

                Debug.Print _
                "Event: " & ctrlProp.Name & "; " & _
                "Value: " & ctrlProp.Value

            End If

        Next

    Next

End Sub

Private Function PropertyExists( _
    ByRef ctrlCurrent As Access.Control, _
    ByRef ctrlProp As Object, _
    ByRef PropName As String _
) As Boolean

    On Error Resume Next

    Set ctrlProp = ctrlCurrent.Properties(PropName)
    PropertyExists = Err.Number = 0

End Function

score 0 · Answer 3 · answered Feb 05 '21 at 03:13

I know that this question has been marked as answered like 4 years ago.
I have been to this question in my search for the same thing in Excel.
This question was the source behind my search for an answer to a similar question.

I have found an answer to this question using TLI.
The answer I provided here is meant to help other people reach for a solution quickly and painlessly.
There is NO intention of gaining merits/points/reputation but just to help others get an answer I've only reached after spending so much time and energy.

I am no expert in Access VBA nor am I in Excel VBA but I think TypeLib Info can be used in Access too.

Requirement: Reference to TypeLib Information library at C:\Windows\SysWow64\TLBINF32.DLL

Sub printControlEventNames(ByVal o As Object)

Dim t As TLI.TLIApplication
    Set t = New TLI.TLIApplication

Dim ti As TLI.TypeInfo
    Set ti = t.ClassInfoFromObject(o)
    
Dim mi As TLI.MemberInfo
    For Each mi In ti.DefaultEventInterface.Members
        Debug.Print mi.Name
    Next
End Sub

My question and my own solution can be found here.

List an MS Access form's controls with their events

3 Answers3

Linked