I found some basic code and * attempted* to modify it in order to set the background color of a form contingent upon the entry in the "Employing Unit" field (which happens to be a combobox). The only "rule" it's applying is to change the background to orange (33023) when the Employing Unit is "Orange." Otherwise, it's setting everything else as gray (effectively ignoring the other conditions).

I would appreciate any help given. Thanks.


Private Sub Form_Current()
If Me.EmployingUnit = "Pink" Then
Detail.BackColor = 6697881
End If
If Me.EmployingUnit = "Green" Then
Detail.BackColor = 32768
End If
If Me.EmployingUnit = "Blue" Then
Detail.BackColor = 12615680
End If
If Me.EmployingUnit = "Red" Then
Detail.BackColor = 255
End If
If Me.EmployingUnit = "Yellow" Then
Detail.BackColor = 65535
End If
If Me.EmployingUnit = "Orange" Then
Detail.BackColor = 33023
Else
Detail.BackColor = 9868950
End If
End Sub

Hi,

Welcome to Utter Access!

Just a guess but you might have spaces around the data in your table. Try modifying your code to something like this to see if it makes a difference:

If Trim(Me.EmployingUnit) = "Pink" Then
...

Hope that helps...

I don't have spaces around my data (one word only), but I tried it anyway.

No dice. Any other suggestions?

try putting the me keyword in front of detail

Your logic is flawed. Your code evaluates every IF statement so always evaluates the Orange condition last.

You need to use an ElseIf construct namely:

Private Sub Form_Current()
If Me.EmployingUnit = "Pink" Then
Detail.BackColor = 6697881
ElseIf Me.EmployingUnit = "Green" Then
Detail.BackColor = 32768
ElseIf Me.EmployingUnit = "Blue" Then
Detail.BackColor = 12615680
ElseIf Me.EmployingUnit = "Red" Then
Detail.BackColor = 255
ElseIf Me.EmployingUnit = "Yellow" Then
Detail.BackColor = 65535
ElseIf Me.EmployingUnit = "Orange" Then
Detail.BackColor = 33023
Else
Detail.BackColor = 9868950
End If
End Sub

PERFECT!

Thanks, everyone.

Nice work, SteveH2508!

I would probably recommend using the SELECT CASE statement in this case:

Select Case Me.EmployingUnit
Case "Pink"
Me.Detail.BackColor = 6697881
Case "Green"
Me.Detail.BackColor = 32768
Case "Blue"
Me.Detail.BackColor = 12615680
Case "Red"
Me.Detail.BackColor = 255
Case "Yellow"
Me.Detail.BackColor = 65535
Case "Orange"
Me.Detail.BackColor = 33023
Case Else
Me.Detail.BackColor = 9868950
End Select

Just my 2 cents...

You may also like to try putting the numerical colour equivalent in an extra field against each colour tag and set the detail back colour to the appropriate column of the combo box.

Regards,
Chris.

The Elseif and case select methods both work well in single form but don't seem to work in the oncurrent event for a data sheet, though Normal conditional formatting does. Is this an access restriction? if so is there a work around?

Regards

Llew