MS Access: Replace Many On Click Events with Generic On Click Event-CodePudding

I have a form in MS Access with 12 buttons running along the top serving as column headers. For each button there is an On Click event which calls the same public function. This function, (shown below), opens the Filter Menu for the field corresponding to the button that was clicked.

Public Function HeaderClick(HeaderName As String) 
    DoCmd.GoToControl "[" & HeaderName & "]"
    DoCmd.RunCommand acCmdFilterMenu
End Function

For example, I click on the First Name button and it pulls up the filter menu for First Name:

I'm wondering if there is a way to call HeaderClick whenever ANY of these buttons are clicked. In other words, I'd like an alternative to creating 12 separate On Click Events. Reason being that this is a technique I use on many forms in many databases. It just doesn't seem very efficient.

CodePudding user response：

When you click on the OnClick textbox to add a new function, you typically see the text [Event Procedure]. Just overwrite it with the name of the function (make sure the function is in a global module). So instead of [Event Procedure], you would type =HeaderClick("Name"). You would still need to specify the argument for the function.

CodePudding user response：

You would create a class, i've called mine clsCustomButton like so

Option Explicit

Private WithEvents cmdCustom As CommandButton

Public Sub Initialise(cmdIn As CommandButton)
    Set cmdCustom = cmdIn
    cmdCustom.OnClick = "[Event Procedure]"
End Sub

Private Sub cmdCustom_Click()
    MsgBox "Hello"
End Sub

Private Sub Class_Terminate()
    Set cmdCustom = Nothing
End Sub

In a standard code module, you'll need to have a collection/array to hold this "new" custom buttons, i've called mine colButtons

Option Explicit

Public colButtons As Collection

Then you need to add to collection by passing in the buttons you want to change, i've just done one, but you could loop for all or specify use tag/name to do this. Like so, from within the form, usually on opening

Dim clsCommandButton As clsCustomButton

Set colButtons = New Collection
    
Set clsCommandButton = New clsCustomButton

clsCommandButton.Initialise Me.Command0

colButtons.Add clsCommandButton, CStr(colButtons.Count)

CodePudding user response：

Another example is my Windows Phone Theme Colour selector where clicks on textboxes are caught:

Form:

Option Explicit

' Form to display the Windows Phone 7.5/8.0 colour theme.
' Also works as a basic example of implementing WithEvents for a form.
' 2017-04-19. Gustav Brock, Cactus Data ApS, CPH.
' Version 1.0.0
' License: MIT.

' *

Private ControlCollection   As Collection

Private Sub Form_Load()

    ' Load events for all colour value textboxes.
    
    Dim EventProcedure  As ClassTextboxSelect
    Dim Control         As Access.Control
    
    Set ControlCollection = New Collection
    
    For Each Control In Me.Controls
        If Control.ControlType = acTextBox Then
            Set EventProcedure = New ClassTextboxSelect
            EventProcedure.Initialize Control
            ControlCollection.Add EventProcedure, Control.Name
        End If
    Next
    
    Set EventProcedure = Nothing
    Set Control = Nothing
    
End Sub


Private Sub Form_Unload(Cancel As Integer)

    ' Unload events for all colour value textboxes.
    
    Dim EventProcedure  As ClassTextboxSelect
    
    For Each EventProcedure In ControlCollection
        EventProcedure.Terminate
    Next
    
    Set EventProcedure = Nothing
    Set ControlCollection = Nothing

End Sub

Class:

Option Explicit

' Helper class for form Palette for event handling of textboxes.
' 2017-04-19. Gustav Brock, Cactus Data ApS, CPH.
' Version 1.0.0
' License: MIT.

' *

Private Const EventProcedure    As String = "[Event Procedure]"

Private WithEvents ClassTextBox As Access.TextBox
Attribute ClassTextBox.VB_VarHelpID = -1

Public Sub Initialize(ByRef TextBox As Access.TextBox)

    Set ClassTextBox = TextBox    
    ClassTextBox.OnClick = EventProcedure
    
End Sub


Public Sub Terminate()

    Set ClassTextBox = Nothing

End Sub


Private Sub ClassTextBox_Click()

    ' Select full content.
    ClassTextBox.SelStart = 0
    ClassTextBox.SelLength = Len(ClassTextBox.Value)
    ' Display the clicked value.
    ClassTextBox.Parent!CopyClicked.Value = ClassTextBox.Value
    ' Copy the clicked value to the clipboard.
    DoCmd.RunCommand acCmdCopy

End Sub

Full code, the Access application for download, and documentation at VBA.ModernTheme.