change a reports control backcolor

[J Lagos]

I want to change the backcolor of textboxes in a report depending on their
value.
I messed with this for a few hours but I'm not savvy with the syntax for
getting at controls in a report.

can anyone suggest a direction? Conditional Formatting does'nt provide
flexibility or enough format options

For example the Textboxes would contain values like "8R", "8S", "10S",

the numbers would change depending on user input, but the letters are
restricted to about 8 different letters that mean different things. I want
to change the color based on the letter value.

 

[John Spencer]

One method would be to enter vba code into the sections format event.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Select Case Right(Me.txtControl,1)
Case "R"
Me.txtControl.BackColor = vbRed
Case "B"
Me.txtControl.BackColor = vbBlack
Case "S"
Me.txtControl.BackColor = 23568
...
Case Else
Me.txtControl.BackColor = vbWhite
End Select

End Sub

 

[J Lagos]

I'll give this a try Mr. Spencer. And I will also give you my gratitude for
your kind assistance.

JL.

 

[J Lagos]

I wanted to do this in a loop so as to avoid explicit references to text
boxes
So, here is what I have...it works to a point but the backcolors do not come
through as I had intended

I'm not entirely sure what is wrong here. The Debug.print values seem to be
just fine, but the backcolor doesn't change as intended based on the values.
-------

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
Dim strV

Const conTransparent = 0
Const conWhite = 16777215
Const conRed = 255
For Each ctl In Me.Controls

With ctl
If .ControlType = acTextBox And .Section = acDetail Then
strV = ctl
strV = Right(strV, 1)
.BackStyle = acNormal
Debug.Print ctl.Name
Debug.Print strV
Select Case strV
Case Is = "R"

.BackColor = 255

Case Is = "L"
.BackColor = 1

Case Else

End Select
End If
End With

Next ctl

End Sub


<SNIP>

 

[Marshall Barton]

I wanted to do this in a loop so as to avoid explicit references to text
boxes
So, here is what I have...it works to a point but the backcolors do not come
through as I had intended

I'm not entirely sure what is wrong here. The Debug.print values seem to be
just fine, but the backcolor doesn't change as intended based on the values.
-------

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
Dim strV

Const conTransparent = 0
Const conWhite = 16777215
Const conRed = 255
For Each ctl In Me.Controls

With ctl
If .ControlType = acTextBox And .Section = acDetail Then
strV = ctl
strV = Right(strV, 1)
.BackStyle = acNormal
Debug.Print ctl.Name
Debug.Print strV
Select Case strV
Case Is = "R"

.BackColor = 255

Case Is = "L"
.BackColor = 1

Case Else

End Select
End If
End With

Next ctl

End Sub

Use vbRed and vbWhite instead of 255 and 1 (1 is as close to
black as you can get).

Your loop is broader that necessary. You could save sine
time by just looping through Section(0).Controls.
Personally, I prefer to explicitly identify the controls by
setting their Tag property to some specific string (e.g.
RS), then testing for that instead of checking ControlType:

For Each ctl In Me.Section(0).Controls
With ctl
If .Tag = "RS" Then
strV = Right(ctl, 1)
Debug.Print ctl.Name
Debug.Print strV
Select Case strV
Case "R"
.BackColor = RGB(255,224,244) 'pale red
Case "L"
.BackColor = vbYellow
Case Else
.BackColor = vbWhite
End Select
End If
End With
Next ctl

If you want different colors than the predefined constants,
use the RGB function.

 

[J Lagos]

Hmm. I like the code simplification.

I tried this but the result was the same, code ran fine, but no color
changes. I'm stumped.

I assume section(0) is the detail section. I also have backstyle set to
acNormal just for safety sake. I set all controls with tag "W" and used that
to refer to the controls I wanted checked. Still not giving me the result I
am trying for.

<>Snip

 

[J Lagos]

Never mind...I figured out what was wrong.
I'm admitting my error and posting it here in case someone else runs into
this issue.

I had conditional formatting set on the text boxes, and it was over riding
the code formatting.

Sorry.

Jon.