Problem with Formatting code

[CSDunn]

Hello,
In my report, the following code sets the BackColor value of any acTextBox =
0 If the value in the TextBox = 'X':

Private Sub FormHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Value = "X" Then ctl.BackColor = 0
End If
Next ctl
End Sub

The problem is, once an instance of the report is found to have an 'X' in a
given TextBox, the same TextBox in every other instance of that report will
have the BackColor value set to zero whether or not the value in the TextBox
= 'X' for those other instances.

How can I change the above code so that if the ctl.Value <> "X", the
ctl.BackColor will not be changed?

Thanks for your help!

CSDunn

 

[fredg]

Hello,
In my report, the following code sets the BackColor value of any acTextBox =
0 If the value in the TextBox = 'X':

Private Sub FormHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Value = "X" Then ctl.BackColor = 0
End If
Next ctl
End Sub

The problem is, once an instance of the report is found to have an 'X' in a
given TextBox, the same TextBox in every other instance of that report will
have the BackColor value set to zero whether or not the value in the TextBox
= 'X' for those other instances.

How can I change the above code so that if the ctl.Value <> "X", the
ctl.BackColor will not be changed?

Thanks for your help!

CSDunn

CS,
If you turn the color on, you then have to turn it off when the
critreria is not met.

Private Sub FormHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Value = "X" Then
ctl.BackColor = 0
Else
ctl.BackColor = vbWhite
End If
Next ctl
End Sub

 

[Marshall Barton]

In my report, the following code sets the BackColor value of any acTextBox =
0 If the value in the TextBox = 'X':

Private Sub FormHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Value = "X" Then ctl.BackColor = 0
End If

You have to set the color either way:

If ctl.Value = "X" Then
ctl.BackColor = 0
Else
ctl.BackColor = vbRed ' or whatever
End If

 

[CSDunn]

Thank you for this input. Actually, some of the BackColor colors are gray,
and some are white, and need to remain that way if the TextBox value is not
'X'. I cannot express a single color in the 'Else' statement. So if the
TextBox value is not X, I either need for nothing to happen to the
ctl.BackColor, or I need to express that the ctl.BackColor = gray if
ctl.BackColor =gray and the same for white.

Which way should I go, and how would the code be modified?

Thanks again!

CSDunn

 

[fredg]

Thank you for this input. Actually, some of the BackColor colors are gray,
and some are white, and need to remain that way if the TextBox value is not
'X'. I cannot express a single color in the 'Else' statement. So if the
TextBox value is not X, I either need for nothing to happen to the
ctl.BackColor, or I need to express that the ctl.BackColor = gray if
ctl.BackColor =gray and the same for white.

Which way should I go, and how would the code be modified?

Thanks again!

CSDunn

CS,
If you turn the color on, you then have to turn it off when the
critreria is not met.

Private Sub FormHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Value = "X" Then
ctl.BackColor = 0
Else
ctl.BackColor = vbWhite
End If
Next ctl
End Sub

What determines if the default background is white or gray or some other
color. The control's name? Some other criteria? Adapt this.

If ctl.ControlType = acTextBox Then
If ctl.Value = "X" Then
ctl.BackColor = 0
Else
If ctl.name = "Whatever1" or ctl.name = "Whatever2 Then
ctl.BackColor = 12632256 ' Gray value
ElseIf ctl.name = "Whatever3" or ctl.name = "Whatever4" Then
ctl.BackColor = Someothercolor
Else
ctl.BackColor = vbWhite
End If
End If

 

[CSDunn]

I have about 200 TextBoxes on the report, and they are all bound. If I have
to name all the TextBoxes to address the BackColor, then I've defeated the
purpose of having code that would globally change the BackColor of the
TextBoxes.

These reports are student report cards that show subject grades and marks
for state standards. An individual report shows all the grades for a
specific student. The actual configuration of the report is one report with
twelve sub reports (six English and six Spanish reports for K-5). The sub
reports are 'stacked' one each other, and each Spanish/English report
represents a different grade level (because the report cards are different
for each grade). The sub reports are located within a 'StudentID' header of
the main report. There is a Macro assigned to the OnFormat event of this
StudentID header that hides or shows a Spanish or English report card based
on the grade of the student and whether or not a checkbox called 'Spanish'
was checked.

