Coloring in the report

[bpifer23]

What I need is for the whole line in my report to be a color if one of the fields in my report has a certain peice of data. Example: Sales Rep is Dave......then the line in the report that has Dave needs to be blue. I was thinking of conditional formatting but that seems to be a rather uneffective way to accomplish this.

Any help would be great.
Thanks
Brian

 

[scriverb]

You can accomplish this by setting the .backstyle property to "Transparent" of all the controls on the detail section line. Then create a Box control the entire width of your detail line. Send this control to the back of all of your other controls. This is accomplished by selecting the Box control and selecting from the Format menu, Send to the Back.

Now in the Detail Sections Format Event procedure use the following code as an example:

Select Case Me.SalesRep
   Case "Dave"
      Me.Box1.BackColor = 8421631
   Case "Bob"
      Me.Box1.BackColor = 4259584
   Case "Lisa"
      Me.Box1.BackColor = 16744576
   Case Else
      Me.Box1.BackColor = 16777215
End Select

Now each line will have its own color. If the control Me.SalesRep doesn't match one of the names the color for the line will be white. Just change the numeric color representations to the colors that you want for each sales rep.

Post back with any questions or problems.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[dhookom]

I like Bob's suggestion. You might also consider adding a "RepColor" field in your sales rep table. You can then use data rather than hard-coding colors into your code.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[scriverb]

bpifer23: dhookom's suggestion to have a table with the rep's colors is a good one. Just create a new table with two fields. RepName and ColorCode. Enter the Reps name as you would find it in your main table field Rep Name. Then enter the numeric color representation. Now create a saved query with a left join between the tables Rep Name fields. Your report would use this saved query as the Record Source rather than the table. This will bring in the appropriate color. Create a control in your Detail second for the ColorCode field and make it small and invisible by setting the Visible property to False. You can also send it to the back of the other controls. Now the code in the OnFormat of the report would look like this:

Me.Box1.BackColor = IIF(Not IsNull(Me![ColorCode]), Me![ColorCode], 16777215)

dhookom, great idea.

Post back if you have further questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???