Change background color in one text box based on text in 2nd text

[Gnerks]

I am working in Access 2007 with reports. I have two text boxes one is
populated (from a 6 field table) with a series of statuses (Complete,
Delayed, Unsigned MOA, etc.). The second textbox is populated (from the same
table) with the titles of studies and each study name is different. When
the report loads I want each study with a status of Complete to have either
the backcolor or the forecolor blue in the study title text box, if the
status is Delayed then red, etc. Both textboxes are bound to the appropriate
field from my table.

I tried the Select Case statement provided by Wayne Morgan in a CONDITIONAL
question but it is not working. I can get the color to change in the study
title column with the Select Case in the Report_Current() event if I click on
a status field. For example if the first record has a status of COMPLETE and
i click on this, all of the Study Title boxes turn blue, or red for Delayed.

I need it to look at one record at a time and base the color of the title
textbox on the text of the status textbox. My code follows:


Option Compare Database

Private Sub Report_Current()
Select Case Me.txtStatus
Case "COMPLETE"
Me.txtStudyTitle.BackColor = RGB(30, 144, 255)
Case "DELAYED"
Me.txtStudyTitle.BackColor = vbRed
Case "[UNSIGNED MOA]" 'used brakets because two words
Me.txtStudyTitle.BackColor = vbGreen
End Select
End Sub

Any help would be appreciated!

 

[Marshall Barton]

I am working in Access 2007 with reports. I have two text boxes one is
populated (from a 6 field table) with a series of statuses (Complete,
Delayed, Unsigned MOA, etc.). The second textbox is populated (from the same
table) with the titles of studies and each study name is different. When
the report loads I want each study with a status of Complete to have either
the backcolor or the forecolor blue in the study title text box, if the
status is Delayed then red, etc. Both textboxes are bound to the appropriate
field from my table.

I tried the Select Case statement provided by Wayne Morgan in a CONDITIONAL
question but it is not working. I can get the color to change in the study
title column with the Select Case in the Report_Current() event if I click on
a status field. For example if the first record has a status of COMPLETE and
i click on this, all of the Study Title boxes turn blue, or red for Delayed.

I need it to look at one record at a time and base the color of the title
textbox on the text of the status textbox. My code follows:


Option Compare Database

Private Sub Report_Current()
Select Case Me.txtStatus
Case "COMPLETE"
Me.txtStudyTitle.BackColor = RGB(30, 144, 255)
Case "DELAYED"
Me.txtStudyTitle.BackColor = vbRed
Case "[UNSIGNED MOA]" 'used brakets because two words
Me.txtStudyTitle.BackColor = vbGreen
End Select
End Sub

That sounds very strange. Report's do not have a Current
event. Also, you can not click on a value in a report, much
less have that cause other parts of the report to change
their appearance.

What you are describing is a common question for a form
displayed in continuous view. If that's what you really
have, then you can not do it with code. Instead, you need
to use Conditional Formatting (Format menu).

 

[Gnerks]

If you go to the Property Sheet for Report - and you select Code Builder, the
Report_Current() is what automatically appears in the VB Code area. As I
stated, the code I inserted below will change the text font color if you
click in the textbox AND it changes all the records for that field the same
color, the color changes by the text within the text box. If I click in a
STATUS box that has COMPLETE all of my status boxes are blue fonted - if I
click in a STATUS box that has IN PROGRESS all of my status boxes are green
fonted. I want each record to be the appropriate color, based on the text in
the status textbox.

I would have used the CONDITIONAL FORMATTING option in Access, however, I
have 5 color options plus default white - and only 3 conditional formatting
choices.

Any ideas? I am not real familiar with coding for recordsets - but think
that is where I need to go so that each record is formatted as it is loaded
into the report.

I am working in Access 2007 with reports. I have two text boxes one is
populated (from a 6 field table) with a series of statuses (Complete,
Delayed, Unsigned MOA, etc.). The second textbox is populated (from the same
table) with the titles of studies and each study name is different. When
the report loads I want each study with a status of Complete to have either
the backcolor or the forecolor blue in the study title text box, if the
status is Delayed then red, etc. Both textboxes are bound to the appropriate
field from my table.

I tried the Select Case statement provided by Wayne Morgan in a CONDITIONAL
question but it is not working. I can get the color to change in the study
title column with the Select Case in the Report_Current() event if I click on
a status field. For example if the first record has a status of COMPLETE and
i click on this, all of the Study Title boxes turn blue, or red for Delayed.

I need it to look at one record at a time and base the color of the title
textbox on the text of the status textbox. My code follows:


Option Compare Database

Private Sub Report_Current()
Select Case Me.txtStatus
Case "COMPLETE"
Me.txtStudyTitle.BackColor = RGB(30, 144, 255)
Case "DELAYED"
Me.txtStudyTitle.BackColor = vbRed
Case "[UNSIGNED MOA]" 'used brakets because two words
Me.txtStudyTitle.BackColor = vbGreen
End Select
End Sub

That sounds very strange. Report's do not have a Current
event. Also, you can not click on a value in a report, much
less have that cause other parts of the report to change
their appearance.

What you are describing is a common question for a form
displayed in continuous view. If that's what you really
have, then you can not do it with code. Instead, you need
to use Conditional Formatting (Format menu).

 

[Marshall Barton]

If you go to the Property Sheet for Report - and you select Code Builder, the
Report_Current() is what automatically appears in the VB Code area. As I
stated, the code I inserted below will change the text font color if you
click in the textbox AND it changes all the records for that field the same
color, the color changes by the text within the text box. If I click in a
STATUS box that has COMPLETE all of my status boxes are blue fonted - if I
click in a STATUS box that has IN PROGRESS all of my status boxes are green
fonted. I want each record to be the appropriate color, based on the text in
the status textbox.

I would have used the CONDITIONAL FORMATTING option in Access, however, I
have 5 color options plus default white - and only 3 conditional formatting
choices.

Any ideas? I am not real familiar with coding for recordsets - but think
that is where I need to go so that each record is formatted as it is loaded
into the report.

As I said before, I have never heard of a report Current
event. I guess that's something new in A2007.

Whatever you're working with, a recordset is not going to be
useful.

Regardless of all that, in a report, put your code in the
Format event of the section containing the text box you want
to color.

 

[Gnerks]

Thank you - i will do that - just was unsure what to do but didn't think (or
even know about) the format event of the DETAIL section - where my textbox is
located.