If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Color coding reports/labels
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. |
#3
|
|||
|
|||
Color coding reports/labels
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. |
#4
|
|||
|
|||
Color coding reports/labels
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. |
#5
|
|||
|
|||
Color coding reports/labels
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 |
#6
|
|||
|
|||
Color coding reports/labels
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 |
#7
|
|||
|
|||
Color coding reports/labels
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 |
#8
|
|||
|
|||
Color coding reports/labels
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 |
#9
|
|||
|
|||
Color coding reports/labels
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 |
#10
|
|||
|
|||
Color coding reports/labels
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 |
Thread Tools | |
Display Modes | |
|
|