Color subreport fields need big help!

[Ben]

I have a form that changes the color of the combo box to a color based on the
user's choice.

if [PreviousStatus] = "Blue" Then
[PreviousStatus].BackColor = 16711680

I have this information in a table. Separately, on a subreport I want the
subreport to show the right color for whatever the value is from that table
from [PreviousStatus] to be on the subreport too. Is that possible? Does
that make sense?

 

[Marshall Barton]

I have a form that changes the color of the combo box to a color based on the
user's choice.

if [PreviousStatus] = "Blue" Then
[PreviousStatus].BackColor = 16711680

I have this information in a table. Separately, on a subreport I want the
subreport to show the right color for whatever the value is from that table
from [PreviousStatus] to be on the subreport too.

The subreport can get the information from main report by
using the Parent property.

For example, you could set the subreport detail section's
color by using this in the detail section's Format event
procedure:

Me.Section(0).BackColor = Parent.PreviousStatus.BackColor

 

[Ben]

Marshall,
Where do I find the Format event in Access 2003? Just guide me thru simple
steps to get all of this accomplished.

Thanks

Ben

I have a form that changes the color of the combo box to a color based on the
user's choice.

if [PreviousStatus] = "Blue" Then
[PreviousStatus].BackColor = 16711680

I have this information in a table. Separately, on a subreport I want the
subreport to show the right color for whatever the value is from that table
from [PreviousStatus] to be on the subreport too.

The subreport can get the information from main report by
using the Parent property.

For example, you could set the subreport detail section's
color by using this in the detail section's Format event
procedure:

Me.Section(0).BackColor = Parent.PreviousStatus.BackColor

 

[Marshall Barton]

Double click on the gray bar at the top of the appropriate
section.

Select the events tab in the property sheet.

Look down the list of event **properties** for the OnFormat
property.

Select [Event Procedure] from the drop list on the right
side of the property.

Click on the builder button [...] to the right of the drop
down button.

You should then be in the Format event **procedure** where
you can put the code.

Note that the code I posted made several assumptions that
may or may not be valid. E.g. that the color you want was
already set on the main report. If that was a poor
assumption and all you have is a value in the subreport
(similar to what you have in the form), then you can use
code like you have in the form.

If you want to change the color of a text box instead of the
section, then the code might be the same as the code in the
form.
--
Marsh
MVP [MS Access]

Where do I find the Format event in Access 2003? Just guide me thru simple
steps to get all of this accomplished.

The subreport can get the information from main report by
using the Parent property.

For example, you could set the subreport detail section's
color by using this in the detail section's Format event
procedure:

Me.Section(0).BackColor = Parent.PreviousStatus.BackColor

I have a form that changes the color of the combo box to a color based on the
user's choice.

if [PreviousStatus] = "Blue" Then
[PreviousStatus].BackColor = 16711680

I have this information in a table. Separately, on a subreport I want the
subreport to show the right color for whatever the value is from that table
from [PreviousStatus] to be on the subreport too.

 

[Ben]

Marshall,
OK. I got to the onFormat event. I entered the code in but nothing is
working. It says there is something wrong with the code and to debug it.

On the form the color changes just fine. I want the color to be on the
report too. So if someone on the form clicks on Blue for PreviousStatus, the
background color should color to blue.

Thanks

Ben

Double click on the gray bar at the top of the appropriate
section.

Select the events tab in the property sheet.

Look down the list of event **properties** for the OnFormat
property.

Select [Event Procedure] from the drop list on the right
side of the property.

Click on the builder button [...] to the right of the drop
down button.

You should then be in the Format event **procedure** where
you can put the code.

Note that the code I posted made several assumptions that
may or may not be valid. E.g. that the color you want was
already set on the main report. If that was a poor
assumption and all you have is a value in the subreport
(similar to what you have in the form), then you can use
code like you have in the form.

If you want to change the color of a text box instead of the
section, then the code might be the same as the code in the
form.
--
Marsh
MVP [MS Access]

