Conditional Formatting

[PeterM]

I have a continuous form which contains a date field called Call_Back_Date.
I need to be able to conditionally set the forecolor of that field for each
record depending on it's value.

Below is the code I'm using with no success. I suspect it's the
acExpression value of "[call_back_date] = date", I'm comparing the
call_back_date value to today's date. I've tried formatting the date, using
now() and none of the conditions are being triggered...can anyone please tell
me why? What am I doing wrong?

Public objFrc As FormatCondition

Private sub Set_Conditions()
Me.Call_Back_Date.FormatConditions.Delete
Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,
"[call_back_date] = date")
Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,
"[call_back_date] < date")
Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,
"[call_back_date] > date")
'set the default value
With Me.Call_Back_Date
.ForeColor = vbVlue
End With
With Me.Call_Back_Date.FormatConditions(0)
.ForeColor = vbRed
End With
With Me.Call_Back_Date.FormatConditions(1)
.ForeColor = vbBlack
End With
'req call/onsite mtg
With Me.Call_Back_Date.FormatConditions(2)
.ForeColor = vbGreen
End With
End Sub

 

[JimB]

Have you tried using an explicit reference?

Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,

"[call_back_date] = #" & date & "#")
Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,
"[call_back_date] < #" & date & "#")
Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,
"[call_back_date] > #" & date & "#")

I have a continuous form which contains a date field called Call_Back_Date.
I need to be able to conditionally set the forecolor of that field for each
record depending on it's value.

Below is the code I'm using with no success. I suspect it's the
acExpression value of "[call_back_date] = date", I'm comparing the
call_back_date value to today's date. I've tried formatting the date, using
now() and none of the conditions are being triggered...can anyone please tell
me why? What am I doing wrong?

Public objFrc As FormatCondition

Private sub Set_Conditions()
Me.Call_Back_Date.FormatConditions.Delete
Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,
"[call_back_date] = date")
Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,
"[call_back_date] < date")
Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,
"[call_back_date] > date")
'set the default value
With Me.Call_Back_Date
.ForeColor = vbVlue
End With
With Me.Call_Back_Date.FormatConditions(0)
.ForeColor = vbRed
End With
With Me.Call_Back_Date.FormatConditions(1)
.ForeColor = vbBlack
End With
'req call/onsite mtg
With Me.Call_Back_Date.FormatConditions(2)
.ForeColor = vbGreen
End With
End Sub

 

[Wayne Morgan]

The problem is that on a continuous form, there is really only one control.
The code will run for the current record and then change the color for all
of the records since you can't refer to one copy of the control at a time.
The way to work around this is to use the built-in Conditional Formatting
(Access 2000 or newer). It will allow for a default plus three conditions.

Open the form in Design View. Right click the control in question and choose
"Conditional Formatting...". Set as desired.

 

[PeterM]

thanks...worked perfectly!

Have you tried using an explicit reference?

Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,

"[call_back_date] = #" & date & "#")
Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,
"[call_back_date] < #" & date & "#")
Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,
"[call_back_date] > #" & date & "#")

I have a continuous form which contains a date field called Call_Back_Date.
I need to be able to conditionally set the forecolor of that field for each
record depending on it's value.

Below is the code I'm using with no success. I suspect it's the
acExpression value of "[call_back_date] = date", I'm comparing the
call_back_date value to today's date. I've tried formatting the date, using
now() and none of the conditions are being triggered...can anyone please tell
me why? What am I doing wrong?

Public objFrc As FormatCondition

Private sub Set_Conditions()
Me.Call_Back_Date.FormatConditions.Delete
Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,
"[call_back_date] = date")
Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,
"[call_back_date] < date")
Set objFrc = Me.Call_Back_Date.FormatConditions.Add(acExpression, ,
"[call_back_date] > date")
'set the default value
With Me.Call_Back_Date
.ForeColor = vbVlue
End With
With Me.Call_Back_Date.FormatConditions(0)
.ForeColor = vbRed
End With
With Me.Call_Back_Date.FormatConditions(1)
.ForeColor = vbBlack
End With
'req call/onsite mtg
With Me.Call_Back_Date.FormatConditions(2)
.ForeColor = vbGreen
End With
End Sub