Switch Subforms with an Option button

[Yarbz]

Greetings,
I'm a newby so this might be a silly question but...
I have four forms that have a lot of combo boxes in them that are complex.
I would like to create a form with an Option button and would like to have the Subform in the detail section change according to the Option selection, instead of switching back and forth between the four different forms.
The four forms are all attached to the same table but have different lengthy dropdowns for the different departments that use them.
Is there a way to code it to allow the a data entry person to switch between the dropdowns and not have to leave the form?
Thanks in advance.

 

[LaurieHamlin]

If I understand you correctly, this should do it.
Select Case OptionButton.value
Case 1: SubForm1.visible = True
SubForm2.Visible = False
SubForm3.visible = False
SubForm4.visible = False
Case 2: SubForm1.visible = False
SubForm2.Visible = True
SubForm3.visible = False
SubForm4.visible = False
...
End Select
I hope that helps.

 

[softhemc]

Probably because I'm lazy,but when I've done this in the past I have a simple Sub that sets all the relevant SubForms.Visible = False.

Ths is called immediately before the Select Case statement an then the Case options just need to set the appropriate SubForm as visible.

This only saves having to list all of the SubForms in each Case option, I'm not aware of any other benefits.

 

[Andrzejek]

I would go a step further...

SubForm1.visible = False 
SubForm2.Visible = False
SubForm3.visible = False 
SubForm4.visible = False 

Select Case True  
  Case OptionButton1.Value
    SubForm1.visible = True 
  Case OptionButton2.Value
    SubForm2.visible = True
  Case OptionButton3.Value
    SubForm3.visible = True
  Case OptionButton4.Value
    SubForm4.visible = True
 ... 
 End Select

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Yarbz]

Thanks LaurieHamlin and softhemc, I'll try it.
Though do I understand the premise correctly that I load all four Subforms into the Detail section as the final form, but have them as Visible=false ?
The reason why I'm asking is, it normally take about 1-2 minutes to load the form with a single subform in it. They have that many dropdowns in each form. One for every half hour 8-5 Mon-Fri (80 dropdowns per subform).
Lets find out if it works...
Thanks again.
Yarbz

 

[MajP]

They have that many dropdowns in each form. One for every half hour 8-5 Mon-Fri (80 dropdowns per subform).

If you are saying you have 80 fields with dropdowns, this a very non-normalized design. This will be very difficult to work with and manage. This can probably be radically simplified. If interested provide some information on your table design and how these forms work.

 

[MajP]

The correct code to show hide is simply. Assuming your options are in a option group.

Public Sub ShowHide()
  Me.sub1.Visible = (Me.Frame0 = 1)
  Me.sub2.Visible = (Me.Frame0 = 2)
  Me.sub3.Visible = (Me.Frame0 = 3)
  Me.sub4.Visible = (Me.Frame0 = 4)
End Sub

You want to only load the source object once it is selected and then never reload it. So the first time you select an option it will load the object and then after that show or hide it. This way the subforms only loads if needed and only one will load when the form loads. You will have to remove all source objects from your subform control. This is because I am pretty sure even though the subform is hidden it still loads in the background

So you would add this

Public Sub LoadFirstTime()
  Const source1 = "Table.tbl1"
  Const source2 = "Table.tbl2"
  Const source3 = "query.qry3"
  Const source4 = "Form.Frm2"
  Select Case Me.Frame0
    Case 1
     If Me.sub1.SourceObject = "" Then Me.sub1.SourceObject = source1
    Case 2
     If Me.sub2.SourceObject = "" Then Me.sub2.SourceObject = source2
    Case 3
      If Me.sub3.SourceObject = "" Then Me.sub3.SourceObject = source3
    Case 4
      If Me.sub4.SourceObject = "" Then Me.sub3.SourceObject = source4
  End Select
End Sub

You would call this code when the form loads and after update of the option group

Private Sub Form_Load()
  LoadFirstTime
  Call ShowHide
  
End Sub

Private Sub Frame0_AfterUpdate()
  Call ShowHide
  LoadFirstTime
End Sub

However, this is just a band-aid on a much bigger problem. If you redesign it correctly you will not need these workarounds.

 

[Yarbz]

OK, let me 'splain it as much as possible.
The main form is called "Scheduler Input" and there are about ten of them, one for each department. There's one department with four sub-departments, and it is with this part I'm working on.
The Scheduler Input shows the list of students by name and number and is from the "Student" table. When a student is chosen, the subform (which are from another table that is connected in the main form by student number) shows which half hour has not been assigned a class yet (is blank), and what has been assigned.
The subform has a dropdown for each half hour of class Mon thru Fri 8-5. The dropdowns are connected to the classes taught by the 40 or so teachers which are listed for each day, time and place by department.
Meaning, if you place all classes available in one dropdown for that half hour, you have anywhere from 10 to 100 classes to choose from in each dropdown. To keep down the frustration, I originally split these into four main forms, each for a specific group of teachers so that the dropdowns would be from 1 to 20 or so classes in that groups choices.
Now I'm being asked to combine them into one main form because they are not liking the fact that the new data-entry people are having to switch between the multiple forms to enter the information on one student for multiple teachers/disciplines, and they still don't want to wade through the long list of dropdowns either.
All the subforms enter the info into the same "Schedules" table. The use of the dropdowns is to insure the uniformity of the data is entered into that time slot.
These two tables are used to print off a report to give to each student, that has the day, time and location of the classes they are to attend.

If I could find a coding way to just change the info in the dropdowns instead of changing the screen with the dropdowns pre-coded, that would work too.
Like I said I'm a wanna-be developer, just not that advanced yet.
Hope this helps explain my problem better.
Yarbz

 

[LaurieHamlin]

ComboBox.RowSource = "Select [field for drop down] from [table name] where [table field] = '" & teacher.value & "'"
Depending if you want to lookup by teacher name. The table field would be the field that contains the teacher's name.

 

[Yarbz]

Hello Everyone,
I couldn't get it to be stable and function properly, so I ended up putting them in a Tab Control, with each Subform on a different tab.
Thank you to all who tried to help.
Yarbz