Setting Backcolor of control in access report 1

[STRATMAN1]

I have been working for several days to write code for a report in order to change the backcolor of a report control noted here as "[1]" if it is the maximum value in the field. I am almost there, but I am getting an error message I dont know how to deal with. Here is the code:

Option Compare Database
Dim lngmax As Long
Dim LNGYEL As Long
Dim LNGW As Long
Private Sub detail_format(Cancel As Integer, formatcount As Integer)
LNGYEL = 12632256
LNGW = 16777215
If Me![1].value = lngmax Then
Me![1].BackColor = LNGYEL
Else
Me![1].BackColor = LNGW
End If
End Sub

Private Sub Report_open(Cancel As Integer)

lngmax = DMax("[1]", "qtblsumlee", "[fa_cpmi_fac_def_grade_no]")

End Sub

The error message I get is "object does not support this property or method", and it points to the line of code
Me![1].Backcolor=lngw. And so forth. Any help in how to correct this is greatly appreciated!!

 

[THWatson]

Stratman1
I'm not sure what colors those numerical values stand for, but rather than use them have you tried vbRed, vbGreen, vbYellow etc. on the Format event for the section in which the control lies?

In other words,
If Me.YourTextBox = whatever Then
Me.YourTextBox.BackColor = vbYellow
Else ...
End If

Tom

 

[dhookom]

If qtblsumlee is the record source of the report, then I would add a text box to the Report Header section:
Name: txtMax1
Control Source: =Max([1])
Then add conditional formatting on the control using the expression:
[txtMax1]=[1]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]