Prev: Report with vertical columns
Next: Dynamically Changing Rowsource on Graph
From: J Lagos on 6 Jul 2008 08:52
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.

From: John Spencer on 6 Jul 2008 09:19
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
From: J Lagos on 6 Jul 2008 09:34
I'll give this a try Mr. Spencer. And I will also give you my gratitude for
your kind assistance.

JL.

"John Spencer" <spencer(a)chpdm.umbc> wrote in message
news:e2vrlr23IHA.776(a)TK2MSFTNGP04.phx.gbl...
> 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

From: J Lagos on 6 Jul 2008 11:44
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>
"John Spencer" <spencer(a)chpdm.umbc> wrote in message
news:e2vrlr23IHA.776(a)TK2MSFTNGP04.phx.gbl...
> 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

From: Marshall Barton on 6 Jul 2008 12:35
J Lagos wrote:

>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.

--
Marsh
MVP [MS Access]
 |  Next  |  Last
Pages: 1 2
Prev: Report with vertical columns
Next: Dynamically Changing Rowsource on Graph