overdue date color

[jubiiab via AccessMonster.com]

Hi

When the date is overdue I want the date color to be changed to red on the
datasheet form but the below code is not working. Can anyone help me please?


Private Sub Form_Open(Cancel As Integer)
If [ServiceDate] < Now() Then
MsgBox "service date overdue!", vbInformation, "Service date"

If [ServiceDate] < Now() Then
[ServiceDate].ForeColor = vbRed

End If

End If
End Sub

 

[BruceM]

The form's Open event is too soon to run the code, as the recordset has not
yet been loaded. In any case, you will want to check at each record, not
just once.

Take a look at conditional formatting, which should be able to accomplish
what you need. If you use code such as you have shown, try the form's
Current event. You will need a way to change it back to black; otherwise it
will turn red and stay that way at the first late record and stay that way.

Private Sub Form_Current()

If [ServiceDate] < Now() Then
MsgBox "service date overdue!", vbInformation, "Service date"
Me.[txtServiceDate].ForeColor = vbRed
Else
Me.[txtServiceDate].ForeColor = vbBlack
End If

End Sub

Note that you only ned to check ServiceDate once. Also, the formatting is
applied to the text box, not the field. It could be that the field and the
text box have the same name, which should be OK in this case, but I prefer
to give them different names, because sometimes Access can become confused
if they have the same name.

Are you sure you don't want > rather than <?

 

[jubiiab via AccessMonster.com]

I did like this but it didnt work

Private Sub Form_Current()
If [ServiceDate] < Now() Then
Me.[ServiceDate].ForeColor = vbRed
Else
Me.[ServiceDate].ForeColor = vbBlack
End If

End Sub

I also tried to change the field name but it didnt work

Private Sub Form_Current()
If [ServiceDate] < Now() Then
Me.[txtServiceDate].ForeColor = vbRed
Else
Me.[txtServiceDate].ForeColor = vbBlack
End If

End Sub

And i thin this sign < is correct because only when the Now() date is greater
then it should turn red.

The form's Open event is too soon to run the code, as the recordset has not
yet been loaded. In any case, you will want to check at each record, not
just once.

Take a look at conditional formatting, which should be able to accomplish
what you need. If you use code such as you have shown, try the form's
Current event. You will need a way to change it back to black; otherwise it
will turn red and stay that way at the first late record and stay that way.

Private Sub Form_Current()

If [ServiceDate] < Now() Then
MsgBox "service date overdue!", vbInformation, "Service date"
Me.[txtServiceDate].ForeColor = vbRed
Else
Me.[txtServiceDate].ForeColor = vbBlack
End If

End Sub

Note that you only ned to check ServiceDate once. Also, the formatting is
applied to the text box, not the field. It could be that the field and the
text box have the same name, which should be OK in this case, but I prefer
to give them different names, because sometimes Access can become confused
if they have the same name.

Are you sure you don't want > rather than <?

Hi

[quoted text clipped - 13 lines]

End If
End Sub

 

[jubiiab via AccessMonster.com]

...I forgot to tell that the sub form opens as datasheet form. I dont no that
is why its not working??

I did like this but it didnt work

Private Sub Form_Current()
If [ServiceDate] < Now() Then
Me.[ServiceDate].ForeColor = vbRed
Else
Me.[ServiceDate].ForeColor = vbBlack
End If

End Sub

I also tried to change the field name but it didnt work

Private Sub Form_Current()
If [ServiceDate] < Now() Then
Me.[txtServiceDate].ForeColor = vbRed
Else
Me.[txtServiceDate].ForeColor = vbBlack
End If

End Sub

And i thin this sign < is correct because only when the Now() date is greater
then it should turn red.

The form's Open event is too soon to run the code, as the recordset has not
yet been loaded. In any case, you will want to check at each record, not

[quoted text clipped - 29 lines]

 

[jubiiab via AccessMonster.com]

...I forgot to tell that the sub form opens as a datasheet form. I dont no
that is why its not working??

I did like this but it didnt work

Private Sub Form_Current()
If [ServiceDate] < Now() Then
Me.[ServiceDate].ForeColor = vbRed
Else
Me.[ServiceDate].ForeColor = vbBlack
End If

End Sub

I also tried to change the field name but it didnt work

Private Sub Form_Current()
If [ServiceDate] < Now() Then
Me.[txtServiceDate].ForeColor = vbRed
Else
Me.[txtServiceDate].ForeColor = vbBlack
End If

End Sub

And i thin this sign < is correct because only when the Now() date is greater
then it should turn red.

The form's Open event is too soon to run the code, as the recordset has not
yet been loaded. In any case, you will want to check at each record, not

[quoted text clipped - 29 lines]

 

[Linq Adams via AccessMonster.com]

You cannot do this with code on a Datasheet View form (nor on a Continuous
View form) you have to use Conditional Formatting off of the Format menu
dropdown.

 

[Linq Adams via AccessMonster.com]

In the Conditional Formatting select

Expression Is

and then enter

[txtServiceDate]>Date()

 

[jubiiab via AccessMonster.com]

can I then change color on the datasheet cell...the background on the
servicedate cell?

And I can not find this: Conditional Formatting off of the Format menu.

I opend properties --> format--> what next?

 

[jubiiab via AccessMonster.com]

Okay I found it out and its working now. Thank you very much for your time.