Solved Conditional formatting without conditional formatting

[John Sh]

In the form below I want to colour each row depending on the content of the "Notes" field.
If I use conditional formatting in multiple mode, after a few rules the formatting of the notes field disappears.
I have tagged the controls to respond to code but the entire column is coloured.
Clicking anywhere on a record will change the colour but again the entire column is changed.
I have tried using a dummy field to mirror the id field, "Accession Number" in the on current event to no avail.
I know I can do this with conditional formatting but it sees that each control will need multiple rules to work correctly.
It also means hours of work to change any one colour.
All controls in the detail area are bound.
How do I modify my code to make it work?

Private Sub Form_Current()
    Me.txtSelect = Me.txtAccess        'this has no effect
    setColours Nz(Me.txtNotes, ""), Me
    Me.txtSelect = Me.txtAccess
End Sub

Public Sub setColours(sStr As String, frm As Form)
    Dim R   As Integer
    Dim G   As Integer
    Dim B   As Integer
    Dim str As String
    Dim ctl As Control
    R = 240         ' default colour if "Notes" is null
    G = 240
    B = 240
    Select Case sStr
        Case "boxes":
            R = 230
            G = 192
            B = 228
            str = "BO"
        Case "Location":
            R = 230
            G = 192
            B = 228
            str = "BO"
        Case "No Data":
            R = 218
            G = 138
            B = 151
            str = "ND"
        Case "Double":
            R = 159
            G = 211
            B = 225
            str = "DO"
        Case "Duplicate":
            R = 157
            G = 245
            B = 172
            str = "DU"
        Case "Original":
            R = 209
            G = 234
            B = 240
            str = "OR"
        Case "No Match":
            R = 225
            G = 204
            B = 75
            str = "MA"
        Case "No Raw":
            R = 212
            G = 150
            B = 148
            str = "RA"
        Case "No Jpg":
            R = 229
            G = 190
            B = 189
            str = "JP"
        Case "Spelling":
            R = 255
            G = 255
            B = 255
            str = "SP"
    End Select
    With frm
        For Each ctl In .Section(acDetail).Controls
            ctl.BackColor = RGB(240, 240, 240)
            ctl.ForeColor = RGB(0, 0, 0)
            If ctl.ControlType = acTextBox Then
                If InStr(ctl.Tag, str) > 0 Then       
                    ctl.BackColor = RGB(R, G, B)
                    If str = "SP" Then ctl.ForeColor = RGB(250, 0, 0)        '' The colour in this case is red text on white
                End If
            End If
        Next
    End With
End Sub

 

[arnelgp]

use Paint Event of the Detail section to set the color of each Conditions.

 

[tvanstiphout]

I noticed an unrelated bug:
Case "Location":<br> R = 230<br> G = 192<br> B = 228<br> str = "BO"

Did you know you can add FormatConditions programmatically?

 

[CJ_London]

you can simplify your code

for example

Public Sub setColours(sStr As String, frm As Form)
    Dim bc as long  
    Dim str As String
    Dim ctl As Control
    bc=rgb(240 ,240,240)        ' default colour if "Notes" is null
 
    Select Case sStr
        Case "boxes":
          bc=rgb(230,192,228)
            str = "BO"
        Case...
       ....
       ....
       etc
    End Select
    With frm
        For Each ctl In .Section(acDetail).Controls
            ctl.BackColor = RGB(240, 240, 240)
            ctl.ForeColor = RGB(0, 0, 0)
            If ctl.ControlType = acTextBox Then
                If InStr(ctl.Tag, str) > 0 Then      
                    ctl.BackColor = bc
                    If str = "SP" Then ctl.ForeColor = RGB(250, 0, 0)        '' The colour in this case is red text on white
                End If
            End If
        Next
    End With
End Sub

 

[MajP]

I know I can do this with conditional formatting but it sees that each control will need multiple rules to work correctly.
It also means hours of work to change any one colour

I do not get this statement. Yes you would select all the controls and apply the rules. However this should take 5-10 minutes and be done. Not sure how you are doing CF, but clearly you are doing something wrong because this is a trivial thing to do in CF.

 

[Ken Sheridan]

An easy way, which predates conditional formatting, to colour code rows on the basis of a value in a column is to firstly create a table with, in your case, a Notes column its primary key and a further column with a single plain bitmap image of distinct colours. The choice of colours is virtually infinite. Then base a form on a query which joins the current table to the Colours table on the Notes column.

In the form set the BackStyle property of all controls in the detail section to Transparent. Add a bound object frame to the form, bound to the bitmap image column in the Colours table. Size the control so that it covers the entire detail section, and send it to the back. The text in the controls will then appear on whatever colour matches the value in the Notes column.

If you want to change a colour it's simply a matter of doing so for the one row in the Colours table. The colours used should allow the text to show clearly of course, so it's best not to use heavily saturated colours.

Personally, rather than colouring the whole of the section, I prefer to puts a colour 'patch' at the end of the section. Colouring the whole of the section can be rather overpowering, particularly with a large number of colours.

 

[John Sh]

Personally, rather than colouring the whole of the section, I prefer to puts a colour 'patch' at the end of the section. Colouring the whole of the section can be rather overpowering, particularly with a large number of colours.

Thanks Ken. That sounds like a good idea and easily adaptable to my code or CF.
Many thanks to all responders.

John