Access VBA: how to subscribe to a the change of a control/field happened from other vba code and not from user interaction

Question

I'm in a weird situation. I have a form with a subform. When a value in the subform changes, in the after update I call

If Not IsNull(Me.Parent) Then
   Me.Parent.ParentField.Value = Me.SubformField.Value
End If

In the parent form I want the change in ParentField to trigger another event. Too bad that changing programmatically doesn't trigger either the AfterUpdate nor the onChange event.

Since the same subform is used in more parent forms I can't call directly the method that is called on the afterUpdate

Is there something else I can "subscribe" to?

Based on experience with Excel userforms (not sure if this translates to Access; hence not adding as an answer). Possibly create a class with the subform. When the value changes raise a custom event. On the parent forms, adding an instance of the subform class withevents should allow custom code to be added in the Parent's code-behind under the custom event; similar to adding code for a dropdop/textbox change, button click, etc. — Mistella
Your answere seemed exactly what I wanted, but unfortunately I didn't quite get how to implement it. I went with a "stupid" solution that checks if the parent is the one having the correct function and called the function only if there's a match. Not ideal but the quickest solution given my time restraints. — vkmi
Fair enough. I'll add an answer with more detail, and a mock-up for anyone who might be working on this in Excel. — Mistella

Mistella Mistella · Accepted Answer · 2019-03-13T13:53:23

At least in Excel, this can be accomplished by adding a custom event to the sub userform, then adding the subuserform as an object in the parent userforms.

I created a mock-up, shown below (the command button on the Parent Userform is primarily to hold the initial focus, as in this mock-up the sub form opens when the text box is entered):

'SubUserForm code
Option Explicit
'Custom Events don't have to pass values, or can pass multiple values as desired
Public Event FormChange(newValue As Variant)

Private Sub TextBoxS_Change()
    'If I wanted the event to raise for various control changes,
    '    I could add it to multiple Change Events.
    RaiseEvent FormChange(TextBoxS.Value)

    'Or I could instead call a private sub such as
    '        ConditionalEvent(TextBoxS.Value)
    '    with the logic for checking if the event should
    '    be raised; especially helpful if
    '    logic depends on multiple changes or conditions
End Sub

Private Sub ConditionalEvent(vNew As Variant)
    If True Then 'More complicated checks, maybe changed private variables, etc.
        RaiseEvent FormChange(vNew)
    End If
End Sub

'This is to prevent the parent losing refernce to the sub form.
'The parent should be the one that creates an instance of this form,
'   as well as be the one to delete it.
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Cancel = True
    Me.Hide
End Sub

'Parent UserForm code
Option Explicit
'The private instance of the subform needs to be of the
'    baseform itself, not a generic object or userform.
'    Otherwise the custom event won't carry through.
'    However, this also means that the only events carried through
'    will be the custom ones (at least as far as I could tell)
Private WithEvents subForm As SubUserForm

'This is the custom event from the subform
Private Sub subForm_FormChange(newValue As Variant)
    TextBoxP.Value = newValue
End Sub

'This is important. The default value of subform is Nothing,
'    so initialization is required.
Private Sub UserForm_Initialize()
    Set subForm = New SubUserForm
End Sub

'This sub is the mock-up logic and call to show the sub form for testing.
'It is not required for using custom events.
Private Sub TextBoxP_Enter()
    subForm.Show
End Sub

Private Sub UserForm_Terminate()
    'Terminates the subform on parent closure
    Set subForm = Nothing
End Sub

For testing, I used the code below to show the parent userform. Please note I also changed the names of the TextBoxes and userforms like so:

Subform: SubUserForm, TextBoxS
ParentForm: ParentUserFrom, TextBoxP

If any one wants to use the code mock-up, after creating the userforms, they will either need to change the names of the textboxes and userforms, or their references in the code.

'In a generic module
Option Explicit

' Used to open the parent form for testing
Sub test()
    ' Using a with statement allows using a new instance of the form
    '    (instead of the "free instance" automatically provided)
    '    without having to assign the new instance to a variable to
    '    use, and then having to assign to variable to Nothing
    With New ParentUserForm
        .Show
    End With
End Sub

Access VBA: how to subscribe to a the change of a control/field happened from other vba code and not from user interaction

2 Answers