Form ComboBx Conditional Formatting Current Record Only

[DevilsSlide]

I want to use Conditional Formatting to visually alert users (Red, Yellow and Green).

I have a ComboBox on a Form that I want to display as 'Continuous Forms' that I want to apply Conditional Formatting. I understand that I can use Conditional Formatting from the Format Menu. But the ComboBox has several Text Values that I want to set Conditions for {Like: If tbl2Value = "tbl2V1" Or tbl2Value = "tbl2V2" Then BackColor = vbRed - See Sample Below}. The problem is when I use the AfterUpdate Event it applies the BackColor to the same color for all records regardless of value. I'd like it to apply to only the current record.

I think I could live with (the 1-default and 3-Optional Conditions) using the Conditional Formatting from the Format Menu if I can enter more than one text value per condition {Like: If Field Value is = "tbl2V4" or "tbl2V5" or "tbl2V6 or "tbl2V9" then FillColor = Yellow}. I've tried several combinations, but every one I've tried none seem to fit the condition so it rolls to the Default Format. I'm sure it can be done because on the same form I have another combo box with only 3 Text Values and can specify the format I need and it applies to only the current record.

I have the same problem when I try to hide objects of the form based on a check box of the current record. It Hides ALL or NONE - Not what I want.

Any Ideas or Suggestions?
Thanks
John

Current Code Snips:
Private Sub tbl2Value_AfterUpdate()
Dim lRed As Long, lYellow As Long, lGreen As Long
Dim lBlue As Long, lCyan As Long, lMagenta As Long

lRed = RGB(255, 0, 0)
lYellow = RGB(255, 255, 0)
lGreen = RGB(0, 255, 0)
lBlue = RGB(0, 0, 255)
lCyan = RGB(0, 255, 255)
lMagenta = RGB(255, 0, 255)

If tbl2Value = "tbl2V1" Or tbl2Value = "tbl2V2" Then
Me.tbl2Value.BackColor = lRed
ElseIf tbl2Value = "tbl2V3" Then
Me.tbl2Value.BackColor = lYellow
ElseIf tbl2Value = "tbl2V4" Then
Me.tbl2Value.BackColor = lGreen
ElseIf tbl2Value = "tbl2V5" Or tbl2Value = "tbl2V6" Then
Me.tbl2Value.BackColor = lBlue
ElseIf tbl2Value = "tbl2V7" Or tbl2Value = "tbl2V8" Or tbl2Value = "tbl2V9" Then
Me.tbl2Value.BackColor = lCyan
Else
Me.tbl2Value.BackColor = lMagenta
End If

End Sub

Private Sub tbl3Active_AfterUpdate()
If tbl3Active = -1 Then
Me.tbl2Value.Visible = True
Me.tbl4Note.Visible = True
Else
Me.tbl2Value.Visible = False
Me.tbl4Note.Visible = False
End If
End Sub

 

[SBendBuckeye]

It looks like you are pulling data from your table for your comparisons, not anything the user is doing. If that is the case, try converting your record source to a query and include a calclated field called MyColor. In the zoom box it would look something like this:
MyColor: =GetColor(tbl2Value)

You can reference a function from a query as long as it is in a standard module. Maybe something like your code below:

Private Function GetColor(tbl2Value As String) As Long
Dim lRed As Long, lYellow As Long, lGreen As Long
Dim lBlue As Long, lCyan As Long, lMagenta As Long

lRed = RGB(255, 0, 0)
lYellow = RGB(255, 255, 0)
lGreen = RGB(0, 255, 0)
lBlue = RGB(0, 0, 255)
lCyan = RGB(0, 255, 255)
lMagenta = RGB(255, 0, 255)

Select Case tbl2Value
Case "tbl2V1", "tbl2V2"
GetColor = lRed
Case "tbl2V3"
GetColor = lYellow
Case "tbl2V4"
GetColor = lGreen
Case "tbl2V5", "tbl2V6"
GetColor = lBlue
Case "tbl2V7", "tbl2V8", "tbl2V9"
GetColor = lCyan
Case Else
GetColor = lMagenta
End Select

End Function

Then your form controls would be Me.ControlName.BackColor = MyColor

Good LucK!