Enabling field visibility from combo box selection in Access 2007

Question

I have a form in Microsoft Access 2007 called System, and a combo box called Utility within this form. Below this is yet another combo box called Utility_FOO, and I have disabled its visibility by default. One of the options in Utilities is a checkbox labeled 'FOO.' I want Utility_FOO to become visible whenever FOO is selected.

I have tried creating a subroutine in Visual Basic that checks whether or not FOO is selected every time I select an item from the list (using onUpdate), but I cannot figure out how to check that specific entry. Is there a simple way to do this?

Thanks!

I not quite sure; I'm quite new to Access. When I press the arrow to view the available choices, there is a checkbox next to each one of them, and I can check any number of them I like. — Bucket, Oct 01 '10 at 20:25
It sounds to me like you want this to happen in the AfterUpdate of your control. Also, if there are checkboxes in it, it sounds like it's a multi-value field, not a standard combo box or listbox. Finding out the values in those programmatically is a different kettle of fish. A screenshot of your form in design view might be helpful. — David-W-Fenton, Oct 01 '10 at 23:27

HansUp · Accepted Answer · 2010-10-02T20:03:18.007

If your combo box is bound to a multi-valued field, examine its .Value property to determine whether FOO is among the selected (checked) items.

Private Sub Utility_AfterUpdate()
    Call SetVisible
End Sub

Private Sub SetVisible()
    Dim varItm As Variant
    Dim blnVisible as Boolean

    blnVisible = False
    If Not IsNull(Me.Utility.Value) Then
        For Each varItm In Me.Utility.Value
            If varItm = "FOO" Then
                blnVisible = True
                Exit For
            End If
        Next varItm
    End If
    Me.Utility_FOO.Visible = blnVisible
End Sub

You may also want to do the same thing for the form's On Current event. If so, add this:

Private Sub Form_Current()
    Call SetVisible
End Sub

Enabling field visibility from combo box selection in Access 2007

1 Answers1