Change backcolour when a date becomes ripe.

[Refereejohn]

I have around ten dates in an Access form I want to change the colour orange or amber (to indicate a warning in my [30DaysDate] thirty days to go) and when the anniversary date [EDEDateStart] arrives ten will turn red, these are mandatory learning dates for e-learning. I have tried the following code for the first experiment: -
Private Sub Form_Load()
Dim Orange As Long
Orange = RGB(255, 165, 0)
If [30DaysDate] - [EDEStartDate] <= 30 Then [30DaysDate.BackStyle] = Orange


Else: [30DaysDate.BackStyle] = 0
End If
End Sub

Being a novice I am uncertain if I can use the If Function for this. Even if I use an Expression in Conditional Formatting I would need the date the e-learning started to remain a constant (Date() would always be the current date). I would have to repeat the code with name changes I know but if I could find a code that would do this I’d be grateful. The amber or orange Backcolour would trigger thirty days before the anniversary date with the EDEStartDate turning red when the person went out of compliance.

[Gicu]

You will need to use the Current event of the form instead of the load one and the backcolor property (set it for 42495 for orange- see http://www.endprod.com/colors/invcol.htm for Access numeric codes).

Cheers,
Vlad

[pbaldy]

Code is only practical if the form is in single view. Otherwise, only the selected (or first) record is affected. If the form is in continuous or datasheet view, you'd want to use Conditional Formatting.

[Missinglinq]

Actually, every Record in Continuous or Datasheet view would be affected...but they'd all be affected/formatted based on the condition of the current Record.

Linq ;0)>

[pbaldy]

Good point, I didn't say that very well.