Color box on Report

[speltrain]

i have a report that has a olecolorbox on it that is used to display a color based on a calculated field. The color box will display the either green, yellow, or red based on this calculated value. The problem is that the box is only keying on the first Record displayed on the report.

How can i get a this color box to display the right color for each record on the report.

The color is determined by the following range:

value = val(txtSumMetricIndex)

If (Value >= 0) And (Value <= 1) Then
oleColorBox.BackColor = vbRed
ElseIf (Value > 1) And (Value <= 2) Then
oleColorBox.BackColor = vbRed
ElseIf (Value > 2) And (Value <= 3) Then
oleColorBox.BackColor = vbYellow
ElseIf (Value > 3) And (Value <= 4) Then
oleColorBox.BackColor = vbYellow
ElseIf (Value > 4) And (Value <= 5) Then
oleColorBox.BackColor = vbGreen
End If

Thanks in advance

 

[dhookom]

Your code needs to be in the section containing the box. I would also use Select Case rather than a bunch of ElseIfs.

value = val(txtSumMetricIndex)
Select Case Value
Case 0 To 1
oleColorBox.BackColor = vbRed
Case 1 to 2
oleColorBox.BackColor = vbRed
Case 2 to 3
oleColorBox.BackColor = vbYellow
Case 3 to 4
oleColorBox.BackColor = vbYellow
Case 4 to 5
oleColorBox.BackColor = vbGreen
End Select


Duane
MS Access MVP