Choosing subform to display based on combo box

[RussCRM]

Is there a way to use a combo box to choose which subform you want to
display?

Basically, I have different information from different queries that I
want a user to be able to display based on their choice from a combo
box. I have a form with a combo box with Option1 and Option2. I have
two different subforms based on which set of information (which query)
they want to display. If they select Option 1, I want the subform to
display information from my frm_Option1Subform and the same for Option
2, etc.

Is there any way to go about this? Thanks!

 

[Graham Mandeno]

Hi Russ

I would create a small table SubformSelect with the following fields:
SubformName
OptionDescription

If the LinkMasterFields and LinkChildFields are different for different
subforms, then add fields for them too.

Also, if you do this in several places in your application, you might want
to add another field (ComboID) to identify the selection of records to be
used in each given combo box.

Set the properties of the combo box as follows:
RowSource: Select * from SubformSelect where ComboID=1
BoundColumn=1
ColumnCount=2 (or 4 if you change link fields)
ColumnWidths=0 (or 0;;0;0 if you change link fields)

Use the AfterUpdate event of your combo box thus:

Private Sub cboSubformSelect()
Me.Painting = False
With Me![NameOfSubformControl]
.SourceObject = cboSubformSelect
' if you need to change link fields:
.LinkMasterFields = cboSubformSelect.Column(2)
.LinkChildFields = cboSubformSelect.Column(3)
End With
Me.Painting = True
End Sub

You could of course hard code all the names of subforms and link fields, but
I thing using a table-driven approach would make your application easier to
modify in the future.