Where do I find the Format event in Access 2003? Just guide me thru simple
steps to get all of this accomplished.

The subreport can get the information from main report by
using the Parent property.

For example, you could set the subreport detail section's
color by using this in the detail section's Format event
procedure:

Me.Section(0).BackColor = Parent.PreviousStatus.BackColor

Ben wrote:
I have a form that changes the color of the combo box to a color based on the
user's choice.

if [PreviousStatus] = "Blue" Then
[PreviousStatus].BackColor = 16711680

I have this information in a table. Separately, on a subreport I want the
subreport to show the right color for whatever the value is from that table
from [PreviousStatus] to be on the subreport too.

 

[Marshall Barton]

Since you did not post a Copy/Paste of the code you tried, I
can't tell what you did, much less what might be wrong.

OTOH, OnFormat is a ***property***, not an event or an event
procedure so you may be talking about something other than
what I was trying to suggest.

I also never noticed where you explained exactly where the
value of the PreviousStatus field is in the main or sub
report, so my suggested code could be off base.
--
Marsh
MVP [MS Access]

OK. I got to the onFormat event. I entered the code in but nothing is
working. It says there is something wrong with the code and to debug it.

On the form the color changes just fine. I want the color to be on the
report too. So if someone on the form clicks on Blue for PreviousStatus, the
background color should color to blue.

Double click on the gray bar at the top of the appropriate
section.

Select the events tab in the property sheet.

Look down the list of event **properties** for the OnFormat
property.

Select [Event Procedure] from the drop list on the right
side of the property.

Click on the builder button [...] to the right of the drop
down button.

You should then be in the Format event **procedure** where
you can put the code.

Note that the code I posted made several assumptions that
may or may not be valid. E.g. that the color you want was
already set on the main report. If that was a poor
assumption and all you have is a value in the subreport
(similar to what you have in the form), then you can use
code like you have in the form.

If you want to change the color of a text box instead of the
section, then the code might be the same as the code in the
form.

Where do I find the Format event in Access 2003? Just guide me thru simple
steps to get all of this accomplished.


:
The subreport can get the information from main report by
using the Parent property.

For example, you could set the subreport detail section's
color by using this in the detail section's Format event
procedure:

Me.Section(0).BackColor = Parent.PreviousStatus.BackColor

Ben wrote:
I have a form that changes the color of the combo box to a color based on the
user's choice.

if [PreviousStatus] = "Blue" Then
[PreviousStatus].BackColor = 16711680

I have this information in a table. Separately, on a subreport I want the
subreport to show the right color for whatever the value is from that table
from [PreviousStatus] to be on the subreport too.

 

[Ben]

Marshall,
Here's what's going on. I have a table that has two fields PreviousStatus
and CurrentStatus. Each have a combo box on a form with values of Blue,
Green, Red.
The table is a child of the parent tblWAR.

When the user clicks on the drop-down combo box on the form and selects
either blue green or red, the background color of that text box changes to
that color and the value they selected is stored in the table. The code I
have on the form is:

if [PreviousStatus] = "Blue" Then
[PreviousStatus].BackColor = 16711680

Just an example of just the "Blue" selection.

Now, on the report, my Program Manager wants to see the values that were
selected but have the background of that value be that color also. So on the
report if the value is Blue, then the background behind that word is Blue.

This report is a subreport from the main report. The main report has two
date fields as the parent and these current and previous status values are a
subreport of that.
So, what I'm struggling with is getting the background color of those values
on the subreport to be present.

Does that make sense?

Ben

Ben

Since you did not post a Copy/Paste of the code you tried, I
can't tell what you did, much less what might be wrong.

OTOH, OnFormat is a ***property***, not an event or an event
procedure so you may be talking about something other than
what I was trying to suggest.

I also never noticed where you explained exactly where the
value of the PreviousStatus field is in the main or sub
report, so my suggested code could be off base.
--
Marsh
MVP [MS Access]

OK. I got to the onFormat event. I entered the code in but nothing is
working. It says there is something wrong with the code and to debug it.

