Changing a Control's Color and Display a Message Based on the Value

[pompeyjon]

Hi everyone,

Can someone tell me how to the following with VBA (I know how to do it in SQL!!!).

Private Sub Form_Current()
        
    '  If value in GPLookup text box is not a Sandwell PCT
    '  display value in GPLookup in red and display FinLiability label.
    
    If [Forms]![GeneralPractitioners subform]![PCTDescription] NOT IN ('OS','RRT','WWB') Then      *******
       [Forms]![GeneralPractitioners subform]!PCTDescription.ForeColor = 255
    Me!FinLiability.Visible = True

    
    ' Otherwise, display value in GPLookup text box in black
    '  and hide FinLiability label.
    
    Else
    [Forms]![GeneralPractitioners subform]!PCTDescription.ForeColor = 0
    Me!FinLiability.Visible = False
    End If

End Sub

Basically, what I need help with is the formulation of the code to check whether the value of the GPLookup control is in the given list as defined above, but I'm not sure how to do this in VBA.

Can anyone help please...!

Cheers,

Jon

 

[mp9]

In my view the easiest way is this:

Private Sub Form_Current()
    
    Select Case [Forms]![GeneralPractitioners subform]![PCTDescription]
    Case "OS","RRT","WWB"
        [Forms]![GeneralPractitioners subform]!PCTDescription.ForeColor = 0
        Me!FinLiability.Visible = False
    Case Else
        [Forms]![GeneralPractitioners subform]!PCTDescription.ForeColor = 255
        Me!FinLiability.Visible = True
    End Select
End Sub

Hope this helps!

http://www22.brinkster.com/accessory/