Change record design for each record based on calculation

[rivereridanus]

I have a custom report whose dataset is just a table. For each record in the table, the report properly shows up. However, I need to make colored dials visible based on each record (think stoplight- red,green,yellow circles). When I do this on the report it colors the appropriate circle, but only for the first record. All the other dials are given a circle the same color as the one which was correctly calculated for the first record.



Is there a way to have access re-run the ChangeColor sub and change the image for each individual record on the report instead only running once coloring them all the same?

Private Sub Report_Load()
    'FILL METADATA
    Owner = DLookup("DialOwner", "tblMetadata", "DialName = 'Risk Maturity'")
    sponsor = DLookup("DialSponsor", "tblMetadata", "DialName = 'Risk Maturity'")
    If sponsor & "" = "" Then
        sponsor = "N/A"
    End If
    Me.Label33.Caption = "Dial Owner: " & Owner
    Me.Label34.Caption = "Dial Sponsor: " & sponsor
    
    'GET COLOR
    ChangeColor
End Sub

Private Sub ChangeColor()
On Error Resume Next
    riskavg = CDbl(Me.RiskMaturity.value) / 100
    'lookup the threshold info for selected year
        riskThreshGreen = DLookup("Green", "tblThresholds", "Metric = 'Risk Maturity'")
        riskThreshYellow = DLookup("Yellow", "tblThresholds", "Metric = 'Risk Maturity'")
        
        If (riskavg > riskThreshGreen Or riskavg = riskThreshGreen) Then
            'MAKE DIAL GREEN
            Me.Image35.Visible = True
            Me.Image36.Visible = False
            Me.Image37.Visible = False
        ElseIf (riskavg > riskThreshYellow Or riskavg = riskThreshYellow) Then
            'MAKE DIAL YELLOW
            Me.Image35.Visible = False
            Me.Image36.Visible = True
            Me.Image37.Visible = False
        Else:
            'MAKE DIAL RED
            Me.Image35.Visible = False
            Me.Image36.Visible = False
            Me.Image37.Visible = True
        End If
End Sub

[aytee111]

Put the code into the "OnFormat" of the Detail.

[rivereridanus]

That's exactly what I needed- Thanks!