NeverSayDie
New member
- Local time
- Today, 07:22
- Joined
- Apr 22, 2011
- Messages
- 1
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
End Sub
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" ThenlkpStatus.ForeColor = lngBlack
lkpStatus.BackColor = lngYellow
lkpStatus.ForeColor = lngBlack
ElseIf lkpStatus = "Late" ThenlkpStatus.ForeColor = lngBlack
lkpStatus.BackColor = lngRed
lkpStatus.ForeColor = lngWhite
ElseIf lkpStatus = "Target TBD" ThenlkpStatus.ForeColor = lngWhite
lkpStatus.BackColor = lngTan
lkpStatus.ForeColor = lngBlack
ElseIf lkpStatus = "Closed" Or lkpStatus = "Cancelled" ThenlkpStatus.ForeColor = lngBlack
lkpStatus.BackColor = lngBlue
lkpStatus.ForeColor = lngWhite
ElseIf lkpStatus = "Monitor" ThenlkpStatus.ForeColor = lngWhite
lkpStatus.BackColor = lngGray
lkpStatus.ForeColor = lngBlack
End IflkpStatus.ForeColor = lngBlack
End Sub