Same BackColor On Detail & Footers - Optimize VB Code 1

[EdAROC]

The BackColor is based on a criteria (certain customers or the Sales Agent) in the Detail and ReportFooter sections. I first got it to work in the detail and then copied and edited the code in the ReportFooter section.

However - when criteria changes or the colors being used are changed then we must edit both sections of the code.

It's late and I'm tired ... I'm sure there's a way to put the color "selecting" code in one place (like in ReportHeader - hence executes once) and then assign the resulting color value in the appropriate section, like: strBackColor is a public variable and the color is assigned to it. Then in each section (detail and footer) the color is assigned, i.e. Detail.BackColor = strBackColor

Here is the current code in the Detail section, ReportFooter is same except it uses ReportFooter.BackColor:
* CSCODE is Customer Code
* SACODE is Sales Agent Code

'Background Colors
'Customers have precedence: ABC (138), XYZ (702)

Select Case Me.txtCSCODE
Case "138" 'ABC
Detail.BackColor = RGB(204, 255, 153)
Case "702" 'XYZ
Detail.BackColor = RGB(255, 255, 128)
Case Else
Select Case Me.txtSACODE '[Sales Agent Code]
Case "006" 'Andrew
Detail.BackColor = RGB(214, 92, 173)
Case "44" 'Chuck
Detail.BackColor = RGB(255, 153, 0)
Case "26" 'Debbie
Detail.BackColor = RGB(255, 221, 204)
Case "008" 'Evert
Detail.BackColor = RGB(153, 153, 153)
Case "020" 'House
Detail.BackColor = RGB(255, 255, 255)
Case "53" 'Vince
Detail.BackColor = RGB(204, 255, 255)
Case Else 'Not Assigned
Detail.BackColor = RGB(102, 102, 51)
End Select
End Select

I hope this is clear to you. (-:

 

[dhookom]

I would store the color values in the table of customers and sales agents. Then your code will stay the same. To change colors, change data (not code).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[EdAROC]

Ahhh! Good one. I told you I was tired, at the end of the day. Yes! Since the database is an ERP all I have to do is create an "Agents" table in this file for the colors - and any other "data" that may come along as people think of enhancements. (I will check to see if the ERP has user defined fields in their table first).

Now that I've slept on it, and my mind is fresher, I realize all I need to do is create a "PickColor" function with the public variable and have an assign statement the puts the value into the .BackColor variable.

Thanks for the tip.