Issue With Conditional Formatting (1 Viewer)

[IainMc2013]

I have a table (Access 2000) that I am displaying in a form in datasheet format. I have set conditional formatting colours from the menu so that most fields are coloured depending on the status of the list item (4 different formats including the default) and this is working fine.

However I want to add some additional conditional formatting to only three fields in order to flag up some of the items that are due soon and also to denote the importance. I have written VBA code to do this but instead of applying the conditional formatting to individual rows it seems to be applying the first condition to the entire list. Here is my code:

Option Compare Database

Private Sub Form_Load()
Call ColourConfiguration
End Sub

Function ColourConfiguration()

' Action, Due_Date, Importance to be color ranked based on the priority score
If CDate(Me.Due_Date.Value) < DateAdd("d", 1, Format(Now, "dd MMMM yyyy")) Then ' action is due today or overdue
Me.Action.FormatConditions(0).BackColor = vbRed
Me.Action.FormatConditions(0).ForeColor = vbYellow
Me.Action.FormatConditions(1).BackColor = vbRed
Me.Action.FormatConditions(1).ForeColor = vbYellow

Me.Due_Date.FormatConditions(0).BackColor = vbRed
Me.Due_Date.FormatConditions(0).ForeColor = vbYellow
Me.Due_Date.FormatConditions(1).BackColor = vbRed
Me.Due_Date.FormatConditions(1).ForeColor = vbYellow

Else

End If


If CDate(Me.Due_Date.Value) < DateAdd("d", 28, Format(Now, "dd MMMM yyyy")) Then ' action is due within 28 days

Me.Due_Date.FormatConditions(0).BackColor = vbRed
Me.Due_Date.FormatConditions(0).ForeColor = vbWhite
Me.Due_Date.FormatConditions(1).BackColor = vbRed
Me.Due_Date.FormatConditions(1).ForeColor = vbWhite

If Me.Importance = "High" Then
Me.Action.FormatConditions(0).BackColor = vbRed
Me.Action.FormatConditions(1).BackColor = vbRed
Me.Action.FormatConditions(0).ForeColor = vbWhite
Me.Action.FormatConditions(1).ForeColor = vbWhite
Else
Me.Action.FormatConditions(0).BackColor = vbOrange
Me.Action.FormatConditions(1).BackColor = vbOrange
End If

Else
End If

If (CDate(Me.Due_Date.Value) > DateAdd("d", 28, Format(Now, "dd MMMM yyyy"))) And (CDate(Me.Due_Date.Value) < DateAdd("d", 60, Format(Now, "dd MMMM yyyy"))) Then ' action due within 60 days
Me.Due_Date.FormatConditions(0).BackColor = vbYellow
Me.Due_Date.FormatConditions(1).BackColor = vbYellow

If Me.Importance = "High" Then
Me.Action.FormatConditions(0).BackColor = vbYellow
Me.Action.FormatConditions(1).BackColor = vbYellow
Else
Me.Action.FormatConditions(0).BackColor = vbGreen
Me.Action.FormatConditions(1).BackColor = vbGreen
End If
Else
End If

End Function

I know some of the VB color values are wrong (VBOrange came up black) but I'll deal with that once I get this sorted. All of the cells for "Action" and "Due_Date" are showing up red, regardless of the date value (the first value in the table is a week from now). However if I reorder the list so that a date > 4 weeks from now tops the list all the records change to yellow. So clearly the code is taking the first value and applying to the rest of the list.

Any ideas what I need to do? I'm sure its something really obvious but late Friday afternoon is probably not the best time to get my head around it! lol

I'm using long date format throughout the DB because it seemed to be the only way around UK and US date format conflicts in queries.

 

[JHB]

You have to use the Add or the Modify metode when you are doing it from VBA code.
http://msdn.microsoft.com/en-us/library/office/ff845092(v=office.14).aspx

 

[missinglinq]

This is normal behavior, on Datasheet View (as well as Continuous View) Forms when trying to do this kind of thing through code. In actuality, there is only one Control named Due_Date; what you see, when you look at a Datasheet View Form, are multiple instances of this single Control.

This type of Formatting, with this type of Form, can only be done through Conditional Formatting off of the Menu.

And coding using the FormatConditions Collection, as suggested, is still governed by the three conditions plus a default rule, in pre-2010 Access.

Sorry!

Linq ;0)>