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!

Answer 1

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