Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

more than 4 colors

Status
Not open for further replies.

rperre

Programmer
Jan 25, 2005
39
US
Hello,

I have created a database with scheduling rosters.
The basic idea is to have 4 teams rotate a "call" schedule with the monday team also taking the next weekend schedule (don't ask, they want it like that). I made a rule table based on all the "rules" they have for call rotation.

When I make a report I get all the data nicely in a calendar shape. I want to make the different teams on this report clearer and came up with the idea of giving them a certain color, this way they can see easily when they are "on call". I have used the conditional formatting for this, but I can only use 3 conditions, the default doesn't work right for this purpose and is really unusable.

Is there any way to make more conditions to format more colors? Especially handy if a 5th team is ever created!

Thanks,

Richard
 
rperre
In the Format event for the Detail section, can you set the ForeColor and BackColor by using code?

If Me.Team = "WhiteSox" Then
Me.Team.BackColor = vbRed
ElseIf Me.Team = "Astros" Then
Me.Team.BackColor = vbGreen
etc.
End If

Tom
 
Hello, I set the format part up like this, but because of the lengthy search strings it takes the report a full 19 seconds to calculate the format on me computer (i can't imagine having a P2 do this). Is there a quicker way of doing this?

Thank you,

Richard

Here is the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

SetColors (vbWhite)
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!Mo & "'") = "1" Then
Me.Mo.BackColor = vbBlue
Me.Fr.BackColor = vbBlue
Me.Sa1.BackColor = vbBlue
Me.Sa2.BackColor = vbBlue
Me.Su1.BackColor = vbBlue
Me.Su2.BackColor = vbBlue
End If
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!Mo & "'") = "2" Then
Me.Mo.BackColor = vbGreen
Me.Fr.BackColor = vbGreen
Me.Sa1.BackColor = vbGreen
Me.Sa2.BackColor = vbGreen
Me.Su1.BackColor = vbGreen
Me.Su2.BackColor = vbGreen
End If
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!Mo & "'") = "3" Then
Me.Mo.BackColor = vbRed
Me.Fr.BackColor = vbRed
Me.Sa1.BackColor = vbRed
Me.Sa2.BackColor = vbRed
Me.Su1.BackColor = vbRed
Me.Su2.BackColor = vbRed
End If
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!Mo & "'") = "4" Then
Me.Mo.BackColor = vbYellow
Me.Fr.BackColor = vbYellow
Me.Sa1.BackColor = vbYellow
Me.Sa2.BackColor = vbYellow
Me.Su1.BackColor = vbYellow
Me.Su2.BackColor = vbYellow
End If
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!Tu & "'") = "1" Then Me.Tu.BackColor = vbBlue
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!Tu & "'") = "2" Then Me.Tu.BackColor = vbGreen
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!Tu & "'") = "3" Then Me.Tu.BackColor = vbRed
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!Tu & "'") = "4" Then Me.Tu.BackColor = vbYellow
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!We & "'") = "1" Then Me.We.BackColor = vbBlue
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!We & "'") = "2" Then Me.We.BackColor = vbGreen
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!We & "'") = "3" Then Me.We.BackColor = vbRed
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!We & "'") = "4" Then Me.We.BackColor = vbYellow
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!Th & "'") = "1" Then Me.Th.BackColor = vbBlue
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!Th & "'") = "2" Then Me.Th.BackColor = vbGreen
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!Th & "'") = "3" Then Me.Th.BackColor = vbRed
If DLookup("[Team]", "EmployeeLetters", "[LastName] = '" & Me!Th & "'") = "4" Then Me.Th.BackColor = vbYellow
End Sub

Public Sub SetColors(colorvb)
Me.Mo.BackColor = colorvb
Me.Tu.BackColor = colorvb
Me.We.BackColor = colorvb
Me.Th.BackColor = colorvb
Me.Fr.BackColor = colorvb
Me.Sa1.BackColor = colorvb
Me.Sa2.BackColor = colorvb
Me.Su1.BackColor = colorvb
Me.Su2.BackColor = colorvb
End Sub
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top