How to apply different backcolors to a field

[Mary]

I'm trying to create a form that will change the backcolor on one field based
on the value of a different field. The code below colors all the M fields
green, even though the TType value is different. Any help would be greatly
appreciated. I'm working in Access2007.

Private Sub Form_Load()
If Me.TType = "Enrolled" Then
Me.M.BackColor = vbGreen
ElseIf Me.TType = "Quality" Then
Me.M.BackColor = vbRed
ElseIf Me.TType = "Performance" Then
Me.M.BackColor = vbYellow
ElseIf Me.TType = "Safety" Then
Me.M.BackColor = vbBlue
End If

End Sub

 

[John W. Vinson]

I'm trying to create a form that will change the backcolor on one field based
on the value of a different field. The code below colors all the M fields
green, even though the TType value is different. Any help would be greatly
appreciated. I'm working in Access2007.

Private Sub Form_Load()
If Me.TType = "Enrolled" Then
Me.M.BackColor = vbGreen
ElseIf Me.TType = "Quality" Then
Me.M.BackColor = vbRed
ElseIf Me.TType = "Performance" Then
Me.M.BackColor = vbYellow
ElseIf Me.TType = "Safety" Then
Me.M.BackColor = vbBlue
End If

End Sub

It would be much simpler to select the control M and choose Format...
Conditional Formatting from the menu. No code needed at all.

 

[John Spencer]

Actually, conditional format gives you three ADDITIONAL choices. Along with
the default setup, that gives you four choices total.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Except conditional formatting only gives you three choices and they are
already showing 4 possible choices.

I'm trying to create a form that will change the backcolor on one field based
on the value of a different field. The code below colors all the M fields

[quoted text clipped - 13 lines]

End Sub

It would be much simpler to select the control M and choose Format...
Conditional Formatting from the menu. No code needed at all.

 

[Tom]

If you are dealing with a continious form you have to use conditional
formating for this and, as indicated by others, you are limited to a
baseline plus three additional conditions. Its easy to set up
conditional formatting from the menu in design mode, but if you want
to put it in code, use the following in the Forms Load event (assumes
you name your Me.TType field txtTType):

With Me.M
.FormatConditions.Delete
.BackColor = vbGreen
.FormatConditions.Add acExpression, acEqual, "txtTType='Quality'"
.FormatConditions(0).BackColor = vbRed
.FormatConditions.Add acExpression, acEqual,
"txtTType='Performance'"
.FormatConditions(1).BackColor = vbYellow
.FormatConditions.Add acExpression, acEqual, "txtTType='Safety'"
.FormatConditions(2).BackColor = vbBlue
End With

Tom

 

[Mary]

I tried your solution, but I still get the first backcolor for all the M
Fields.
I will keep looking.
Thank you

 

[Mary]

Thanks Tom, this solution does work. The form that they want the back color
on is using only 4 of the 5 values. My concern is, they may change their
minds and want the 5th value included in this form. Is there another way to
change backcolor, without using the FormatConditions when there are more then
4 values?

 

[Tom]

In a continious form with A2003 or earlier - not that I am aware of.
Its a hard limit of 3 plus default. If you are using A2007, the
limits might be different - you'd have to check.

Good luck...

Tom