On the form the color changes just fine. I want the color to be on the
report too. So if someone on the form clicks on Blue for PreviousStatus, the
background color should color to blue.

Double click on the gray bar at the top of the appropriate
section.

Select the events tab in the property sheet.

Look down the list of event **properties** for the OnFormat
property.

Select [Event Procedure] from the drop list on the right
side of the property.

Click on the builder button [...] to the right of the drop
down button.

You should then be in the Format event **procedure** where
you can put the code.

Note that the code I posted made several assumptions that
may or may not be valid. E.g. that the color you want was
already set on the main report. If that was a poor
assumption and all you have is a value in the subreport
(similar to what you have in the form), then you can use
code like you have in the form.

If you want to change the color of a text box instead of the
section, then the code might be the same as the code in the
form.


Ben wrote:
Where do I find the Format event in Access 2003? Just guide me thru simple
steps to get all of this accomplished.


:
The subreport can get the information from main report by
using the Parent property.

For example, you could set the subreport detail section's
color by using this in the detail section's Format event
procedure:

Me.Section(0).BackColor = Parent.PreviousStatus.BackColor

Ben wrote:
I have a form that changes the color of the combo box to a color based on the
user's choice.

if [PreviousStatus] = "Blue" Then
[PreviousStatus].BackColor = 16711680

I have this information in a table. Separately, on a subreport I want the
subreport to show the right color for whatever the value is from that table
from [PreviousStatus] to be on the subreport too.

 

[Marshall Barton]

Here's what's going on. I have a table that has two fields PreviousStatus
and CurrentStatus. Each have a combo box on a form with values of Blue,
Green, Red.
The table is a child of the parent tblWAR.

When the user clicks on the drop-down combo box on the form and selects
either blue green or red, the background color of that text box changes to
that color and the value they selected is stored in the table. The code I
have on the form is:

if [PreviousStatus] = "Blue" Then
[PreviousStatus].BackColor = 16711680

Just an example of just the "Blue" selection.

Now, on the report, my Program Manager wants to see the values that were
selected but have the background of that value be that color also. So on the
report if the value is Blue, then the background behind that word is Blue.

This report is a subreport from the main report. The main report has two
date fields as the parent and these current and previous status values are a
subreport of that.
So, what I'm struggling with is getting the background color of those values
on the subreport to be present.

That didn't really answer my questions, but I think I can
infer what you want. Let's try this code in the subreport's
detail section's Format event procedure:

Select Case Me.PreviousStatus
Case "Blue"
Me.PreviousStatus.BackColor = 16711680
Case "Green"
Me.PreviousStatus.BackColor = RGB(0,255,0)
Case "Red"
Me.PreviousStatus.BackColor = RGB(255,0,0)
Case Else
Me.PreviousStatus.BackColor = vbWhite
End Select

 

[Ben]

Marshall,
Entered the code and still no color on the report.

Ben

Here's what's going on. I have a table that has two fields PreviousStatus
and CurrentStatus. Each have a combo box on a form with values of Blue,
Green, Red.
The table is a child of the parent tblWAR.

When the user clicks on the drop-down combo box on the form and selects
either blue green or red, the background color of that text box changes to
that color and the value they selected is stored in the table. The code I
have on the form is:

if [PreviousStatus] = "Blue" Then
[PreviousStatus].BackColor = 16711680

Just an example of just the "Blue" selection.

Now, on the report, my Program Manager wants to see the values that were
selected but have the background of that value be that color also. So on the
report if the value is Blue, then the background behind that word is Blue.

This report is a subreport from the main report. The main report has two
date fields as the parent and these current and previous status values are a
subreport of that.
So, what I'm struggling with is getting the background color of those values
on the subreport to be present.

That didn't really answer my questions, but I think I can
infer what you want. Let's try this code in the subreport's
detail section's Format event procedure:

Select Case Me.PreviousStatus
Case "Blue"
Me.PreviousStatus.BackColor = 16711680
Case "Green"
Me.PreviousStatus.BackColor = RGB(0,255,0)
Case "Red"
Me.PreviousStatus.BackColor = RGB(255,0,0)
Case Else
Me.PreviousStatus.BackColor = vbWhite
End Select

 

