By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Change color of field depending on Date (was How to include OnTimer event?)

P: 55
Hi All,

I want to include some code for the OnTimer event in my Form "frmDueDateList"

But when i open the form in the design view and checked the properties menu,
i could not find the OnTimer event.

I want to change the color of the DueDate field to Red if it is less than the current Date. I thought it could be done under OnTimer event.

so i included the following code on Form_Load but there was no change in the DueDate field ForeColor for those which are less than the current Date:

Private Sub Form_Load()

If [Ins_NextDueDate] < Date Then

If [Ins_NextDueDate].ForeColor = vbRed Then
[Ins_NextDueDate].ForeColor = vbBlack
Else
[Ins_NextDueDate].ForeColor = vbRed
End If
End If
End Sub

Need your assistance.

Thanks,
Kathy
Aug 9 '07 #1
Share this Question
Share on Google+
8 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi All,

I want to include some code for the OnTimer event in my Form "frmDueDateList"

But when i open the form in the design view and checked the properties menu,
i could not find the OnTimer event.

I want to change the color of the DueDate field to Red if it is less than the current Date. I thought it could be done under OnTimer event.

so i included the following code on Form_Load but there was no change in the DueDate field ForeColor for those which are less than the current Date:

Private Sub Form_Load()

If [Ins_NextDueDate] < Date Then

If [Ins_NextDueDate].ForeColor = vbRed Then
[Ins_NextDueDate].ForeColor = vbBlack
Else
[Ins_NextDueDate].ForeColor = vbRed
End If
End If
End Sub

Need your assistance.

Thanks,
Kathy
Hi, Kathy.

Handling OnTimer event is not useful this case. Why not just to apply conditional formatting on the "field"?
Aug 9 '07 #2

P: 55
Hi, Kathy.

Handling OnTimer event is not useful this case. Why not just to apply conditional formatting on the "field"?
Hi,

Thanks for your reply

I did not use the On Timer event but i just used the following code on Form Load.
I get the Message to be displayed if the date is less then the current date but the ForeColor is still the same. For some reason, it is not happy with the code

Private Sub Form_Load()

If [Ins_NextDueDate] < Date Then

[Ins_NextDueDate].ForeColor = vbRed

Else

[Ins_NextDueDate].ForeColor = vbBlack

MsgBox "Due Date is exceeded. Please inform the customer"

End If

Need your assistance.

Thanks.
End Sub
Aug 10 '07 #3

missinglinq
Expert 2.5K+
P: 3,532
What kind of form is this? Single View, Continuous or Datasheet?

BTW, any conditional formatting placed in the FormLoad event will only be applied against the first record to load, not against all records. This type of code needs to be in the Form_Current event, and depending on the type of form, may or may not do the job you want.

Linq ;0)>
Aug 10 '07 #4

Scott Price
Expert 100+
P: 1,384
If [Ins_NextDueDate] < Date Then
In this line of code, unless Date is a variable that you have declared elsewhere, try Now() instead.

Regards,
Scott

Added... And shouldn't your message box actually be in the same section of code that changes the color to red?
Aug 10 '07 #5

missinglinq
Expert 2.5K+
P: 3,532
Scott, Date() is an Access function! It returns the current date, and is perefectly acceptable here. There's no valid reason to use Now() which returns both date and time.

Linq
Aug 10 '07 #6

FishVal
Expert 2.5K+
P: 2,653
Hi,

Thanks for your reply

I did not use the On Timer event but i just used the following code on Form Load.
I get the Message to be displayed if the date is less then the current date but the ForeColor is still the same. For some reason, it is not happy with the code

Private Sub Form_Load()

If [Ins_NextDueDate] < Date Then

[Ins_NextDueDate].ForeColor = vbRed

Else

[Ins_NextDueDate].ForeColor = vbBlack

MsgBox "Due Date is exceeded. Please inform the customer"

End If

Need your assistance.

Thanks.
End Sub
Hi, Kathy.

To apply this via code you need to handle Load, Current and AfterUpdate (on the field) event to ensure the field appearance to be relevant in any case.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     CheckDueDate
  3. End Sub
  4.  
  5. Private Sub CheckDueDate()
  6.  
  7.     With Me.Ins_NextDueDate
  8.         If .Value < Date Then
  9.             .ForeColor = vbRed
  10.             MsgBox "Due Date is exceeded. Please inform the customer"
  11.         Else
  12.             .ForeColor = vbBlack
  13.         End If
  14.     End With
  15.  
  16. End Sub
  17.  
  18. Private Sub Form_Load()
  19.     CheckDueDate
  20. End Sub
  21.  
  22. Private Sub Ins_NextDueDate_AfterUpdate()
  23.     CheckDueDate
  24. End Sub
  25.  
But, again, why do you not like to use conditional formatting?
Aug 10 '07 #7

Scott Price
Expert 100+
P: 1,384
Scott, Date() is an Access function! It returns the current date, and is perefectly acceptable here. There's no valid reason to use Now() which returns both date and time.

Linq
That's fine, Linq, but doesn't she still need to add the ()??? That was my whole point in mentioning the Now()... To call attention to that line of code...

Regards,
Scott

Just re-checked, and the () are not nec... Sorry for the bother.
SP
Aug 10 '07 #8

missinglinq
Expert 2.5K+
P: 3,532
Exactly, Scott!

FishVal, why is the code necessary in the Form_Load event? Form_Current will apply it to every record, right?

Linq ;0)>
Aug 10 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.