Make controls visible/invisible in combo box using VBA

[dackedyman]

I am new to VBA. I used code from this site to make subforms visible or invisible based off the selection from a combo box. The issue I am having is that if I select "Therapist" from the Combo12 combo box, the SpecialitiesSubFrm and ModelSubFrm are visible (which is what I want), but they are visible for everyone, including the "Providers" (they should not be visible for the Providers).

I want certain controls to be visible when I user selects "Therapist" from the Combo12 combo box. Likewise, I want certain controls to be invisible if the user selects the Provider option from the Combo12 combo box.

Here is a copy of the code I now have:

Private Sub Combo12_AfterUpdate()

If Me.Combo12.Text = "Provider" Then
Me.SpecialitiesSubFrm.Visible = False
Me.ModelSubFrm.Visible = False

ElseIf Me.Combo12.Text = "Therapist" Then
Me.SpecialitiesSubFrm.Visible = True
Me.ModelSubFrm.Visible = True

End If
End Sub

 

[theDBguy]

Hi. When you say “providers,” are you referring to your users?

 

[dackedyman]

It is a medical facility, which has Therapist and Providers (Medical doctors). The therapist have Specialties and Models sub forms, which should appear on the form when selecting Therapist from the drop-down menu. For the Providers I want those sub forms to be invisible since that information does not pertain to them.

 

[isladogs]

As you are using the after update event you should use the .Value property instead of the .Text property.
But the .Value property is the default so it can be omitted

 

[theDBguy]

It is a medical facility, which has Therapist and Providers (Medical doctors). The therapist have Specialties and Models sub forms, which should appear on the form when selecting Therapist from the drop-down menu. For the Providers I want those sub forms to be invisible since that information does not pertain to them.

It sounds like you're trying to implement some sort of user security where certain users would have permissions to do some things but not others. If so, I think there's plenty of demo files available for doing something like this. For one thing, you'll need to be able to identify the current user and have a table listing all the permissions for this user, so you can make the form hide the things they're not supposed to see.

 

[Pat Hartman]

Private Sub Combo12_AfterUpdate()
If Me.Combo12 = "Therapist" Then
    Me.SpecialitiesSubFrm.Visible = True
    Me.ModelSubFrm.Visible = True
Else
    Me.SpecialitiesSubFrm.Visible = False
    Me.ModelSubFrm.Visible = False
End If
End Sub

If only the Therapist can see those subforms then the above example is a better way to code it. You probably aren't going to add a third option to Therapist/Provider but should you ever, your code would break. Think of the above sample as defensive programming.

As isladogs said, the .value property (which is the default and can therefore be omitted) is the correct property to use. However, your problem may be that what you think is a string is actually not. What is the RowSource for the combo? Does it contain 1 column or 2? Is the first (hidden) column a number? If so, you either need to look for a numeric value since that is what referencing the combo gets you or change to referencing the second column which is the string.

If Me.Combo12.column(1) = "Therapist" Then

The columns of the RowSource are a zero-based array and so the first element is .Column(0) which is also the .Value property, the second column is .Column(1), the third is .Column(2), etc.

And finally, it is much better to use meaningful control names so if you create a control and bind it later, it is up to you to assign a meaningful name. Calling something Combo12 is meaningless and just makes your code harder to understand by someone else or even yourself if you have to go back to it next year.

 

[Mark_]

To add to Pat's good advice, I would normally add one SUB that sets all of the controls .Visible = False. I would call this FIRST, then only make visible what you need.

I'd also have them all default to .visible = false when you design the screen, thus cutting down on what ACCESS needs to do when they open the form.

The reason I'd do it this way is so I don't miss a control. One place to do things means you only need to update one spot. If you had 20 options, you'd still only have one place to turn them invisible, not 20.

 

[dackedyman]

Thank you Mark. I really appreciate your help. There is so much to learn!

 

[isladogs]

Agree with the comments added by Pat Hartman and Mark.
One more approach to consider is to base your code on the controls' Tag property. This is especially useful if you have a lot of controls to show/hide, enable/disable or lock/unlock at once. See my example database which demonstrates the idea: https://www.access-programmers.co.uk/forums/showthread.php?t=293439