For the purposes of the problem, I can look at this as if there were just
two sub reports, lets say an English and Spanish report for the first grade.
When the report is displayed in Print Preview, each page shows the records
of a different student in either a Spanish or English version. If I mark an
'X' in one of the TextBoxes of the input form, the BackColor on the same
TextBox on, say, the English report shows up as blacked out. The problem
is, the report TextBox would show up black on every subsequent English
report. If the next first grade student has a Spanish report card, and 'X'
was not input in the input form, then that report, and subsequent Spanish
reports would be okay, because the English and Spanish reports are
physically different.

I hope this makes sense. If there was a way that I could have the code
reevaluate each page of the report for 'X' in the text boxes, that might
solve the problem. I need to keep the Macro for the OnFormat event that I
already have in the StudentID header so that I can display the correct grade
and language of the report card.

Thanks again for your help!

CSDunn

 

[Douglas J. Steele]

Each control has a Tag property that you can use for whatever purpose you
want. Try putting the default colour of each control as its Tag property. In
that way, you'll be able to use:

Private Sub FormHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Value = "X" Then
ctl.BackColor = 0
Else
ctl.BackColor = ctl.Tag
End If
End If
Next ctl
End Sub

Note that you don't need to manually set all of the tag values. The
following code snippet will do it for you (assuming that the form is open
when you run the code):

Sub UsingTags()
Dim frmCurr As Form
Dim ctlCurr As Control

Set frmCurr = Forms("Form1")

For Each ctlCurr In frmCurr.Controls
If ctlCurr.ControlType = acTextBox Then
ctlCurr.Tag = ctlCurr.BackColor
End If
Next ctlCurr

End Sub


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I have about 200 TextBoxes on the report, and they are all bound. If I have
to name all the TextBoxes to address the BackColor, then I've defeated the
purpose of having code that would globally change the BackColor of the
TextBoxes.

These reports are student report cards that show subject grades and marks
for state standards. An individual report shows all the grades for a
specific student. The actual configuration of the report is one report with
twelve sub reports (six English and six Spanish reports for K-5). The sub
reports are 'stacked' one each other, and each Spanish/English report
represents a different grade level (because the report cards are different
for each grade). The sub reports are located within a 'StudentID' header of
the main report. There is a Macro assigned to the OnFormat event of this
StudentID header that hides or shows a Spanish or English report card based
on the grade of the student and whether or not a checkbox called 'Spanish'
was checked.

For the purposes of the problem, I can look at this as if there were just
two sub reports, lets say an English and Spanish report for the first grade.
When the report is displayed in Print Preview, each page shows the records
of a different student in either a Spanish or English version. If I mark an
'X' in one of the TextBoxes of the input form, the BackColor on the same
TextBox on, say, the English report shows up as blacked out. The problem
is, the report TextBox would show up black on every subsequent English
report. If the next first grade student has a Spanish report card, and 'X'
was not input in the input form, then that report, and subsequent Spanish
reports would be okay, because the English and Spanish reports are
physically different.

I hope this makes sense. If there was a way that I could have the code
reevaluate each page of the report for 'X' in the text boxes, that might
solve the problem. I need to keep the Macro for the OnFormat event that I
already have in the StudentID header so that I can display the correct grade
and language of the report card.

Thanks again for your help!

CSDunn


is

 

[CSDunn]

Douglas,
I tested both pieces of code on one Report ( I changed the second code
snippet accordingly), tested, and everything appears to be working. I'll
report back if I come across any glitches.

Thanks for your help!

CSDunn

Each control has a Tag property that you can use for whatever purpose you
want. Try putting the default colour of each control as its Tag property. In
that way, you'll be able to use:

Private Sub FormHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Value = "X" Then
ctl.BackColor = 0
Else
ctl.BackColor = ctl.Tag
End If
End If
Next ctl
End Sub

Note that you don't need to manually set all of the tag values. The
following code snippet will do it for you (assuming that the form is open
when you run the code):

Sub UsingTags()
Dim frmCurr As Form
Dim ctlCurr As Control

Set frmCurr = Forms("Form1")

For Each ctlCurr In frmCurr.Controls
If ctlCurr.ControlType = acTextBox Then
ctlCurr.Tag = ctlCurr.BackColor
End If
Next ctlCurr

End Sub