Properties of subform controls: Color text in datasheet view?

[PamelaD]

I am working with a main form (frmCases) on which I have placed a tab control with a subform (fsubCaseProgress) thereon. The subform displays in datasheet view. One of the fields in the datasheet view is a date field [TargetDate].

What I want to do is have the TargetDate field in this subform datasheet display in red if it is fewer than 14 days from Now(), and black if it is more than 14 days from Now().

I was able to make this work with a date field that is part of the main form as follows:

Private Sub Form_Current()

If Me.NextDueDate.Value >= (Now() + 14) Then
Me.NextDueDate.ForeColor = 0

ElseIf Me.NextDueDate.Value <= (Now() + 14) Then
Me.NextDueDate.ForeColor = 255

End If
End Sub

I can’t seem to make this work in the subform. I have searched the forum to see if this has already been addressed, but didn’t find (or didn’t recognize?) the answer. I would very much appreciate any help that someone might offer—I’m only a techie wannabe and not the real thing …… Thanks in advance.

 

[BillPower]

While in your Form's design view, select the control NextDueDate, from the Menu select Format-> Conditional Formatting, From the drop down list (Condition 1) select Expression is, in the box immediately to the right, paste the following:

DateDiff(&quot;d&quot;,Now(),[NextDueDate])>=14

At the bottom you should see a Button with an A on it, select Red. Click OK

 

[BillPower]

Try again:

DateDiff(&quot;d&quot;,Now(),[NextDueDate])<=14

 

[PamelaD]

Thanks very much for your speedy reply, Bill. Upon attempting your suggestion, I realized I left out an important detail in my initial query: I'm using Access 97. My menu bar doesn't offer the option of conditional formatting. Any chance you have a &quot;Plan B&quot; up your sleeve?

 

[BillPower]

I don't know of any way to do what you want in Access 97, but there is a way to manipulate the Back Colour, if you can live with the Back Colour changing instead of the Fore Colour and viewing your Sub Form in Form View rather than Datasheet View, take a look at a previous posting thread702-458318.

If you're interested, let me know and I can put together an example tonight. I would need to know the name of the Tab Control, and the name of the Table/Query or SQL the Sub Form is Bound to. Also is TargetDate both the Field Name and the Control Name.

 

[PamelaD]

Bill,
I do need the subform in a datasheet format, so I will just live with it for the time being. The person for whom I am making this may be upgrading to Access 2002 in the near future, so I can update it with your suggestion when she does. Thanks again for your responses. It is much appreciated!