How to conditionally format controls in Access datasheet WITHOUT affecting row background colors?

Question

I'm using Access 2010. I have a control named [Event Count] in a summary datasheet form. If the value of [Event Count] is greater than one, I'll use an onClick event to link to a pop-up form containing the individual event details. By default, the control is formatted to appear as a hyperlink. When [Event Count] equals one, I want to override the default appearance to remove the underline and set the ForeColor to black. I can't figure out how to do this without also overriding the default (alternating) row BackColor in the [Event Count] column.

Here's what the datasheet currently looks like:

Notice that the background for the cells in the "Events" column is always white when the [Event Count] value = 1, regardless of the background color of the adjacent cells in the same row. I want the background color to follow the alternating color schema of the datasheet.

I've tried Conditional Formatting, but it overrides the [Event Count] BackColor:

I then deleted the conditional formatting and tried 'touching' every row and testing for a FormatCondition:

    Private Sub Form_Load()
       Dim objFrc As FormatCondition
       Me![Event_Count].FormatConditions.Delete
       Set objFrc = Me![Event_Count].FormatConditions.Add(acExpression, , "[Event_Count]=1")
       With Me![Event_Count].FormatConditions(0)
         .ForeColor = vbBlack
         .FontUnderline = False
       End With
    End Sub

This, too, sets the background color to white in all cases.

How can I conditionally affect only the text color and underline attributes of certain [Event Count] elements without affecting the background color on the datasheet? Can/should I set up a compound test that also evaluates {rowNumber} MOD 2, or is there (hopefully) an easier way?

Answer 1

I know this is an old thread, but I recently did something similar and worked out a fairly good solution for 'preserving the alternating background color in a datasheet issue', similar to the approach you mentioned using 'MOD', so thought I might share this.

In your conditional formatting, you need 2 rules for the 'Event Count' control.

For the 1st one, set the font to black, disable underline and set the background to white and add this expression:

[Event_Count] = 1 And isAlternateRow([FieldID])>1

For the 2nd one, set the font to black, disable underline and set the background to light grey (the same shade of grey as the alternating background color) and add this expression:

[Event_Count] = 1 And isAlternateRow([FieldID])=1

(replace FieldID with the name of your unique ID field)

Then, in a VBA code module, add this function:

Public Function isAlternateRow(id As Long) As Integer

    On Error GoTo rah

    Dim rs As Recordset
    Set rs = Form_FormName.Form.RecordsetClone 'Change FormName to the name of the datasheet form
    rs.FindFirst "[FieldID] = " & id 'Change FieldID to the name of your unique ID field

    If (rs.AbsolutePosition Mod 2 = 0) Then
        isAlternateRow = 2
        Set rs = Nothing
        Exit Function
    Else
        isAlternateRow = 1
        Set rs = Nothing
        Exit Function
    End If

    rah:
    isAlternateRow = 3
    Set rs = Nothing

End Function

I found this to work reasonably well, the only drawback being if your datasheet has a lot of records, the colours don't update immediately, and may take half a second to change to the 'black' that you are after, as you scroll through the records.

I also found that without the error handler in the function, the user will get multiple errors, so leave it in there.