On a continuous form, I’m trying to change the background color of a particular field depending on the value of one of the adjacent fields. If this adjacent field is equal to 5 I’m wanting to color the field red, if it’s 3 I want the background to be blue.
I have several records on my form that satisfy both of these conditions, but the 1st condition is the only one that ever receives special formatting, meaning the records whose field should be shaded blue are not.
I’ve included my code below. I’ve done extensive reasearch about the formatconditions objects and it’s add method and it seems like I’m doing things right. My only guess is that my use of an IIF is somehow causing a conflict.
Private Sub MarkDone(tb As TextBox)
‘highlight background of pet name if status is finished
With tb
If .FormatConditions.Count > 0 Then
.FormatConditions(0).Delete
.FormatConditions(1).Delete
End If
.FormatConditions.Add acExpression, , “IIf([AppointmentStatusID] = 5, True, False)”
.FormatConditions(0).BackColor = vbRed
.FormatConditions.Add acExpression, , “IIf([AppointmentStatusID] = 3, True, False)”
.FormatConditions(1).BackColor = vbBlue
End With
End Sub
