Go Back   Access World Forums > Apps and Windows > Visual Basic

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-13-2018, 09:41 AM   #1
Refereejohn
Newly Registered User
 
Join Date: Apr 2018
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Refereejohn is on a distinguished road
Change Backcolour if a date is a year or two years old using VBA

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?

Refereejohn is offline   Reply With Quote
Old 08-13-2018, 09:50 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,938
Thanks: 9
Thanked 3,845 Times in 3,788 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Change Backcolour if a date is a year or two years old using VBA

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
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 08-13-2018, 10:25 AM   #3
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 728
Thanks: 0
Thanked 169 Times in 169 Posts
June7 will become famous soon enough
Re: Change Backcolour if a date is a year or two years old using VBA

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.

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 08-13-2018 at 10:43 AM.
June7 is offline   Reply With Quote
Old 08-13-2018, 10:27 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,938
Thanks: 9
Thanked 3,845 Times in 3,788 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Change Backcolour if a date is a year or two years old using VBA

Quote:
Originally Posted by June7 View Post
Eliminate the [ ].
That will fix the code?
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 08-13-2018, 10:36 AM   #5
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 728
Thanks: 0
Thanked 169 Times in 169 Posts
June7 will become famous soon enough
Re: Change Backcolour if a date is a year or two years old using VBA

Too fast. Already edited my post.

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Date Criteria to filter Current Year and next 2 years...???? :( farhanleos Queries 8 10-21-2017 09:57 PM
change the backcolour on report based on value of text box murray83 Reports 14 08-03-2017 10:00 AM
Year to date for last three years unclefink Queries 6 01-07-2013 03:50 PM
How to change backcolour of Control reddevil1 Forms 5 11-30-2012 03:01 AM
[SOLVED] Change BackColour & Msg Display JennyM Forms 10 02-25-2002 08:31 AM




All times are GMT -8. The time now is 05:29 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World