Color coding reports/labels

I have a requirement to color code labels and reports based on criteria
contained in the report data. The database is for VHS and DVD movies and the
requirement calls for the printed reports and labels to have the media number
color coded based on the genre. For example, Horror would be Backcolor=BLACK,
Forecolor=RED, etc. The color coding would have to occur whether or not
grouping was used. I have expaned the genres table to include columns for
Back and Fore colors. Any thoughts or advice how to implement this in code
for the reports/labels; which module to include the code in? Thanks.

Okay, so you have a Genre table with fields such as:
GenreID AutoNumber
Genre Text
BackColorVal Number (Long Integer)
ForeColorVal Number (Long Integer)
as well as your main table, which has a GenreID foreign key.

It that is so, you can build a query that uses the Genre table as well as
your main table, so the BackColorVal and ForeColorVal fields are part of the
report's RecordSource.

You can now use the Format event of the Detail section of the report to set
the ForeColor and BackColor of the text boxes.

(Conditional Formatting will not be suitable for your needs, as you will
have more than 3 genres.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"EyeTech" wrote in message
...
I have a requirement to color code labels and reports based on criteria
contained in the report data. The database is for VHS and DVD movies and
the
requirement calls for the printed reports and labels to have the media
number
color coded based on the genre. For example, Horror would be
Backcolor=BLACK,
Forecolor=RED, etc. The color coding would have to occur whether or not
grouping was used. I have expaned the genres table to include columns for
Back and Fore colors. Any thoughts or advice how to implement this in code
for the reports/labels; which module to include the code in? Thanks.

Alen,

Thanks; that's pretty much exactly what my table looks like. Being that I
haven't been into the meat and potatoes of reports yet, I wasn't exactly sure
where I could put code for this, but you have certainly confirmed my hunch.

Thanks again.
Don


"Allen Browne" wrote:

Okay, so you have a Genre table with fields such as:
GenreID AutoNumber
Genre Text
BackColorVal Number (Long Integer)
ForeColorVal Number (Long Integer)
as well as your main table, which has a GenreID foreign key.

It that is so, you can build a query that uses the Genre table as well as
your main table, so the BackColorVal and ForeColorVal fields are part of the
report's RecordSource.

You can now use the Format event of the Detail section of the report to set
the ForeColor and BackColor of the text boxes.

(Conditional Formatting will not be suitable for your needs, as you will
have more than 3 genres.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"EyeTech" wrote in message
...
I have a requirement to color code labels and reports based on criteria
contained in the report data. The database is for VHS and DVD movies and
the
requirement calls for the printed reports and labels to have the media
number
color coded based on the genre. For example, Horror would be
Backcolor=BLACK,
Forecolor=RED, etc. The color coding would have to occur whether or not
grouping was used. I have expaned the genres table to include columns for
Back and Fore colors. Any thoughts or advice how to implement this in code
for the reports/labels; which module to include the code in? Thanks.

Well, I thought this would be 'on track' but, unfortunately, the backcolor
and forecolor properties are not available when writing code for reports.

Specifically, the media number text box (filled from another table) must be
color coded. But when I try to access the properies in code, they are not
available. They are available in designer.

Any further thoughts appreciated.

Don



"Allen Browne" wrote:

Okay, so you have a Genre table with fields such as:
GenreID AutoNumber
Genre Text
BackColorVal Number (Long Integer)
ForeColorVal Number (Long Integer)
as well as your main table, which has a GenreID foreign key.

It that is so, you can build a query that uses the Genre table as well as
your main table, so the BackColorVal and ForeColorVal fields are part of the
report's RecordSource.

You can now use the Format event of the Detail section of the report to set
the ForeColor and BackColor of the text boxes.

(Conditional Formatting will not be suitable for your needs, as you will
have more than 3 genres.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"EyeTech" wrote in message
...
I have a requirement to color code labels and reports based on criteria
contained in the report data. The database is for VHS and DVD movies and
the
requirement calls for the printed reports and labels to have the media
number
color coded based on the genre. For example, Horror would be
Backcolor=BLACK,
Forecolor=RED, etc. The color coding would have to occur whether or not
grouping was used. I have expaned the genres table to include columns for
Back and Fore colors. Any thoughts or advice how to implement this in code
for the reports/labels; which module to include the code in? Thanks.

On Jul 14, 10:20 am, EyeTech
wrote:
Well, I thought this would be 'on track' but, unfortunately, the backcolor
and forecolor properties are not available when writing code for reports.

Specifically, the media number text box (filled from another table) must be
color coded. But when I try to access the properies in code, they are not
available. They are available in designer.

Any further thoughts appreciated.

Don

Hmm. Did you try:
ctrlName.ForeColor = rgb(255, 255, 255)
and end up w/ an error message?

I suppose you could try:
ctrlName.Properties("ForeColor") = blah

-Kris

Yes. Any attempt to access the property and there is no InelliSense for it
and if you just type it and run, you will get an invalid property error.
Seems strange to me but I'm willing to hear all ideas!

Thanks,
Don


"krissco" wrote:

On Jul 14, 10:20 am, EyeTech
wrote:
Well, I thought this would be 'on track' but, unfortunately, the backcolor
and forecolor properties are not available when writing code for reports.

Specifically, the media number text box (filled from another table) must be
color coded. But when I try to access the properies in code, they are not
available. They are available in designer.

Any further thoughts appreciated.

Don

Hmm. Did you try:
ctrlName.ForeColor = rgb(255, 255, 255)
and end up w/ an error message?

I suppose you could try:
ctrlName.Properties("ForeColor") = blah

-Kris

Although the IntelliSense doesn't understand the property, it does work,
e.g.:
Me.MyField.ForeColor = &H777777

Note that you must use the Format event of the section that contains this
control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"EyeTech" wrote in message
...
Yes. Any attempt to access the property and there is no InelliSense for it
and if you just type it and run, you will get an invalid property error.
Seems strange to me but I'm willing to hear all ideas!

Thanks,
Don


"krissco" wrote:

On Jul 14, 10:20 am, EyeTech
wrote:
Well, I thought this would be 'on track' but, unfortunately, the
backcolor
and forecolor properties are not available when writing code for
reports.

Specifically, the media number text box (filled from another table)
must be
color coded. But when I try to access the properies in code, they are
not
available. They are available in designer.

Any further thoughts appreciated.

Don

Hmm. Did you try:
ctrlName.ForeColor = rgb(255, 255, 255)
and end up w/ an error message?

I suppose you could try:
ctrlName.Properties("ForeColor") = blah

-Kris

Thanks Allen, I'll give it a try.

Don


"Allen Browne" wrote:

Although the IntelliSense doesn't understand the property, it does work,
e.g.:
Me.MyField.ForeColor = &H777777

Note that you must use the Format event of the section that contains this
control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"EyeTech" wrote in message
...
Yes. Any attempt to access the property and there is no InelliSense for it
and if you just type it and run, you will get an invalid property error.
Seems strange to me but I'm willing to hear all ideas!

Thanks,
Don


"krissco" wrote:

On Jul 14, 10:20 am, EyeTech
wrote:
Well, I thought this would be 'on track' but, unfortunately, the
backcolor
and forecolor properties are not available when writing code for
reports.

Specifically, the media number text box (filled from another table)
must be
color coded. But when I try to access the properies in code, they are
not
available. They are available in designer.

Any further thoughts appreciated.

Don

Hmm. Did you try:
ctrlName.ForeColor = rgb(255, 255, 255)
and end up w/ an error message?

I suppose you could try:
ctrlName.Properties("ForeColor") = blah

-Kris

Thanks, Allen. Success! However, I found I had to:

Include my genre table in my querie and select the BackColorCode and
ForeColorCode fields on the label/report with their visible properties set to
NO. I could then use the code:

Me.Media_no.ForeColor=Me.ForeColorCode
Me.Media_no.BackColor=Me.BackColorCode

Works the way I want it to. Thanks again! Hopefully this will be helpful to
others down the road.

Don


"Allen Browne" wrote:

Although the IntelliSense doesn't understand the property, it does work,
e.g.:
Me.MyField.ForeColor = &H777777

Note that you must use the Format event of the section that contains this
control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"EyeTech" wrote in message
...
Yes. Any attempt to access the property and there is no InelliSense for it
and if you just type it and run, you will get an invalid property error.
Seems strange to me but I'm willing to hear all ideas!

Thanks,
Don


"krissco" wrote:

On Jul 14, 10:20 am, EyeTech
wrote:
Well, I thought this would be 'on track' but, unfortunately, the
backcolor
and forecolor properties are not available when writing code for
reports.

Specifically, the media number text box (filled from another table)
must be
color coded. But when I try to access the properies in code, they are
not
available. They are available in designer.

Any further thoughts appreciated.

Don

Hmm. Did you try:
ctrlName.ForeColor = rgb(255, 255, 255)
and end up w/ an error message?

I suppose you could try:
ctrlName.Properties("ForeColor") = blah

-Kris

That's great, Don.

It seems that Access does not always fetch all the fields specified in the
report's RecordSource (depends on sorting/grouping and when you added the
field to the query or control to the report), i.e. it's optimization is
trying to be too clever. When this happens, the workaround is to add hidden
controls to the report, so it does fetch the data.

Hence your solution makes good sense. Well done.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"EyeTech" wrote in message
...
Thanks, Allen. Success! However, I found I had to:

Include my genre table in my querie and select the BackColorCode and
ForeColorCode fields on the label/report with their visible properties set
to
NO. I could then use the code:

Me.Media_no.ForeColor=Me.ForeColorCode
Me.Media_no.BackColor=Me.BackColorCode

Works the way I want it to. Thanks again! Hopefully this will be helpful
to
others down the road.

Don


"Allen Browne" wrote:

Although the IntelliSense doesn't understand the property, it does work,
e.g.:
Me.MyField.ForeColor = &H777777

Note that you must use the Format event of the section that contains this
control.

"EyeTech" wrote in message
...
Yes. Any attempt to access the property and there is no InelliSense for
it
and if you just type it and run, you will get an invalid property
error.
Seems strange to me but I'm willing to hear all ideas!

Thanks,
Don


"krissco" wrote:

On Jul 14, 10:20 am, EyeTech
wrote:
Well, I thought this would be 'on track' but, unfortunately, the
backcolor
and forecolor properties are not available when writing code for
reports.

Specifically, the media number text box (filled from another table)
must be
color coded. But when I try to access the properies in code, they
are
not
available. They are available in designer.

Any further thoughts appreciated.

Don

Hmm. Did you try:
ctrlName.ForeColor = rgb(255, 255, 255)
and end up w/ an error message?

I suppose you could try:
ctrlName.Properties("ForeColor") = blah

-Kris