Change Backcolour if a date is a year or two years old using VBA (1 Viewer)

Refereejohn

New member
Local time
Today, 12:42
Joined
Apr 15, 2018
Messages
2
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 - I also get an End without If error?:banghead:
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 12:42
Joined
Aug 30, 2003
Messages
36,118
Code would only work properly if the form was in single record view. It would only affect the first record in continuous/datasheet views. I'd probably use Conditional Formatting. You get an error because you're mixing the one-line and block formats of If/Then/Else. Personally I'd use block format:

Code:
If [30DaysDate] - [EDEStartDate] <= 30 Then 
  [30DaysDate.BackStyle] = Orange
Else
  [30DaysDate.BackStyle] = 0
End If
 

June7

AWF VIP
Local time
Today, 11:42
Joined
Mar 9, 2014
Messages
5,423
Fix the [ ].

[30DaysDate].BackStyle

Or better, don't begin names with number. Could use Days30Date instead. Then no [ ] would be needed on any of the control references in VBA. However, would recommend using form qualifier prefix, like:

Me.[30DaysDate].BackStyle

Use Conditional Formatting.
 
Last edited:

June7

AWF VIP
Local time
Today, 11:42
Joined
Mar 9, 2014
Messages
5,423
Too fast. Already edited my post.
 

Users who are viewing this thread

Top Bottom