Chameleon subform

[knauftj]

I was wondering if it is possible to have the available fields in a subform change based on a combobox in the parent form? I have a form that has a combobox and I would like the fields available in the subform to change based on what is selected in the combobox. Thanks.

Jon

 

[SydneyGeek]

Yes, but how you approach it can vary.

Probably the simplest way is to set up a series of tabs on the subform, with sets of fields on each tab. Then, instead of looping through every control (and having to update the code whenever you add a control to the form), you merely hide and display tabs and provide the users with the set of controls that you have placed on that tab. This works well if you have the subform displayed as a form, not in datasheet format.

If you're using datasheet-style subforms, consider placing multiple subforms on top of each other and toggling their visibility.

Whichever route you use, grouping the controls and then hiding or showing the groups is much easier to manage.

Generically, you do it by using the AfterUpdate event of the combo --

Private Sub MyCombo_AfterUpdate()
  Select Case MyCombo
    Case "Alpha"
      FirstSubForm.Form.Visible=True
      SecondSubForm.Form.Visible=False
    Case Else
    '... etc
  End Select
End Sub

The above is for a subform. To use for a tab, change FirstSubForm.Form to FirstPage, etc

Change names to suit your setup

Denis

 

[knauftj]

Re:

Thanks Denis. I was thinking of using the Visible control but wasn't sure of the code for subforms. Thanks again.

Jon