Color coding reports/labels

[EyeTech]

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.

 

[Allen Browne]

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.)

 

[EyeTech]

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

 

[EyeTech]

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

 

[krissco]

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

 

[EyeTech]

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

 

[Allen Browne]

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]

Thanks Allen, I'll give it a try.

Don

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]

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]

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.