Troubleshoot Conditional Formatting of Access 07 Report using VBA (1 Viewer)

[NeverSayDie]

Hello forum goers,

I'm looking for some troubleshooting help with an MS Access 20007 report using conditional formatting. I have more than 3 conditions, so I'm unable to use the Access front end tool for this. I've had success doing this in earlier versions of Access, but for some reason I can't seem to make it work in 07.

Here's the situation:

I'm formatting a status report using values such as: "On Target", "At Risk", "Late", "Target TBD", etc. I'm using IF statements to specify when a text box should be Green, Yellow, Red, etc. The field I'm formatting is called [lkpStatus] which appears as "Status" on the report.

I'm able to get Access to show the formatting in Print Preview mode, but in Report View it only seems to look at the very last record in the set, and then sets the textbox color for all of the records on the report based on the value of the last record in the set. I'm using the On Current event for the code in Report view.

I need it to look at each record individually at set the BackColor based on each record's value instead of solely looking at the last record. I was able to do this in earlier versions of Access without looping through the record set. Any ideas?

Here's my code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim lngRed As Long, lngYellow As Long, lngWhite As Long, lngBlack As Long, lngGreen As Long

Dim lngTan As Long, lngBlue As Long, lngGray As Long

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)
lngGreen = RGB(0, 255, 0)
lngTan = RGB(196, 189, 151)
lngBlue = RGB(16, 37, 63)
lngGray = RGB(169, 181, 165)

If lkpStatus = "On Track" Then

lkpStatus.BackColor = lngGreen
lkpStatus.ForeColor = lngBlack
​

ElseIf lkpStatus = "At Risk" Or lkpStatus = "Update Needed" Then

lkpStatus.BackColor = lngYellow
lkpStatus.ForeColor = lngBlack
​

ElseIf lkpStatus = "Late" Then

lkpStatus.BackColor = lngRed
lkpStatus.ForeColor = lngWhite
​

ElseIf lkpStatus = "Target TBD" Then

lkpStatus.BackColor = lngTan
lkpStatus.ForeColor = lngBlack
​

ElseIf lkpStatus = "Closed" Or lkpStatus = "Cancelled" Then

lkpStatus.BackColor = lngBlue
lkpStatus.ForeColor = lngWhite
​

ElseIf lkpStatus = "Monitor" Then

lkpStatus.BackColor = lngGray
lkpStatus.ForeColor = lngBlack
​

End If

End Sub

 

[pbaldy]

I think your problem is that the format events do not fire in Report view, only Preview and Print. Can you stick with one of those?