Question

Locked

How do you use conditional formatting properly in MS Access VBA?

By SoulRebelPD ·
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

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

?

by brian In reply to How do you use conditiona ...

FormatConditions.Add acExpression, , "IIf([AppointmentStatusID] = 5, True, False)"
.FormatConditions(0).BackColor = vbRed


looking at the code (i'm havent coded in access for a long time) but the "IIf...etc" you have, should it read "If..etc" you have this in both lines for =5 and =3, or do you need a next statement between the two of the conditions? just throwing something out there...

Collapse -

I havent used those in awhile but, why not skip that part?

by Slayer_ In reply to How do you use conditiona ...

<pre>
Private Sub MarkDone(tb As TextBox)
'highlight background of pet name if status is finished
with tb
If [AppointmentStatusID] = 5 then
.FormatConditions(0).BackColor = vbRed
Else
.FormatConditions(0).BackColor = vbwhite 'White is probably not a good choice, but I don't know the system hex code for the system colour off the top of my head
End If

If [AppointmentStatusID] = 3 Then
.FormatConditions(1).BackColor = vbBlue
Else
.FormatConditions(1).BackColor = vbWhite
End If
End With
End Sub
</pre>

Collapse -

gave up

by SoulRebelPD In reply to How do you use conditiona ...

I gave up and just used the conditional formatting dialog. I guess as long as I don't need more than 3 conditions it should be fine.

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums