Customize subform based on value selected on form

  • Thread starter Thread starter Terye N
  • Start date Start date
T

Terye N

Is there a way to make only certain fields appear on a subform, based on a
record selected on the main form? If I select "Medical Unit" for example, I
need all 11 subforms with all fields on each subform to appear. If I select
"Acccounting" I only need two of the subforms to appear, and only some of the
fields on those subforms. Other units will have varying combinations of
subforms/fields.

I have started with a table listing each unit, and then a yes/no field for
each field name; but I am not sure how to link that information within the
form to make each subform and field appear or disappear. Any help would be
appreciated!
 
Eleven subforms? That's a bunch.

I've done things like that in code for a calendar that eliminated week ends
but it was a lot of work.
If you have 11 that are appearing and disappearing, why not just add a
couple more that show when you select accounting.
Except for umteen Me!subform1.visible = true (or a loop)
ther is no othr coding required.
 
I assume that with this many subforms, they are probably all on tabs, so the
first thing I would do, is in the AfterUpdate event of the combo box (I
assume it is a combo) where you select the "unit", I would have some code
that looks something like:

Private Sub cbo_Unit_AfterUpdate

me.tab0.visible = (me.cbo_Unit = "Medical Unit") OR _
(me.cbo_Unit = "yyy")
me.tab1.visible = (me.cbo_Unit = "Accounting") OR ...
me.tab2.visible = (me.cbo_Unit = "Medical Unit") OR _
(me.cbo_Unit = "Radiology") OR _
(me.cbo_Unit = "Lab")

End Sub

That will take care of the subforms not showing up if they shouldn't for a
particular "unit". Regarding the visibility of controls on the subforms,
that is a little more difficult, but not much.

In the Current event of the subform, I would put code similar to the
following. This would make the control only visible to the accounting
department. The problem with this code is that it will error out if you try
to open the subform as a stand-alone form.

Private Sub Form_Current

'This would make the control only visibile for the "accounting" unit
me.lbl_SomeControl.Visible = (me.parent.cbo_Unit = "Accounting")
me.txt_SomeControl.Visible = (me.parent.cbo_Unit = "Accounting")

End Sub
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Back
Top