How do I change the background of a field if the date is before today?

[David_Ssc]

I want to highlight a field background if the contained date is prior to today. I have identified .BackgroundColor as the field property that I need to change. I would appreciate assistance on:

1. The syntax of how to compare the field value to Now()
2. What form Event I would tie the macro to (so that it the background is appropriate for each new record during navigation).

Thanks, David

[David_Ssc]

Here’s the macro:

Sub FupColor(oEvent as object)
	Me = oEvent.Source
 	oField = Me.getbyName("Fup Pending")
 	If CDateFromUnoDate(oField.BoundField.date) <= Now() then
 		oField.BackGroundColor = 16764159
 	  Else
 	  	oField.BackGrounColor = 1450253
 	  end if
End sub

[Ratslinger]

You never state, but this code is based using a Date control.

Your question states BEFORE today but your code says less than or EQUAL to today! Note minor errors in code also:

Sub FupColor(oEvent as object)
    Me = oEvent.Source
    oField = Me.getbyName("Fup Pending")

 'CHANGE IS HERE'
    If CDateFromUnoDate(oField.CurrentValue) <= Now() then

        oField.BackGroundColor = 16764159
      Else
  'YOUR CODE IS WRONG HERE - SPELLING MISSING "d" - & BLACK COLOR?'
        oField.BackGroundColor = 14502530
      end if
End sub

Event - on internal form event After record change.

[David_Ssc]

This answer got me most of the way there - the correct Event was very helpful. A slight variation in Ratslinger’s answer for when the field is empty:

Sub FupColor(oEvent as object)
	Me = oEvent.Source
 	oField = Me.getbyName("Fup Pending")
 	oDate=oField.CurrentValue

 	oField.BackGroundColor = 14540253
 	If NOT IsEmpty(oDate) then
 		If CDateFromUnoDate(oDate) <= Now() then oField.BackGroundColor = 16764159
 	  end if
End sub

[Ratslinger]

@David_Ssc You’re absolutely correct especially for new records.

[librebel]

in addition to @Ratslinger’s answer above,
if you want to update the backgroundcolor immediately when the date value is being edited, you could connect the following macro to the Text_modified() event of your datefield control:

Sub DateField_TextChanged( oEvent )
	If CDateFromUnoDate( oEvent.Source.Date ) <= Now() Then
		oEvent.Source.Model.BackGroundColor = 16764159
	Else
		oEvent.Source.Model.BackGroundColor = 1450253	REM ... you sure about this color?
	End If
End Sub

[Ratslinger]

@librebel +1 for added functionality.