database - Changing Row Colour according to condition

Question

I am using MS Access for database Administration. I have seeveral linked tables producing different reports. I have already found a similar question on stackoverflow and did my reaserach on this issue. I am trying to paint rows according to conditions (Duration <20 Paint Beige, 2060 Red)

I am using VBasic. This is my code.

Please let me know what you think. Many thanks for your help!

Sub ChangeBackType()

    Me.Date.BackStyle = 1
    Me.Cell.BackStyle = 1
    Me.Maintenance_Category.BackStyle = 1
    Me.Duration.BackStyle = 1
    Me.Line_Description.BackStyle = 1
    Me.Machine_Description.BackStyle = 1
    Me.Station_Number.BackStyle = 1
    Me.Fault_Description.BackStyle = 1
    Me.GM.BackStyle = 1
    Me.Remarks.BackStyle = 1
    Me.Intervention.BackStyle = 1
    Me.Technician_Name.BackStyle = 1
    Me.Shop_Floor.BackStyle = 1

End Sub

 Sub Paint_Rows_Red()

'Same method for other colours

    Me.Date.BackColor = RGB(255, 29, 29)
    Me.Cell.BackColor = RGB(255, 29, 29)
    Me.Maintenance_Category.BackColor = RGB(255, 29, 29)
    Me.Duration.BackColor = RGB(255, 29, 29)
    Me.Line_Description.BackColor = RGB(255, 29, 29)
    Me.Machine_Description.BackColor = RGB(255, 29, 29)
    Me.Station_Number.BackColor = RGB(255, 29, 29)
    Me.Fault_Description.BackColor = RGB(255, 29, 29)
    Me.Intervention.BackColor = RGB(255, 29, 29)
    Me.GM.BackColor = RGB(255, 29, 29)
    Me.Remarks.BackColor = RGB(255, 29, 29)
    Me.Technician_Name.BackColor = RGB(255, 29, 29)
    Me.Shop_Floor.BackColor = RGB(255, 29, 29)

End Sub

    Private Sub Report_Load()


    ChangeBackType
     Dim Test As String

     Test = TestString2

     TestString2 = Me!Duration.Value
     TestString2 = FormatDateTime(TestString2, vbShortTime)

     If TestString2 <= CDate("00:20") Then

        Paint_Rows_Beige

     ElseIf TestString2 > CDate("00:20") And TestString2 < CDate("00:60") Then

        Paint_Rows_Orange

     ElseIf TestString2 >= CDate("00:60") Then

        Paint_Rows_Red

     End If

I am trying to paint rows according to the condition mentioned above in a report. With this code I am only getting one colour..

Answer 1

When you open a report/form with multiple rows, the code you use will only apply to the first row appearing in the data. So in short, your code will not work.

There is however a way to color rows on different criteria using conditional formatting.