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

[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.

 

[Uncle Gizmo]

Many people fall into the trap of creating multiple forms unnecessarily.

I call them "Cookie-Cutter Forms"

This is where you have one original form, and you want it to do something slightly differently. You make a copy and make a minor alteration to it. Now you have two forms! Carry on like this and you can end up with 20 or 30 forms which are basically the same.

The solution is to make a minor change to the form with VBA. This approach can reduce the number of forms from 20 or 30 to 2 or 3.

It sounds to me like you have this design problem. I think you should start by explaining why you have so many forms, we can then review your approach and see if we can come up with something better.

 

[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.

 

[Uncle Gizmo]

the different forms are because each subform goes to a different table

This indeed is a common reason for cookie cutter forms, and it's not a good reason!

The solution is to put all the data in a single table, add an extra field to the table to identify whether it is a plant or shrub or whatever. Now you only need one form.

 

[Pat Hartman]

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

This sounds like a design flaw. What is so different about these plants that requires separate tables.