How can I set a field property (eg. change back color) in a continuous form when the form is loaded?

[ccorley]

I've created a multiple-item form where each record will have a [DueDate] field that will be compared against the current date and the result is entered in the [FlagNumber] field. (Example: flagnumber = 1 if current date > DueDate, flagnumber = 2 if current date is between the [WarningDate] and the [DueDate].

What I'd like to do is have the backcolor of the [Status] field change colors with respect to the Flagnumber value when the form is loaded to provide a visual indicator. I added the code below in the on-load event for the form but it only works for the first record. What do I need to do to make sure the code is performed for each record in the continuous form? Thanks in advanced for your help!

If Date > DueDate Then
FlagNumber = 1
[Status].BackColor = vbRed

ElseIf Date >= WarningDate And Date <= DueDate Then


FlagNumber = 2
[Status].BackColor = vbYellow

Else

FlagNumber = 3
[Status].BackColor = vbGreen

[pbaldy]

Look at Conditional Formatting on the ribbon.

[CJ_London]

correct terminiology is control rather than field.

use conditional formatting on your status control

Expression is----Date()>[DueDate]----set backcolor to red
Expression is----Date()>=[WarningDate] AND Date()<=[DueDate]---- set backcolor to yellow

no need for a third expression, just set the default colour to green.

Note you cannot set the flag value conditionally - if it is potentially different for each record, you need to set it in your formrecordsource - and perhaps then simplify your conditional format formula