For each loop

[pwdpwd via AccessMonster.com]

Hi - could anyone help me with a simplification of some code that I have that
works ok, but is very onerous, and I'm sure that there must be a better way.

I have a report that contains 53 columns; as they are very small, they
contain either an 'X' or a 'T'. To make the T symbol stand out I run a
routine on the <On Print> event of the Detail section of the form that turns
the BackColor to Black and the ForColor to White. Code is as follows:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim lngblack As Long, lngwhite As Long
lngblack = RGB(0, 0, 0)
lngwhite = RGB(255, 255, 255)
If Me![Text115] = "T" Then
Me![Text115].BackColor = lngblack
Me![Text115].ForeColor = lngwhite
Else
Me![Text115].BackColor = lngwhite
Me![Text115].ForeColor = lngblack
End If

Trouble is, as there are 53 text boxes on the Detail section, is there any
way in VBA that I can shorten the code without having to replicate the last 7
lines of the above code for each of the 53 boxes?

 

[Brendan Reynolds]

Hi - could anyone help me with a simplification of some code that I have
that
works ok, but is very onerous, and I'm sure that there must be a better
way.

I have a report that contains 53 columns; as they are very small, they
contain either an 'X' or a 'T'. To make the T symbol stand out I run a
routine on the <On Print> event of the Detail section of the form that
turns
the BackColor to Black and the ForColor to White. Code is as follows:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim lngblack As Long, lngwhite As Long
lngblack = RGB(0, 0, 0)
lngwhite = RGB(255, 255, 255)
If Me![Text115] = "T" Then
Me![Text115].BackColor = lngblack
Me![Text115].ForeColor = lngwhite
Else
Me![Text115].BackColor = lngwhite
Me![Text115].ForeColor = lngblack
End If

Trouble is, as there are 53 text boxes on the Detail section, is there any
way in VBA that I can shorten the code without having to replicate the
last 7
lines of the above code for each of the 53 boxes?

First assign some unique value to the Tag property of these text boxes to
distinguish them from other controls on the report. You can select all the
text boxes by holding down the shift key and then type something into the
Tag property to assign it to all the controls at once, so you don't have to
repeat it 53 times. Now you can loop through the Controls collection and
examine the Tag property to see if this is one of the controls your code is
looking for ...

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Tag = "ChangeMe" Then
If ctl.Value = "T" Then
ctl.BackColor = vbBlack
ctl.ForeColor = vbWhite
Else
ctl.BackColor = vbWhite
ctl.ForeColor = vbBlack
End If
End If
Next ctl

Note that the lngBlack and lngWhite variables, while they will work, are not
really necessary, as the intrinsic constants vbBlack and vbWhite will do the
same job.

 

[pwdpwd via AccessMonster.com]

Thanks - works like a treat - thanks a lot.

Bob

Hi - could anyone help me with a simplification of some code that I have
that

[quoted text clipped - 23 lines]

last 7
lines of the above code for each of the 53 boxes?

First assign some unique value to the Tag property of these text boxes to
distinguish them from other controls on the report. You can select all the
text boxes by holding down the shift key and then type something into the
Tag property to assign it to all the controls at once, so you don't have to
repeat it 53 times. Now you can loop through the Controls collection and
examine the Tag property to see if this is one of the controls your code is
looking for ...

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Tag = "ChangeMe" Then
If ctl.Value = "T" Then
ctl.BackColor = vbBlack
ctl.ForeColor = vbWhite
Else
ctl.BackColor = vbWhite
ctl.ForeColor = vbBlack
End If
End If
Next ctl

Note that the lngBlack and lngWhite variables, while they will work, are not
really necessary, as the intrinsic constants vbBlack and vbWhite will do the
same job.

 

[David W. Fenton]

First assign some unique value to the Tag property of these text
boxes to distinguish them from other controls on the report. You
can select all the text boxes by holding down the shift key and
then type something into the Tag property to assign it to all the
controls at once, so you don't have to repeat it 53 times. Now you
can loop through the Controls collection and examine the Tag
property to see if this is one of the controls your code is
looking for ...

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Tag = "ChangeMe" Then
If ctl.Value = "T" Then
ctl.BackColor = vbBlack
ctl.ForeColor = vbWhite
Else
ctl.BackColor = vbWhite
ctl.ForeColor = vbBlack
End If
End If
Next ctl

Note that the lngBlack and lngWhite variables, while they will
work, are not really necessary, as the intrinsic constants vbBlack
and vbWhite will do the same job.

Give that this has to run in the Format event of the detail of the
report, I think you'd vastly speed up the report if you assigned
your controls to a custom collection in the form's OnOpen event, and
then processed that collection instead of the report's entire
Controls collection. You'll not only have fewer items to loop
through, but you also won't have to test a slow text property in
order to find out which controls to operate on.