Form/Query couple stops working once embedded in another Form

Question

[Working in MS Access 2019 Professional Plus 2019. Not that it matters though.]
My setup can be narrowed down to this hierarchy, which works perfectly fine on its own :

'------> Hierarchy of objects :

"myMainForm" contains :
    "select_client" (ComboBox based on "client" table)
    "select_status" (ComboBox based on "status" table)
    ' and some multi-select checkboxes that forbid use of Master/Child feature
    "mySubForm" 
        "Source Object" : "myQuery" ' "mySubForm" not saved with an explicit name, local to "myMainForm"

"mQuery" filters the table "course" :
    "client_ID" criteria : [Forms]![myMainForm]![select_client]
    "status_ID" criteria : [Forms]![myMainForm]![select_status]

'------> "myMainForm" VBA to requery on change (could be performed with Macros) :

Private Sub select_client_Change()
    Me!mySubForm.Requery
End Sub

Private Sub select_status_Change()
    Me!mySubForm.Requery
End Sub

Then I try to embed "myMainForm" in a higher level form (let's say "myNavForm") :

"myNavForm" contains :
    "myMainForm" contains :
        ' same as above from there
        "mySubForm"

When I try to run "myNavForm", I get prompted for [Forms]![myMainForm]![select_client] and [Forms]![myMainForm]![select_status], and "myMainForm" stops working entirely.

I first thought the problem was related to the ComboBox values not being loaded yet when "myQuery" is triggered, so I added the following VBA to "myMainForm" :

Private Sub Form_Load()
    Me!mySubForm.SourceObject = "Query.myQuery"
    Me!mySubForm.Requery
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Me!mySubForm.SourceObject = ""
End Sub

But the problem remains the same. I guess it comes from a scope / path mistake on my side, but I can't figure it out. I tried absolute paths in "myQuery" as you can see above. I also tried using relative paths with .Parent but couldn't make "myMainForm" to work with it (without even embedding in "myNavForm").

Therefore, I am stuck and screwed and desperate and I want my Node/MongoDB environment back, but I have to do this in Access (don't ask !).

Any idea how to make it work with embedding ? As much as possible, I'd prefer sticking to that setup and avoid entering more complex VBA (the syntax gives me headaches), but I'm open to it if needed ;-)

Thx for any kind of help to free me from that nightmare !

---

[EDIT : SOLUTION IN MY USE CASE BASED ON ACCEPTED ANSWER (for future readers !)]

Thanks to @Olivier , here is the solution working flawlessly :

'------> Hierarchy of objects :

"myMainForm" contains :
    "select_client" '(ComboBox based on "client" table)
    "select_status" '(ComboBox based on "status" table)
    "mySubForm" 
        "Source Object" : "myQuery" 'remove all criteria from the query !

' ------> Handle the rest in VBA :

'Lets write this only once...
Private Sub updateResult()
    mySubForm.Form.Filter = "client_ID=" & select_client & " AND status_ID=" & select_status
    mySubForm.Form.FilterOn = True
End Sub

'Apply filter with default values upon loading
Private Sub mySubForm_Current()
    updateResult
End Sub

'Update filter whenever a control is updated
Private Sub select_client_AfterUpdate()
    updateResult
End Sub
Private Sub select_status_AfterUpdate()
    updateResult
End Sub

All in all, I finally have a nice framework to update any multi-criteria query on the fly after any control update and view the result immediately on the form, behaving just as any modern GUI should ! Thx again Olivier !

Answer 1

This is because then your select_client has another path. Now it is

[Forms]![myNavForm]![myMainForm].Form![select_status]

where myMainForm is now a subform-control. It may have a different name than the contained form.

Instead of including such paths into the query, try to filter the subform like this

mySubForm.Form.Filter = "client_ID=" & select_client & " AND status_ID=" & select_status
mySubForm.Form.FilterOn = True

You would do so in the after update events of these two fields and on the form OnCurrent event.