Question Locked

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

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

4 replies

by brian

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...

by slayer_

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    

by soulrebelpd

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.

This discussion is closed to new replies.