Use of recordsource in main/subform causes Open Event in subform NOT to execute...

I use a mainform (Order) with a subform (order Details). However, I change the recordsource dynamically between table sets (order, orderitems) and (quote, quoteItems). This works fine as long as i stay within the same table set. As soon as I switch then from one set to the other, the subform will no longer execute (neither open nor the load event). I then have to reenter the load event in the subform to reestablish the event. Again, it will work perfectly but as soon as I change again to the other recordset I will experience the same problem (it makes no difference if i change from order to quotes or vice versa). Am really a bit desperate as I have no clue why it is behaving in this way. Any help is greatly appreciated!! Thanks Michael
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Try first to set the recordsource of the subform to some empty recordset, then change the recordsource of the main form, and then change the recordsource of the subform.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mpimAuthor Commented:
Thank you so much Gustav - this has indeed resolved the issue. However, i now have a different problem. Whenever I switch between the table set the system gets confused with the new record pointers. Once I switch it will always edit the last record of the new table set rather than adding a new record. Even if I try to force with DoCmd ... gotoRecord acnewrecord etc it will still not create a new record. However, if i remain within the same table set - I have absolutely no problems.... Again, thank you so much !
Gustav BrockCIOCommented:
It might be simpler to have two subforms - and hide the one not in use.
 Acronis Global Cyber Summit 2019 in Miami

The Acronis Global Cyber Summit 2019 will be held at the Fontainebleau Miami Beach Resort on October 13–16, 2019, and it promises to be the must-attend event for IT infrastructure managers, CIOs, service providers, value-added resellers, ISVs, and developers.

Mark EdwardsChief Technology OfficerCommented:
I take it that what you mean by "change the recordset dynamically between table sets" is that you have two different recordsources using different tables/queries and you are trying to juggle switching between the two sets of recordsources and getting the subform to properly synchronize with the main form.
It also sounds like you don't have a public procedure in your subform that you can call from your main (parent) form to initialize your subform whenever you change recordsets - that, apparently, is done in your subform's Open & Load events.

If you want your subform to run its Open and Load events after it is already open on the main form, you can simply set your main form's subform control's SourceObject to itself.  For example:
Me.MainformName.SubformControlName.SourceObject = Me.MainformName.SubformControlName.SourceObject

Or you can use the name of the subform:
Me.MainformName.SubformControlName.SourceObject  = "frmSubformName"
Either way, that will cause the subform to re-run it's Open and Load events.

Also, moving your Open & Load processes to a public procedure that you can call from the main form and from the open/load events can also work.
Not knowing exactly how you're changing your recordsources and getting your subform to try and synchronize makes it a little difficult to give you a perfect solution that will work in all cases.
Mark EdwardsChief Technology OfficerCommented:
Another thing to consider is that subform code runs before the main form code when the main form first opens.  Sometimes you can gain more control over what happens by leaving the subform's SourceObject blank at design time and setting it dynamically during the main form's code execution.

That little trick has solved a lot of subform control issues for me.
mpimAuthor Commented:
Thank you so much for this advice - it brought excellent results! Cheers Michael
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.