Change color based on value in report

[RogerC]

I would like to change the background fill color of a text box in a report based on the value that the query returns for that text box. Is this possible?

 

[janika]

For example:

Private Sub TextBox1_Change()

TextBox1.BackColor = TextBox1.Value

End Sub

But you can use "if", as well:
if TextBox1.Value >10 then TextBox1.BackColor = 0

I hope that helps!
kj

 

[RogerC]

Thank you janika. If I wanted to use the 'if' method, where do I put the If statement? I'm somewhat new to Reports.

 

[janika]

Instead of this line:
TextBox1.BackColor = TextBox1.Value

If you write more details, I write you the code exactly!
kj

 

[SydneyGeek]

Roger,

you can also use formatting code in the report. Right-click the Detail section (I'm assuming that's where the text box lives). Select Properties. In the Events tab, go to the code for the OnFormat event. You can put your conditional stuff in there.
eg:

Select Case MyTextBox.Value
  Case 0 to 12
     MyTextBox.BackColor=65535 'Yellow
  Case 12.1 to 24
     MyTextBox.Font.Color=255 'Red
  Case Else
     MyTextBox.Font.Color=16777215 'White
End Select

Denis

 

[Norie]

Janika

Does a text box have any events in a report?

Is it not just the headers. footers detail and report that have events?

 

[janika]

Janika

Does a text box have any events in a report?

Is it not just the headers. footers detail and report that have events?

Sorry, I overlook the topic (Microsoft Acces), and I thought of a simple excel TextBox...
I think you are in right Norie... Sorry once again...
kj

 

[Philem]

Janika's post still stands. It just goes in the OnFormat section of the Report Detail area. I've done this several times, to great affect! One caveat: textboxes are standard set to BackStyle=Transparent, and this must be changed for the colours to show. Here's an example of how this works:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim DateType
Dim LSD As Date
Dim RSD As Date
LSD = Me.[LATE SHIP Date].Value
If Not IsNull(Me.[Revised SN]) Then
    RSD = Me.[Revised SN].Value
End If
Select Case DateType
    Case DateType = Not IsNull(Me.[Revised SN])
        If Me.SAILING - RSD > 0 Then
            Me.ORDER.BackColor = vbRed
            Me.ORDER.ForeColor = vbWhite
        Else
            Me.ORDER.BackColor = vbWhite
            Me.ORDER.ForeColor = vbBlack
        End If
    Case Else
        If Me.SAILING - LSD > 0 Then
            Me.ORDER.BackColor = vbRed
            Me.ORDER.ForeColor = vbWhite
        Else
            Me.ORDER.BackColor = vbWhite
            Me.ORDER.ForeColor = vbBlack
        End If
End Select
End Sub

NOTE: all the textboxes referenced here have a BackStyle=Normal

HTH

Pl

 

[RogerC]

Thank you so much for your help, janika!

I have a text box in the Details section of my report that is bound to the 'Expr2' field in my query. When 'Expr2' returns the text 'Pending' I would like the Backcolor to be blue. When 'Expr2' returns the text 'Confirmed' I would like the Backcolor to be red.

 

[SydneyGeek]

Hi Robert, try this in the OnFormat event of the Detail section...
Also, note Philem's comment that textbox backgrounds need to be set to Solid for you to see the colours.

Select Case [Epxr2].Value 
  Case "Pending"
     [Expr2].BackColor=16711680 'Blue 
  Case "Confirmed" 
     [Expr2].BackColor=255 'Red 
  Case Else 
     [Expr2].BackColor=16777215 'White 
End Select

Denis