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

[ontherun]

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

[FishVal]

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"?

[ontherun]

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

[missinglinq]

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)>

[Scott Price]

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?

[missinglinq]

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

[FishVal]

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.

Private Sub Form_Current()
    CheckDueDate
End Sub
 
Private Sub CheckDueDate()
 
    With Me.Ins_NextDueDate
        If .Value < Date Then
            .ForeColor = vbRed
            MsgBox "Due Date is exceeded. Please inform the customer"
        Else
            .ForeColor = vbBlack
        End If
    End With
 
End Sub
 
Private Sub Form_Load()
    CheckDueDate
End Sub
 
Private Sub Ins_NextDueDate_AfterUpdate()
    CheckDueDate
End Sub
 

But, again, why do you not like to use conditional formatting?

[Scott Price]

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

[missinglinq]

Exactly, Scott!

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

Linq ;0)>