Change text box backcolor conditionally

[accessible]

Hello All
This should be easy, but as I'm not very experienced with VBA, it's not.

I have an Access 2010 database to hold employee data and I have a report to flag when a performance review is coming up, overdue or done.

The textbox (Date/Time type) that holds the review date is called [ReviewDate]. I use conditional formatting to turn the backcolor yellow if the date is less than 30 days from now, and that works.

If the review date is before today, I want the backcolor on the report to be red. If the value of the checkbox [ReviewDone] is yes, indicating the review has been completed, I want the backcolor to be white.

I'm not using conditional formatting for the red backcolor because it seems to take priority and ignore any VBA code about turning white if the box is checked.

So I wrote what should be simple code and put it in the report's Current event property, but absolutely nothing happens.

Private Sub Report_Current()
If Me.ReviewDate < Now() Then
Me.ReviewDate.BackColor = vbRed

ElseIf Me.ReviewDone = -1 Then
Me.ReviewDate.BackColor = vbWhite

End If
End Sub

I have similar code on the employee data input form and it works as it should, but the report doesn't work at all. Can anyone tell me where I'm off track?

Thanks in advance!

 

[vbaInet]

So why a mixture of VBA code and Conditional Formatting? Are the three conditions not sufficient for your needs?

 

[accessible]

Thanks for your reply. I agree, using conditional formatting for everything would be a lot easier, and it works fine for changing the backcolor to either yellow (for upcoming) or red (for overdue) according to the value in the [ReviewDate] field.

However, in conditional formatting, how do I make one control [ReviewDate] change depending upon the value in a) a different control [ReviewDone] and b) a checkbox, which doesn't allow conditional formatting?

Probably something simple, but that's where I got stuck and resorted to VBA (to no avail).

 

[accessible]

I see that I could have another criterion in conditional formatting that uses "Expression is" rather than "Field value is." I've put in = IIf([ReviewDone].[Value]=-1,"white","red") but still nothing's happening.

Expressions aren't my strong point. Any idea why this doesn't work?

 

[boblarson]

 

[accessible]

Brilliant! I just made a couple of tweaks: I changed the < to > in the third item and used Value Is Between Date() and Date()+30 for the second item. And it works!!!

Now I just wish I could have a fourth criterion to change color again when the evaluation is due within the next week... Ah well.

Thank you SO much for your help,Bob.

 

[vbaInet]

Access 2010? It looks like you can have more than three conditions there. You can add more conditions in code too.

 

[boblarson]

Yeah, in Access 2010 you can do 50 conditions.

 

[vbaInet]

Yeah, in Access 2010 you can do 50 conditions.

Very nice