Conditional Formatting Problem

[Tom]

Hi:

Using Access2002, I am trying to alternate row colors to improve
visibility AND have certain fields override the alternate row color if
some condition is true (e.g., if DueDate is past due, then the
backcolor should be red, regardless of the alternating row color
scheme).

What I have so far is a Continuous form based on a query.
Additionally, the form has a textbox with the following properties:
.name = txtLineNum
.controlsource = =GetLineNumber([Form],"contractid",[contractid]) Mod
2

GetLineNumber is defined below – but it essentially returns a
sequential row number for the form (I cribbed this from somewhere, but
I’ve lost track of where).

In the OnLoad event of the form, I set my conditional format as
follows:
lngGray = RGB(225, 225, 225)
Me.ContractNumber.FormatConditions.Delete
Me.ContractNumber.FormatConditions.Add acExpression, acEqual, "right
(contractnumber,1)=1"
Me.ContractNumber.FormatConditions(0). BackColor = vbYellow

Me.ContractNumber.FormatConditions.Add acExpression, acEqual,
"txtLineNum=1"
Me.ContractNumber.FormatConditions(1).BackColor = lngGray

(normally, each field would get FormatCondition(1), but for the sake
of brevity, I’m only showing 1 here). The goal being if the
ContractNumber ends in 1, set the backcolor to Yellow, otherwise
alternate Gray/White backcolor.

The result is that ContractNumber backcolor is uniformly Gray, and
changes to white if you click on it…narry a yellow back color in
sight.

If I delete one or the other formatconditions things turn out as you
would expect – either alternating gray/white, or yellow as
appropriate.

So why won’t the two conditional formats play nice together? Or more
importantly, how can I get them to play nice together?

As a lark, I tried changing the continuous form to a datasheet view
and it worked correctly – yellows, grays and whites all coexisting.

Why does this work on the datasheet, but not the continuous form?

Thanks

Tom











'*************************************************************

Function GetLineNumber(F As Form, KeyName As String, KeyValue)
Dim RS As DAO.Recordset
Dim CountLines

On Error GoTo Err_GetLineNumber

Set RS = F.RecordsetClone

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value.
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value.
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value.
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Loop backward, counting the lines.
Do Until RS.BOF
CountLines = CountLines + 1
RS.MovePrevious
Loop

Bye_GetLineNumber:
' Return the result.
GetLineNumber = CountLines

Exit Function

Err_GetLineNumber:
CountLines = 0
Resume Bye_GetLineNumber

End Function