Detect a change in a subform, from the main/parent form

[AOB]

Fallover from question on this thread (related but not the same problem...)

I have a continuous subform which lists a set of data from a query. In the header section I have a hidden textbox which holds the ID of the currently selected record. I have a button which updates the value in this header textbox accordingly- nice and simple (this code is in the subform)

Private Sub cmdSelect_Click()
    With Me
        .txtSelectedID.Value = IIf(.txtSelectedID.Value = Me.txtID.Value, "", Me.txtID.Value)
    End With
End Sub

So if you click on a record, the ID of that record is held in the textbox in the header. If you click a different record, the ID updates. If you click the same record again (i.e. to "deselect"), it simply clears the header textbox.

In the main form, I have another textbox which is effectively "bound" to the textbox in the subform, i.e. its Control Cource property is as such :

=[sfmMySubform].[Form]![txtSelectedID]

So as I click on records in the subform, the textbox in the main form automatically updates with the ID in the hidden textbox on the subform.

However - I need to be able to detect when that ID changes (i.e. when a user has selected a record in the subform) from the main form. And weirdly I can't seem to do it?

The Change event on the main form textbox doesn't fire when the value is updated via the subform, and the only subform events available to me are Enter and Exit (neither are any good to me as that requires the user to manually move back to a control on the main form after selecting something)

What event on the main form can I use to detect when the selection on the subform has changed?

Thanks!

AOB

 

[bob fitz]

What event on the main form can I use to detect when the selection on the subform has changed?

Why not use the On Current event of the subform which fires when the user changes to a different record?

 

[arnelgp]

you can set the Value of the textbox in your main form through your cmdSelect button on the subform.

instead of:

=[sfmMySubform].[Form]![txtSelectedID]

you remove this ControlSource and add more code on the cmdSelect button on the subform:

Private Sub cmdSelect_Click()
    Dim sParentID As String
    
    With Me
        .txtSelectedID.Value = IIf(.txtSelectedID.Value = Me.txtID.Value, "", Me.txtID.Value)
    End With
    
     sParentID = Me.Parent!theTextboxOnMainForm & ""
     ' check if id has changed
     If sParentID <> "" And sParentID <> .txtSelectedID.Value Then
         'the id has changed
        'msgbox "?"
     End If
     ' assign the id to parent form textbox
     Me.Parent!theTextboxOnMainForm = sParentID
End Sub

 

[sonic8]

What event on the main form can I use to detect when the selection on the subform has changed?

You are not limited to events of the main form.
Create a form typed variable with the WithEvents keyword on module level in the main form. On Form_Load assign the subform to that form variable. Now you can handle the OnCurrent event of the subform in the main form.

 

[moke123]

However - I need to be able to detect when that ID changes (i.e. when a user has selected a record in the subform) from the main form.

What is supposed to happen once the change is detected? What is your end goal?

 

[AOB]

The end goal is that once the selection on the subform has changed, the main form manipulates its own controls to suit what has just been selected. There's a degree of complexity to this so the cleanest method is to use the subform purely as an alternative to a listbox (in order to allow the user to select/deselect an item from the list) and then "pass" the ID of the selected item back to the main form for it to "do stuff"

I don't want that manipulation to be performed from the subform itself (i.e. adding the manipulation code for the main form, to the Click event of the button on the subform), I just want to pass the ID "up" and have the code in the main form (it just makes so much more sense there, in my use case)

I thought sonic8's suggestion sounded just like what I wanted (i.e. detect an event on the subform, from within the main form) but when I try to add a WithEvents / Form variable to the main form, and then try to add an event handler for Form.Current, Access just crashes completely (I don't mean at runtime or while debugging, I mean just trying to select the event from the dropdown from the IDE, Access just dumps itself immediately)

Private WithEvents sfmMySubform As Form

Private Sub Form_Load()
    Set sfmMySubform = Forms("sfmMySubform")
End Sub

 

[moke123]

Private WithEvents MySubForm As Access.Form

Private Sub Form_Load()

    Set MySubForm = Forms("sfmMySubform")
    MySubForm.OnCurrent = "[Event Procedure]"