[Marshall Barton]

Come on. How about providing some clues that can help me
understand what's going on.

I need to check the names of the text boxes you want to
color, what section of the subreport they are in, where you
put the code, and if there were any error messages, etc.
--
Marsh
MVP [MS Access]

Entered the code and still no color on the report.

Here's what's going on. I have a table that has two fields PreviousStatus
and CurrentStatus. Each have a combo box on a form with values of Blue,
Green, Red.
The table is a child of the parent tblWAR.

When the user clicks on the drop-down combo box on the form and selects
either blue green or red, the background color of that text box changes to
that color and the value they selected is stored in the table. The code I
have on the form is:

if [PreviousStatus] = "Blue" Then
[PreviousStatus].BackColor = 16711680

Just an example of just the "Blue" selection.

Now, on the report, my Program Manager wants to see the values that were
selected but have the background of that value be that color also. So on the
report if the value is Blue, then the background behind that word is Blue.

This report is a subreport from the main report. The main report has two
date fields as the parent and these current and previous status values are a
subreport of that.
So, what I'm struggling with is getting the background color of those values
on the subreport to be present.

That didn't really answer my questions, but I think I can
infer what you want. Let's try this code in the subreport's
detail section's Format event procedure:

Select Case Me.PreviousStatus
Case "Blue"
Me.PreviousStatus.BackColor = 16711680
Case "Green"
Me.PreviousStatus.BackColor = RGB(0,255,0)
Case "Red"
Me.PreviousStatus.BackColor = RGB(255,0,0)
Case Else
Me.PreviousStatus.BackColor = vbWhite
End Select

 

[Ben]

There is a form called Builds. The fields PreviousStatus and CurrentStatus
are combo boxes that have a list of values Blue, Green, Yellow, Red, Purple,
Gold, and N/A. When the user clicks on the drop down menu and selects one of
those values, the background of the combo box changes to that color. If N/A
is selected then it's white. Those values are stored in the table.

There is a main report called rptWAR that has a subreport based on the data
from the Builds table. I can get the data to publish on the subreport, but
the back color of each of these values does not show the color.

When I look at the design view of the subreport I see the combo boxes on the
report. I clicked on the Details section and went to Event, On Format. I
entered the code you provided me the Select Case statement and I don't see
any colors. I wish I could email you the database because it'll be easier
that way. My email is (e-mail address removed) if you need that.

Ben

Come on. How about providing some clues that can help me
understand what's going on.

I need to check the names of the text boxes you want to
color, what section of the subreport they are in, where you
put the code, and if there were any error messages, etc.
--
Marsh
MVP [MS Access]

Entered the code and still no color on the report.

Ben wrote:
Here's what's going on. I have a table that has two fields PreviousStatus
and CurrentStatus. Each have a combo box on a form with values of Blue,
Green, Red.
The table is a child of the parent tblWAR.

When the user clicks on the drop-down combo box on the form and selects
either blue green or red, the background color of that text box changes to
that color and the value they selected is stored in the table. The code I
have on the form is:

if [PreviousStatus] = "Blue" Then
[PreviousStatus].BackColor = 16711680

Just an example of just the "Blue" selection.

Now, on the report, my Program Manager wants to see the values that were
selected but have the background of that value be that color also. So on the
report if the value is Blue, then the background behind that word is Blue.

This report is a subreport from the main report. The main report has two
date fields as the parent and these current and previous status values are a
subreport of that.
So, what I'm struggling with is getting the background color of those values
on the subreport to be present.


That didn't really answer my questions, but I think I can
infer what you want. Let's try this code in the subreport's
detail section's Format event procedure:

Select Case Me.PreviousStatus
Case "Blue"
Me.PreviousStatus.BackColor = 16711680
Case "Green"
Me.PreviousStatus.BackColor = RGB(0,255,0)
Case "Red"
Me.PreviousStatus.BackColor = RGB(255,0,0)
Case Else
Me.PreviousStatus.BackColor = vbWhite
End Select

 

