Multiple Select case statements no working

[john4282]

Hello,
I am in the process of developing a database to collect room and equipment usage data, with the rooms etc being spread across several "Zones" and each zone having its own unique set of rooms and equipment.

I have a drop down menu to allow users to select the zone they are inputting data for, and based on the selection in this dropdown I would ideally like to only show the questions relating to that particular zone.

I have so far used a "Select case" function to look at the property of the dropdown and show or hide controls accordingly. This generally works ok when the dropdown is used as I have placed the select case in the "OnUpdate" property of the dropdown.
My problem seems to arise when the form first loads and some or all of the controls remain visible, I would like it if the relevent controls only appear once the user has actually done someting on the form. So far I have placed the same "select case" statements in the Form_Load area to try to get the controls to remain hidden but to no avail. (alternative experiments have made the controls hide but stay hidden even if the user is looking back over the records already input)

Im assuming there is a more elequent way of handling this but I am still fumbling my way through VB / VBA so any pointers appreciated. (Current code shown below for comment / criticism)

Many thanks.

Option Compare Database
Private Sub Form_Load()
Select Case Zone3_CytoPrep
Case True
Me.Zone3_Cyto_Isolator.Visible = True
Case False
Me.Zone3_Cyto_Isolator.Visible = False
End Select
Select Case Zone
Case "Zone 3"
Me.Zone3_Rooms.Visible = True
Me.Zone3_CytoPrep.Visible = True
Me.Zone3_Dithranol.Visible = True
Me.Zone3_Flammables.Visible = True
Me.Zone3_PrepRoom1.Visible = True
Me.Zone3_PrepRoom2.Visible = True
Me.Zone3_PrepRoom3.Visible = True
Case Else
Me.Zone3_Rooms.Visible = False
Me.Zone3_CytoPrep.Visible = False
Me.Zone3_Dithranol.Visible = False
Me.Zone3_Flammables.Visible = False
Me.Zone3_PrepRoom1.Visible = False
Me.Zone3_PrepRoom2.Visible = False
Me.Zone3_PrepRoom3.Visible = False
End Select
Select Case Zone3_CytoPrep
Case True
Me.Zone3_Cyto_Isolator.Visible = True
Case False
Me.Zone3_Cyto_Isolator.Visible = False
End Select
Select Case Zone3_Dithranol
Case True
Me.Zone3_Dithranol_Isolator.Visible = True
Me.Zone3_CoalTarCabinet.Visible = True
Case False
Me.Zone3_Dithranol_Isolator.Visible = False
Me.Zone3_CoalTarCabinet.Visible = False
End Select
Select Case Zone3_Cyto_Isolator
Case "Not Used"
Me.Zone3_Cyto_Isolator_reason.Visible = True
Case Else
Me.Zone3_Cyto_Isolator_reason.Visible = False
End Select
End Sub
 
Private Sub Zone_AfterUpdate()
Select Case Zone
Case "Zone 3"
Me.Zone3_Rooms.Visible = True
Me.Zone3_CytoPrep.Visible = True
Me.Zone3_Dithranol.Visible = True
Me.Zone3_Flammables.Visible = True
Me.Zone3_PrepRoom1.Visible = True
Me.Zone3_PrepRoom2.Visible = True
Me.Zone3_PrepRoom3.Visible = True
Case Else
Me.Zone3_Rooms.Visible = False
Me.Zone3_CytoPrep.Visible = False
Me.Zone3_Dithranol.Visible = False
Me.Zone3_Flammables.Visible = False
Me.Zone3_PrepRoom1.Visible = False
Me.Zone3_PrepRoom2.Visible = False
Me.Zone3_PrepRoom3.Visible = False
End Select
End Sub
Private Sub Zone3_Cyto_Isolator_AfterUpdate()
Select Case Zone3_Cyto_Isolator
Case "Not Used"
Me.Zone3_Cyto_Isolator_reason.Visible = True
Case Else
Me.Zone3_Cyto_Isolator_reason.Visible = False
End Select
End Sub
Private Sub Zone3_CytoPrep_AfterUpdate()
Select Case Zone3_CytoPrep
Case True
Me.Zone3_Cyto_Isolator.Visible = True
Case False
Me.Zone3_Cyto_Isolator.Visible = False
End Select
End Sub
Private Sub Zone3_Dithranol_AfterUpdate()
Select Case Zone3_Dithranol
Case True
Me.Zone3_Dithranol_Isolator.Visible = True
Me.Zone3_CoalTarCabinet.Visible = True
Case False
Me.Zone3_Dithranol_Isolator.Visible = False
Me.Zone3_CoalTarCabinet.Visible = False
End Select
End Sub

 

[DCrake]

Select Case Zone3_CytoPrep
Case True
Me.Zone3_Cyto_Isolator.Visible = True
Case False
Me.Zone3_Cyto_Isolator.Visible = False
End Select

Replace with

Me.Zone3_Cyto_Isolator.Visible = me.Zone3_CytoPrep

You seem to be repeating alot of code here. If the form is a bound form use the On Current Event and call a sub to set the properties

Call ToggleControls()

Sub ToggleControls

Me.Zone3_Cyto_Isolator.Visible = me.Zone3_CytoPrep
etc

End Sub

Then on the after update of your controls use the sub again

 

[john4282]

Thanks I will give that a try over the weekend and see how I get on.