CODEWITHSUNDEEP
ms-accessvba
Sean Leroy
Sean Leroy

Reputation: 78

MS Access Change an 'OnChange' Event programmatically in VBA

I am working on a project where I have to use MS Access and I have to make the database as loose as possible (Its weird and I dont think best practice but for now given my resources it is what I have).

Anyways I have 50 combo boxes but often have to make changes to one which would mean I have to manually change all of them. Id rather spend hour finding a programming solution then 5 minutes manually doing this.

I need to change the 'OnChange' event using VBA but my code produces an error.

Private Function RunChangePropagate()
    Dim combo As ComboBox
    RevealGrid
    For Each combo In Me.Controls
        combo.OnChange = "=ComboBox_Change()"
    Next combo
    ClearGrid
End Function

Error: Error

I have also tried making the text to change it to a Variant and then assigning the event to said Variant.

How do I go about doing this?

Thanks in advance.

Upvotes: 2

Views: 3955

Answers (2)

Gustav
Gustav

Reputation: 56026

Use WithEvents. This way, you form is completely "detached" from the class controlling (some controls of) the form.

I published an article with links and an example for anyone to study:

Create Windows Phone Colour Palette and Selector using WithEvents

The main code (code behind module and class module) is only:

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


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

and:

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

Full code is also on GitHub: VBA.ModernTheme

Upvotes: 1

Vityata
Vityata

Reputation: 43595

This is some minimal example, that works:

Public Sub ChangeEvent()

    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If ctrl.Name = "Combo5" Then
            Debug.Print ctrl.OnChange
            ctrl.OnChange = "SomeProcedure"
        End If
    Next ctrl

End Sub

In your example you should only remove the = in the assigning. The parenthesis at the end of the assigned sub are not required.

Upvotes: 1

Related Questions