End Sub

Private Sub  MySubForm_Current()
   'do stuff here
End Sub

Make sure the HasModule property is set to yes.

 

[AOB]

Not working for some reason?

This falls over :

Set sfmMySubform = Forms("sfmMySubform")
sfmMySubform.OnCurrent = "[DetectChange]"

Public Sub DetectChange()
    'Do Stuff
End Sub

With an error of : Microsoft Access cannot find the referenced form 'sfmMySubform'

(The name is definitely correct...)

If I change it to this, it loads fine :

Set sfmMySubform = Me.sfmMySubform.Form
sfmMySubform.OnCurrent = "[DetectChange]"

Public Sub DetectChange()
    'Do Stuff
End Sub

But when I select a record in the subform, I get this error : Microsoft Access cannot find the object 'DetectChange' / If 'DetectChange' is a new macro or macro group, make sure you have saved it and that you have typed its name correctly.

(I have...)

If I qualify the procedure more fully :

Set sfmMySubform = Me.sfmMySubform.Form
sfmMySubform.OnCurrent = "Forms([frmParent])![DetectChange]"

Public Sub DetectChange()
    'Do Stuff
End Sub

...it makes no difference : Microsoft Access cannot find the object Forms([frmParent])!DetectChange / If 'Forms([frmParent])!DetectChange' is a new macro or macro group, make sure you have saved it and that you have typed its name correctly.

 

[AOB]

Private Sub  MySubForm_Current()
   'do stuff here
End Sub

Make sure the HasModule property is set to yes.

That's still firing code in the subform module, though, correct?

I need to trigger a sub/function in the main form, somehow

 

[AOB]

Okay this seems to be working :

In the subform :

Option Compare Database
Option Explicit

Private frmParent As Form

Private Sub Form_Load()
    Set frmParent = Me.Parent
End Sub

Private Sub cmdSelect_Click()
    With Me
        .txtCurrentID.Value = IIf(.txtCurrentID.Value = Me.txtID.Value, "", Me.txtID.Value)
    End With
    With frmParent
        .Controls("txtSelectedID").Value = Me.txtCurrentID.Value
        Call .UpdateForm
    End With
End Sub

In the parent form :

Option Compare Database
Option Explicit

Public Sub UpdateForm()
    ' Do Stuff
End Sub

What am I missing?...

 

[Pat Hartman]

However - I need to be able to detect when that ID changes (i.e. when a user has selected a record in the subform) from the main form. And weirdly I can't seem to do it?

And you can't because when the subform has the focus, the mainform is "sleeping". If you do something to "wake up" the mainform like click into a control, that triggers Access to save the subform record if it is dirty and run any relevant event procedure in the subform for which you have provided code. After the subform's AfterUpdate event runs, the subform goes "dormant" and the main form "wakes up". It then makes the mainform current and handles whatever event "woke up" the main form. ALSO, placing a value in a control using VBA will NOT trigger ANY control level events. However, it will trigger the BeforeUpdate event for a bound form. PS - your control with the ControlSource that starts with "=" is NOT bound so you should not think of it as bound or you are "bound" to get into trouble In the world of Access, BOUND has a very specific meaning and this is not it.

I don't want that manipulation to be performed from the subform itself

But that is your only option.

A form is a class module. There are thousands of lines of code that you didn't write, you can't see, and you can't change, running to make an Access form work the way it works and you can't change that. The class module gives you "hooks" which are called event procedures. When certain actions are detected, the form's class module will call the associated event procedure. You don't have any programatic way to trigger an event. The user can do things like move the mouse, click in an event, choose the save option from the ribbon. All of these actions will trigger some corresponding event and if you have code in that event, Access will run your code at the appropriate point in time. You can indirectly cause Access to trigger events. Issuing a save command for example or using send keys to execute a key stroke will both cause Access to trigger an event and therefore cause your code to run.

In the Current event of the subform, you can run code that does stuff to the main form. placing values in controls will NOT run ANY control level events though. Just don't forget that.

