NEW - Change colors on one field based on another

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

I have 5 Phase fields called: Req, Design, Development, Transition, Pilot
For each "Phase" the user is to select the status of that phase "Phase
Completed", "On Schedule", "In Progress", "Off Schedule", and "Severe Issues"

I have those done and working.
However, on the report I want to have the background color of the Phases
change color based on the "Status".
Phase Completed - Purple (8388736)
On Schedule - Blue (16777680)
In Progress - Green (65280)
Off Schedule - Yellow (66635)
Severe Issues - Red (255)

I know this needs to be coded in the On Format part of the report, but not
sure how to get the backgrounds to change based on those conditions for each
phase.

Thanks
 
Ben said:
I have 5 Phase fields called: Req, Design, Development, Transition, Pilot
For each "Phase" the user is to select the status of that phase "Phase
Completed", "On Schedule", "In Progress", "Off Schedule", and "Severe Issues"

I have those done and working.
However, on the report I want to have the background color of the Phases
change color based on the "Status".
Phase Completed - Purple (8388736)
On Schedule - Blue (16777680)
In Progress - Green (65280)
Off Schedule - Yellow (66635)
Severe Issues - Red (255)

I know this needs to be coded in the On Format part of the report, but not
sure how to get the backgrounds to change based on those conditions for each
phase.
Click to expand...


A quick and dirty approach could be the code you asked for:

Private Function SetBackColor(status As Textbox) As Long
SELECT Case status.Value
Case "Phase Completed" ' Purple
status.BackColor = 8388736
Case "On Schedule" ' Blue
status.BackColor = 16777680
. . .
End Select

Then call the function 5 times in the section's Format event
procedure:

SetBackColor Me.Phase1
SetBackColor Me.Phase2
. . .

A far more flexible approach would be to create a Statuses
table that has fields for the status and its color value.
Then the report's record source query can join that table
and include each color value as a field in the query and a
corresponding hidden text box in the report. Then the code
would simply be:
Me.Phase1.BackColor = Me.color1
Me.Phase2.BackColor = Me.color2
. . .

This way, you would not have to change any code if you
should ever have to change a status;s text or color value.
All you would have to do is edit the data in the table.
 
Back
Top