Field Color not changing using Conditional Format or On Not In Lis

[RAN]

I've got a single form with a combo box field ([Combo_NSFCertified]) whose
control source is a field from a table. When the form is opened I want the
field background to be colored red if the value of the field is not one of
the acceptable values from the combo list. I've created a hidden text field
([NSF_Certified_Unmatched]) from an unmatched query, which compares the value
to the accepable list. I've tried using conditional formatting with
Expression Is [Combo_NSFCertified]=[NSF_Certified_Unmatched], but this isn't
working.

I've also tried using the On Not in List with the following code:
Private Sub Combo_NSFCertified_NotInList(NewData As String, Response As
Integer)
Me!Combo_NSFCertified.BackColor = vbRed
End Sub
This doesn't work either. Please help. Thanks.

 

[Marshall Barton]

I've got a single form with a combo box field ([Combo_NSFCertified]) whose
control source is a field from a table. When the form is opened I want the
field background to be colored red if the value of the field is not one of
the acceptable values from the combo list. I've created a hidden text field
([NSF_Certified_Unmatched]) from an unmatched query, which compares the value
to the accepable list. I've tried using conditional formatting with
Expression Is [Combo_NSFCertified]=[NSF_Certified_Unmatched], but this isn't
working.

I've also tried using the On Not in List with the following code:
Private Sub Combo_NSFCertified_NotInList(NewData As String, Response As
Integer)
Me!Combo_NSFCertified.BackColor = vbRed
End Sub

Why not just set the combo box's LimitToList property so an
invalid entry can not be entered?

 

[RAN]

That would work if this was the initial entry form. However the initial info
is being extracted from an Excel worksheet. My form in question is being used
to review and approve the submitted data. Any additional ideas would be
greatly appreciated. Thanks.

I've got a single form with a combo box field ([Combo_NSFCertified]) whose
control source is a field from a table. When the form is opened I want the
field background to be colored red if the value of the field is not one of
the acceptable values from the combo list. I've created a hidden text field
([NSF_Certified_Unmatched]) from an unmatched query, which compares the value
to the accepable list. I've tried using conditional formatting with
Expression Is [Combo_NSFCertified]=[NSF_Certified_Unmatched], but this isn't
working.

I've also tried using the On Not in List with the following code:
Private Sub Combo_NSFCertified_NotInList(NewData As String, Response As
Integer)
Me!Combo_NSFCertified.BackColor = vbRed
End Sub

Why not just set the combo box's LimitToList property so an
invalid entry can not be entered?

 

[Marshall Barton]

That would work if this was the initial entry form. However the initial info
is being extracted from an Excel worksheet. My form in question is being used
to review and approve the submitted data.

Here's a shot in the dark. Use the form's Current event:

With Me!Combo_NSFCertified
If .ListIndex = -1 Then
.BackColor = vbRed
Else
.BackColor = vbWhite
End If
End With

 

[RAN]

It works! Thanks a lot.

Here's a shot in the dark. Use the form's Current event:

With Me!Combo_NSFCertified
If .ListIndex = -1 Then
.BackColor = vbRed
Else
.BackColor = vbWhite
End If
End With