Change RecordSource in Tabbed Subforms

[matchorno]

I have a main form called "frmqryMainSort" and a subform called, "frmqryMainSortSubform". There are two tabs on the main form. The subform was placed on the first tab. Then a copy of the same subform (copy/pasted) was placed on the second tab. I want them to be the same exact subforms.



The subform's record source is set to a query called, "qryMainSort". I want that to be the default recordsource when the user opens up the main form with subform. Since, the first tab is displayed by default when opened, and I want the first tab to have the "qryMainSort" records.

Now, depending on which tab is chosen, I want the recordsource to change on the subform. Here is the code I have on the "On Change" event of the tab control:

 
If MainSortTab.Value = 0 Then
  Me.frmqryMainSortSubform.Form.RecordSource = "qryMainSort"
ElseIf MainSortTab.Value = 1 Then
  Me.frmqryMainSortSubform.Form.RecordSource = "qryMainSortPTI"
End If

Now here's the weird behavior..
If I set the subform's recordsource to be "qryMainSortPTI" by default (instead of "qryMainSort") in the properties tab of the subform, everything works fine. When I choose the different tabs, the recordsource changes as desired. However, the problem is that I want the default recordsource to be the other query, "qryMainSort" when the user opens up the form.

If I set the subform's recordsource to be "qryMainSort" by default in the properties tab of the subform, the recordsource won't change when I move from tab to tab. When I click on the second tab, the source looks like "qryMainSort" (which is wrong). When I click on the first tab, it flashes the "qryMainSortPTI" records, then changes real fast to the "qryMainSort" records. So I am essentially getting the "qryMainSort" records on both tabs.

Why does it work if I set the default recordsource to be one over the other? Help!!! Thanks!

[RuralGuy]

I take it this code is in the Change event of the MainSortTab tab control that has two tabs, correct?

[Peter68]

Execute the code by calling the OnChange sub, from the Form's OnOpen or OnLoad.

[matchorno]

Thank you for your replies. Yes, the code is in the OnChange event and there are two tabs. I actually figured out the answer. I posted on a different forum before and the answer was posted there. I'm posting the answer here for posterity, in case someone else happens upon this thread in the future. The problem was that I was referring to the subform and not the subform control. I had to name the two different controls, two different names and refer to them instead of referring to the actual subform.

"If you are using the same subform then remember it is just that; the same one. Putting a subform control on the second tab and using the same subform is just an illusion. The subform is the exact same one. So you would need to set its starting record source to be what you want it to be when first visible (I assume on Tab 1). Then in the tab control's On Change event you need to do the change there. The default of the second tab will always be the one for the first tab.

And I noticed in your code that you are using the same exact subform control name for both which it can't be. The first one is probably named
frmqryMainSortSubform

But the one on the second tab is going to be named something different. Because when you use subform syntax you refer to the Subform CONTROL name (name of the control that sits on the parent form and HOUSES the subform) and NOT the subform name itself (unless they share the same name).

Second, I would suggest that you don't use the Value of the tab control as your defining feature. Tabs might be moved around at some point and so you will find that you would have to go make code changes. Use the actual names of the tabs themselves."

[RuralGuy]

Thanks for posting back with your solution. It will be very helpful to others that read this forum. I went ahead and marked this thread as Solved for you.