Case statement

[kbrooks]

I have a combo box called OccurrenceCategory, and several text boxes following it. I want certain text boxes to be visible depending on which value is picked in the combo box.

For example, if "Fall" is the choice made in the combo box, I want the "IfFall" text box to be visible. For all other choices, I don't want it visible.

If "Medication" is the choice made in the combo box, I want the "IfMedication" and "MedicationRoute" text boxes to be visible. For all other choices, I don't want them visible.

I started by making the properties of all text boxes "Visible?" a NO, and figured the code would turn them "on" when needed. I've used a If/Then statement before but never for more than one option in a combo box. I assume I need a case statement here? My VB knowledge is very limited, so any help you can give me is MUCH appreciated!!

 

[D-Fresh]

Use this code in the onChange event of the combo box. It will first make all the controls invisible, except the combobox, and then make the appropriate textboxes visible. Add case statements as needed to accommadate all of your choices...


Private Sub OccuranceCategory_Change()
Dim ctl As Control

For Each ctl In Controls
If ctl.Name <> "OccuranceCategory" Then
ctl.Visible = False
End If
Next

Select Case Me("OccuranceCategory")
Case "Fall":
Me("ifFall").Visible = True
Case "Medication":
Me("ifMedication").Visible = True
Me("MedicationRoute").Visible = True
End Select
End Sub


Hope that helps!

Doug

 

[kbrooks]

Thanks Doug! One more question for you. This is the code I entered:

Private Sub OccurrenceCategory_Change()
Dim ctl As Control

For Each ctl In Controls
If ctl.Name <> "OccurranceCategory" Then
ctl.Visible = False
End If
Next

Select Case Me("OccurranceCategory")
Case "Fall":
Me("ifFall").Visible = True
Case "Medication variance":
Me("ifMedication").Visible = True
Me("MedicationRoute").Visible = True
Case "Equipment or supply problem":
Me("IfEquipment").Visible = True
Me("EquipmentType").Visible = True
Me("EquipmentSerial").Visible = True
Me("EquipmentDisposition").Visible = True
Case "Infection":
Me("InfectionType").Visible = True
Case "Procedure or treatment variance":
Me("ProcedureTreatmentVarience").Visible = True
End Select
End Sub

When I try to test it, I get an error saying I can't hide the control that has the focus. When I click on debug, it highlights the ctl.Visible=False line.

Any ideas?

 

[D-Fresh]

try to put this line in at the top after the dim statement...

me("OccuranceCategory").setfocus

this will set the focus on a control that you won't be setting to visible=false. I've seen this error before around the boards, so I hope that works out for you.

Doug

 

[kbrooks]

Thanks again, Doug. I put in the line you suggested, but still got the same set focus error.

?!?!?!?!?!?

 

[D-Fresh]

Alright, make sure you have everything spelled correctly. I just ran the code with the combo box name spelled incorrectly and I got that same error... After I fixed the spelling mistake, everything ran fine... Also, which control is it stopping on? If you don't know how to find out, put this code before ctl.visible = false

msgbox ctl.name

I hope that spelling thing will do the trick for ya..

 

[kbrooks]

That was exactly my problem, I was spelling it occurrance instead of occurEnce. Thank you.

Now, however, once I pick a choice from the combo box, EVERYTHING ELSE on the form is invisible, except the combo box I'm in and the 1 or 2 fields I've set to be visible.

Am I a major pain or what?

 

[D-Fresh]

Yes! Nah, just kidding, I've had my problems too. Ok, it takes a little extra code, but you can just set the fields that you're using with the combo box as visible=false instead of using the for..next loop. Do this by adding at the top..

me("ifFall").visible = false

Do this for all the boxes you don't want visible until you select. You can also delete the for each ctl in Controls loop at the top. That should do it.

Doug

 

[kbrooks]

I realize I'm a huge pain. I know absolutely zilch about VB and got totally thrown into this. They bought me a book and figured I could wing it!

One more question and I'll leave you alone.
This is the code I have now:

Private Sub OccurrenceCategory_Change()
Dim ctl As Control
Me("OccurrenceCategory").SetFocus

Me("IfFall").Visible = False
Me("ifMedication").Visible = False
Me("MedicationRoute").Visible = False
Me("IfEquipment").Visible = False
Me("EquipmentType").Visible = False
Me("EquipmentSerial").Visible = False
Me("EquipmentDisposition").Visible = False
Me("InfectionType").Visible = False
Me("ProcedureTreatmentVarience").Visible = False

Select Case Me("OccurrenceCategory")
Case "Fall":
Me("ifFall").Visible = True
Case "Medication variance":
Me("ifMedication").Visible = True
Me("MedicationRoute").Visible = True
Case "Equipment or supply problem":
Me("IfEquipment").Visible = True
Me("EquipmentType").Visible = True
Me("EquipmentSerial").Visible = True
Me("EquipmentDisposition").Visible = True
Case "Infection":
Me("InfectionType").Visible = True
Case "Procedure or treatment variance":
Me("ProcedureTreatmentVarience").Visible = True
End Select
End Sub

But I get a run time error, saying "Object doesn't support this property or method."

I'm looking in my manual, also, but so far, you're MUCH more of a help!

 

[D-Fresh]

Where are you getting the error??? When the error pops up, hit debug, and tell me which line is highlighted...

 

[kbrooks]

It highlights the
Me("EquipmentSerial").Visible = False
line.

I had someone suggest to move it to the AfterUpdate field instead of the OnChange, but it makes no difference.

 

[D-Fresh]

Again I suggest double-checking the spelling, but that's probably not it... Other than that, I don't really know why it's doing that... Any control should support the "visible" method... It's odd... Sorry I can't help here...

Doug

 

[kbrooks]

Ok, well thanks for all your help! I wouldn't have got this far on my own!!

 

[Rich]

Are you sure EquipmentSerial is the name of the control and not the field name?

 

[kbrooks]

I finally got it working. Yes, EquipmentSerial was the name of both. But I had several fields that, within properties, the "Name" and the "Field" said different things. I went through the whole form, field by field, and corrected them all. And now my form runs perfectly!!

Thank you thank you thank you!!

 

[misscrf]

This is a great thread. I was looking to do just this and I have been working on it on and off all day. Got me right to my answer. Thanks both!!!

 

[gemma-the-husky]

seriously - if you are using text values in a combo box (value list maybe its ok), but if its coming from a table field - then you should get used to the habit of having a numeric identifier, and using a two column combo box

over time, you will find it is
a) more efficient and
b) much easier

to manage numbers, rather than text.

----------
you dont want to have specific text values like "FAIL" or "PASS" - What happens if you want to change the description of "PASS" to "SUCCESS" - you have the devil's own job both remembering to do it, and then finding and updating the word "PASS" everywhere you may have used it in your code.