Form display one of two sub-form conditionally

[WCStarks]

I would like to make one of two continuous sub-forms display conditionally. I have a Members Navigation Sub-form, which contains a number of Tabbed pages. When the Transaction page is selected, I would like to have it display either of two Sub-Forms, depending on which [member ID] is selected in the Members form. I understand some thing like this in the main form's Current event property may do it with a normal main form and sub-form context:

   Select Case [Member ID]
    Case 34
        [Team Trans Subform].Visible = True
    Case Else
        [Team Mem_Ledgers Subform].Visible = True
    End Select

But where it needs to only apply when the Transaction page is selected, I am not sure how to make that happen. Here is a screen print that shows the context:



Perhaps it is not even possible in this Tabbed context. Any ideas?

[June7]

The tab control is irrelevant in code referencing the subform. The complication is the subforms are on a navigation subform which is on a main form. Regardless of what event is used, this path reference is tricky.

Understand that subform is composed of a subform/subreport container control that holds a form or report object. Must reference the subform container control. Advise to name the container different from the object it holds, such as ctrTrans.

I am sure this has been discussed and described in other threads but finding again won't be easy.

I don't use navigation form structure but even with normal form structure, getting the path reference correct can be a challenge.

So if code is behind main form, something like:

Me.NavigationSubform.onesubformcontainername.Visible = Me.[Member ID] = 34
Me.NavigationSubform.othersubformcontainername.Visible = Me.[Member ID] <> 34

or change the SourceObject property of container control.

If Me.[Member ID] = 34
     Me.NavigationSubform.ctrTrans.SourceObject = "form name here"
Else
     Me.NavigationSubform.ctrTrans.SourceObject = "other form name here"
End If

[WCStarks]

Ok, that looks good, but changing the sub-form is only relevant when the Transactions tab (page 35) is active. I do not know how to ask if that tab control is active.

[June7]

Really doesn't matter if the Transactions tab is active or not. Could just set the appropriate form anyway. However, if you prefer, use the tab control Change event to determine if the Transactions page is selected by testing the value of the tab control. Its value will be the equivalent of the tab page PageIndex property of the clicked tab. Index begins with 0. Using this event should allow simpler referencing because the code is behind the navigation subform not the mainform. Just remove NavigationSubform from the example code shown earlier.

If Me.tabcontrolname = 1 Then
'set the subform
End If

I suggest using tab control event because even though the tab pages show events, I have never been able to get them to do anything.

[ArviLaanemets]

Instead of setting different form control into subform control, you may consider having 2 subforms of same width and height overlaying at same position. Current event of members form sets one subform visible, and another hidden, depending on active member.

[WCStarks]

Ok. I already had some code in the Members form On Current event property. I presume this new code will not run correctly on a new record, so I have put this new code in with the existing If condition modified as follows:

Public Sub Form_Current()
If Not Me.NewRecord Then
    Me.Joined = ELookup("[Status_Date]", "Membership", "[Status] = ""Joined"" and [Member_ID] =" & [ID], "[Status_Date] Desc")
    Me.Resigned = ELookup("[Status_Date]", "Membership", "([Status] = ""Resigned"" or [Status] = ""Terminated"" or [Status] = ""Emeritus"" or [Status] = ""Retired"") and [Member_ID] =" & [ID], "[Status_Date] Desc")

    If Me.[ID] = 34 then
         Me.CtrLedger.SourceObject = "Team Trans Subform"
    Else
         Me.CtrLedger.SourceObject = "Team Mem_Ledgers Subform"
    End If
End If
End Sub

When I ran the original code, it choked on NavigationSubform, with "Compile error: Method or data member not found". Apparently, using the NavigationSubform clause is not needed, as when I deleted them, it works as written. Thank you so much. This is great!

[WCStarks]

Thanks, I do have two subforms which are the same width and height. The code above calls one or the other subform. Would overlaying them make switching between them more efficient?

[ArviLaanemets]

TWould overlaying them make switching between them more efficient?

The only difference is, are you wasting valuable form space or not (in case you don't overlay them, you'll always have an seemingly empty area on your form).

[June7]

Understand that subforms are composed of a subform/subreport container control which holds a form or report object. The code in post 6 changes 1 container SourceObject property. Alternative is two container controls of the same size overlaying each other. I have done this.

Both of these options are described in post 2.

Keep in mind subforms actually load before the main form, weird but true. The more controls and data a form has, the slower it loads. I have forms with as many as 6 subforms and pulling a lot of data. I do see a definite slowdown compared to much simpler forms. Loading a subform later only when it is needed can speed up the main form load. This is supposed to be an advantage of Navigation form. Only one of the forms is actually loaded at a time which is why one of the forms cannot reference another. Unlike with a tab control where all subform containers load the object if SourceObject property is set.

[WCStarks]

Well, whether doing the Visibility method or the Source Object Method, It is probably good practice to only execute the code when needed. To that end, how do I determine if the tab container is active? I've looked around and can't seem to find how to do that.

[June7]

Described in post 4.

[WCStarks]

I did as indicated Post 4, but access doesn't like it.

Public Sub Form_Current()
If Not Me.NewRecord Then
    Me.Joined = ELookup("[Status_Date]", "Membership", "[Status] = ""Joined"" AND [Member_ID] =" & [ID], "[Status_Date] Desc")
    Me.Resigned = ELookup("[Status_Date]", "Membership", "([Status] = ""Resigned"" OR [Status] = ""Terminated"" or [Status] = ""Emeritus"" OR [Status] = ""Retired"") AND [Member_ID] =" & [ID], "[Status_Date] Desc")
    
    If Me.TabControlName = 1 Then
        If Me.[ID] = 34 Then
             Me.CtrLedger.SourceObject = "Team Trans Subform"
        Else
             Me.CtrLedger.SourceObject = "Team Mem_Ledgers Subform"
        End If
    End If
End If
End Sub

[June7]

Well, what is the name of your tab control? Use that instead of TabControlName.

[WCStarks]

Yes. Sorry. Thank you for your patience. I realized that after I had posted it, then I got called away before I could correct it. I tried using CtrLedger, as suggested, but Access doesn't like it. Access reports a type mismatch. Is that supposed to be a logical expression?

I suppose I should have made this another post. Besides knowing when the CtrLedger Tab Control is active for switching forms, I also need it for displaying a control button when the CtrLedger Tab Control is active. I need to be able to print the ledger for the current Team or Member record. Then I need for the button to disappear when the CtrLedger TC is not active.

This is really frustrating. I have spent considerable time searching the Internet, but have not been able to find a concise explanation of how to check if a certain Tab Control is active. Why is it so difficult?

[WCStarks]

I think I need to clarify the context of the Tab Control in Design Mode.
1) When I click on the "Ledgers" tab (old display name, "Transactions"), I get a bold rectangle, and the Name property is "Page 35".
2) When I click on the embedded sub-form once, I get a thin rectangle, the sub-form container, which I have named, "CtrLedger", as you recommended.

So, for determining the active Tab Control, I now realize I should not be trying to reference "CtrLedger". Since the Tab Control name is "Page 35", I have tried using it as follows:
"If [Page 35] = 1 Then", as in Post 4. But, I get a run-time error 438: "Object doesn't support this property or method."