[Marshall Barton]

There is a form called Builds. The fields PreviousStatus and CurrentStatus
are combo boxes that have a list of values Blue, Green, Yellow, Red, Purple,
Gold, and N/A. When the user clicks on the drop down menu and selects one of
those values, the background of the combo box changes to that color. If N/A
is selected then it's white. Those values are stored in the table.

There is a main report called rptWAR that has a subreport based on the data
from the Builds table. I can get the data to publish on the subreport, but
the back color of each of these values does not show the color.

When I look at the design view of the subreport I see the combo boxes on the
report. I clicked on the Details section and went to Event, On Format. I
entered the code you provided me the Select Case statement and I don't see
any colors. I wish I could email you the database because it'll be easier
that way. My email is (e-mail address removed) if you need that.

The form is irrelevant at this point. It also sound like
the main report has no involvement so try opening the
subreport by itself. We only care about the subreport's
report's record source table/query and what's in the
subreport.

You're using a combo box on a report??? That would be very
odd if true. I'm beginning to think that your table field
is a Lookup Field (displayed as a drop list), which will
totally obscure what is really going on. If so, change it
to a text box and post back with what happened. I suspect
that the value in the field could be something other than
"Blue", "Red" or ...

 

[Ben]

Marshall,
I went to the subreport and changed the PreviousStatus to a textbox and ran
the code and still no color. The table where this information resides is a
combo box, but that shouldn't matter right? When I create a new report and
do the report wizard it takes the PreviousStatus field and places it on the
report and it's a Drop Down menu. I changed it to a textbox, but still no
luck. I see the words, but no color.

Next suggestion?

Ben

 

[Ben]

Marshall,
It seems as if the Me.PreviousStatus.BackColor is not a valid call in the
report on Format event. Is there a way I can code the conditional
formatting? I don't want to use the normal conditional formatting because i
have multiple conditions.

Just a thought. This little problem is being a pain.

Ben

 

[Marshall Barton]

I went to the subreport and changed the PreviousStatus to a textbox and ran
the code and still no color. The table where this information resides is a
combo box, but that shouldn't matter right? When I create a new report and
do the report wizard it takes the PreviousStatus field and places it on the
report and it's a Drop Down menu. I changed it to a textbox, but still no
luck. I see the words, but no color.

Lookup fields can really mess with your mind because what
you see is usually not what you get (depending on the lookup
up field's row source and row source type properties).

The wizards just assume that you want a combo box when the
table field is a lookup field, which can further propogates
the confusion.

Changing the report combo box to a text box will display the
table field's real value so you can see what's really in the
table field.

If the lookup field's row source type is a simple single
column Value List, then the text box should display the same
value as the combo box. It sounds like this is what you
have so it seems like the lookup field is not causing the
problem. Unfortunately, the only other reason I can think
of is that the code you are using is not operating on the
correct text box. It's possible that the text box's name is
not the same as the table field's name. Double check the
text box's Name property and make sure you are using it in
the code.

Another possibility is that you put the code somewhere other
than the Format/Print event of the section containing the
text box.

 

[Marshall Barton]

It seems as if the Me.PreviousStatus.BackColor is not a valid call in the
report on Format event.

If PreviousStatus is the name of a text box, then it is
valid. We need to figure out why Access thinks it's not
valid.

Are you getting an error message?

Does the report's module compile (Debug menu) cleanly?

 

[Ben]

Marshall,
I got it to work. I created a report in Design Mode not with the wizard. I
went to the record source line and put the PreviousStatus field in that.
Then went to the Event onFormat and put the Case code in and it worked.

I really appreciate you working with me.

Ben

 

[Mac Moull]

Guys - I think I might have solved your problem. I too was having trouble
getting the code to work, until I realised the Back Style for the field was
set to 'Transparent'. as soon as I reset it to 'Normal' it worked !!

Hope I've helped you, because your thread certainly helped me in the first
place.