Maybe if you told us what you want the mainform to do when the subform changes to a new record, we could provide more concrete answers. Maybe, the subform shouldn't be a subform at all. Maybe you've got the relationship backwards since it is most unusual for the subform to be incontrol over the main form. Do your children run your household?

 

[sonic8]

Maybe you've got the relationship backwards since it is most unusual for the subform to be incontrol over the main form. Do your children run your household?

That's a fit occasion for advocating events.

It's common for the children to inform the parents about their needs.
"Hey, Parent! I'm hungry!"
That's the OnSpeak event.
If the parent handles the Child.OnSpeak event, it will at least become aware of the information.
If and how the parent responds to this information is still up to them. - No children running the household. ;-)

 

[moke123]

Set MySubForm = Forms("sfmMySubform")

should probably be something like

Set MySubForm =  Me.Form.sfmMySubform

Whatever the reference to the subform is.

Personally I still favor the listbox solution although we still dont know what the entire process involves. A more detailed explanation may help.
I think you may be overcomplicating this.

 

[Pat Hartman]

I'm pretty sure @The_Doc_Man wrote a very long dissertation on why events to not get raised from within events in the not too distant past.

 

[KitaYama]

Set MySubForm = Forms("sfmMySubform")

should probably be something like

Set MySubForm =  Me.Form.sfmMySubform

Whatever the reference to the subform is.

This post from @MarkK suggests the opposite.
Set MySubForm = Me.sfmMySubform.Form

Subform Events

I've got a coworker that is developing an app. He's trying to dynamically assign a subform's RecordSource. He swears that (through Google) the only events available are the OnEnter and OnExit events. That's true for the subform control itself, but the FORM itself does support them. I briefly...

www.access-programmers.co.uk

But neither works for me. I receive Type Mismatch Error on your try, and nothing happens with Markk's suggestion when moving from one record to another.

Just for the sake of education, do you have a working demo how to to handle subform's events from (in) parent form?
Thanks.

 

[The_Doc_Man]

I'm pretty sure @The_Doc_Man wrote a very long dissertation on why events to not get raised from within events in the not too distant past.

The short answer is that since Access is single-threaded (including itself), and since all common events except TIMER are simply subroutine calls triggered by either Access or the user doing something leading up to the event, one event CANNOT interrupt another event. AND in fact the OnTimer event is also linearized. When the timer goes off, the interrupt code makes the timer code the next event to run regardless of what else was in the event queue. In essence, though from our viewpoint the events are asynchronous, the truth is that they are not. They are synchronous when you take into account the code being run as part of MSACCESS.EXE, the MAIN program segment of the Access user session. Therefore having interactions between parent and child forms will be an exercise in LINEAR logic, not interrupting logic. There will never be a time that parent and child code intertwine except by one doing an EXIT SUB and the next one starting a new subroutine from the front.

There is also the DoEvents conundrum, which basically traps the current information of the current event code to find the next instruction after the DoEvents call, and threads THAT into the tail end of the event queue. In effect, anyway - since we don't have an OpenSource for Access, we can only guess on the action mechanism. But what I said would be the ideal way to implement it.

 

[MajP]

This post from @MarkK suggests the opposite.
Set MySubForm = Me.sfmMySubform.Form

I am assuming @moke123 made a typo and meant to do the above.

Just for the sake of education, do you have a working demo how to to handle subform's events from (in) parent form?

See demo. In the subform click into different rows. Then edit a record in the subform and move to another row.

 

[sonic8]

The short answer is that since Access is single-threaded (including itself), [...]

I have a hunch, most process viewers would disagree with this assumption.

 

[KitaYama]

I am assuming @moke123 made a typo and meant to do the above.


See demo. In the subform click into different rows. Then edit a record in the subform and move to another row.

Million thanks.

 

[KitaYama]

I am assuming @moke123 made a typo and meant to do the above.


See demo. In the subform click into different rows. Then edit a record in the subform and move to another row.

I had a stupid mistake in my tests. I had to manually set subform's has module property to true.

Thanks for the lesson.