Access: display a different subform depending on another field?

[Guest]

I'm creating a form in Access, and I have a tab control in it. Under one of
the tabs, I want to display a subform, but not always the same one.

I have 5 different subforms created and I want to display a particular one
based on the choice the user makes in a field further up the form. Is that
possible?

 

[Marshall Barton]

I'm creating a form in Access, and I have a tab control in it. Under one of
the tabs, I want to display a subform, but not always the same one.

I have 5 different subforms created and I want to display a particular one
based on the choice the user makes in a field further up the form. Is that
possible?

If you can make all the subform controls the same size, then
just set the subform control's SourceObject property to the
name of the form you want it to display. A good place to do
this is in the tab control's Change event:

Select Case Me.tabcontrol
Case 3 ' no. of subform page
Select Case Me.furtherupcontrol
Case x
Me.subformcontrol.SourceObject = "form A"
Case y
Me.subformcontrol.SourceObject = "form B"
. . .
End Select
End Select

A similar technique can be used if the subform controls can
not be made the same size, but you would also need to set
the subform control's position and size properties.

Another approach would be to place all the subform controls
on top of one another and make all but the one you want to
display invisible. There are usually significant
performance issues doing it this way.

 

[Guest]

I tried this code. It doesn't return any sort of error, but it also doesn't
result in any changes.
did I do something wrong?

Private Sub TabControl_Change()
Select Case Me.TabControl
Case 3 ' no. of subform page
Select Case Me.Type
Case "Query Only"
Me.Criteria_Subform.SourceObject = 3 'choice of form on drop down
list (I also tried it with the name of the form, i.e. "Query Only Subform"
Case "Import"
Me.Criteria_Subform.SourceObject = 1
Case "Mapping"
Me.Criteria_Subform.SourceObject = 2
End Select
End Select
End Sub

 

[Marshall Barton]

I tried this code. It doesn't return any sort of error, but it also doesn't
result in any changes.
did I do something wrong?

Private Sub TabControl_Change()
Select Case Me.TabControl
Case 3 ' no. of subform page
Select Case Me.Type
Case "Query Only"
Me.Criteria_Subform.SourceObject = 3 'choice of form on drop down
list (I also tried it with the name of the form, i.e. "Query Only Subform"
Case "Import"
Me.Criteria_Subform.SourceObject = 1
Case "Mapping"
Me.Criteria_Subform.SourceObject = 2
End Select
End Select
End Sub

The Case statements need the tab page number of the page
with the subforms. You said that is only one page with a
subform, so there should only be one Case statement (unless
you are doing other things on other pages).

The SourceObject property must be set to the name of the
form object that you want the subform control to display.

Your explanation of how/which form is specified (in a combo
box??) is too vague for me to be specific, but at this point
I'm guessing it would be something along these lines:

Private Sub TabControl_Change()
Select Case Me.TabControl
Case 3 ' no. of subform page
Select Case Me.Type
Case "Query Only"
Me.Criteria_Subform.SourceObject = "Query Only
Subform"
Case "Import"
Me.Criteria_Subform.SourceObject = "import form"
Case "Mapping"
Me.Criteria_Subform.SourceObject = "map form"
Case Else
MsgBox Me.Type & " is not in the Select Case list"
End Select
End Select
End Sub

You need to verify that 3 is the correct tab page number.
Also, make sure of the names of the forms being used as
subforms.

You said it "doesn't result in any changes", but even with
the wrong form names, it should have displayed something
different, probably a blank subform control.