Make subform visible based on combo box selection; avoid nested IIF with Switch or Select Case

[TB11]

Hi.

#1: I have been trying to figure out how to make a subform visible based on combo box selection on main form. I've tried doing this with both after update event and the on click event with a command button, but I just got error after error.

Main form "frmPlants", has combo box "cboPlantType", with just three values ("flower", "shrub" and "tree").

Three subforms "frmPlants_Flower_subf", "frmPlants_Shrub_subf" and "frmPlants_Tree_subf", with the fields limited only to the ID field, the foreign key from the Plants table and the Plant name.

If the main form cboPlantType has "tree" chosen, I would like to have the appropriate subform frmPlants_Tree_subf become visible so data entry can be done.

#2: I was hoping to use either the Switch or Select Case functions to avoid what can be a nested IIF nightmare.

If anyone can help, I would appreciate it.

 

[theDBguy]

Hi. Have you considered using a Tab Control instead?

 

[TB11]

A tab control won't really work for what I need. I envision this scenario having 20-30 subforms. I'm trying to take the guesswork out for the data entry, and faster function (instead of going to a particular tab and finding the subform.).

Too, each of my subforms (tree, shrub and flower) have their own individual tables.

 

[MajP]

If you built a table for the combobox with two fields.
displayName
formName

So you would have for example
Flower frmPlants_Flower_subf
Shrub frmPlants_Shrub_subf

Then your code would be in the combobox after update

Me.NameOfYourSubFormControl.sourceObject = me.cboPlantType.column(1)

 

[arnelgp]

A tab control won't really work for what I need

it is worth considering.
you can make the "tabs" not shown.

 

[TB11]

@Uncle Gizmo the different forms are because each subform goes to a different table.

But, @arnelgp, @MajP and @Uncle Gizmo thank you all, I got it work.