Conditional formatting, changing a memo contingent on a combo

[BUmed]

Hi all
I’m trying to figure this out. But help is needed. I have a combo box called fcmFLAG with 5 options (problem, hold off, call, withdrew and ineligible). What I would like to do is have the background in a memo box (fcmFcom) change to a different color for each option. I don’t think this can be done with Conditional formatting because it’s more then 3. The problem is that I just started getting into VBA but I have been using Access for over a year. So any help with coding this would be of great help.

[missinglinq]

To change the memo field's back color

Private Sub fcmFLAG_AfterUpdate()
  Select Case fcmFLAG
    Case "Problem"
      fcmFcom.BackColor = vbRed
    Case "Call"
      fcmFcom.BackColor = vbGreen
    Case "Hold Off"
      fcmFcom.BackColor = vbBlue
    Case "Withdrawn"
      fcmFcom.BackColor = vbYellow
    Case "Ineligible"
      fcmFcom.BackColor = vbCyan
    Case Else
      fcmFcom.BackColor = vbWhite
   End Select
End Sub
 

So that the color formatting is maintained moving record to record

Private Sub Form_Current()
  Select Case fcmFLAG
    Case "Problem"
      fcmFcom.BackColor = vbRed
    Case "Call"
      fcmFcom.BackColor = vbGreen
    Case "Hold Off"
      fcmFcom.BackColor = vbBlue
    Case "Withdrawn"
      fcmFcom.BackColor = vbYellow
    Case "Ineligible"
      fcmFcom.BackColor = vbCyan
    Case Else
      fcmFcom.BackColor = vbWhite
   End Select
End Sub
 

Of course, the combo box has to have it's value bound to a field in the table for the formatting to hold.

Welcome to TheScripts!

Linq ;0)>

[NeoPa]

Don't listen to him. He's no good.

Try Private Sub fcmFLAG_AfterUpdate()
  Call setColour()
End Sub
 
Private Sub Form_Current()
  Call setColour()
End Sub
 
Private Sub SetColour()
  Select Case fcmFLAG
    Case "Problem"
      fcmFcom.BackColor = vbRed
    Case "Call"
      fcmFcom.BackColor = vbGreen
    Case "Hold Off"
      fcmFcom.BackColor = vbBlue
    Case "Withdrawn"
      fcmFcom.BackColor = vbYellow
    Case "Ineligible"
      fcmFcom.BackColor = vbCyan
    Case Else
      fcmFcom.BackColor = vbWhite
   End Select
End Sub

Of course I nicked all the actual code from Linq :D

[BUmed]

You guys rock, this worked like a charm. On a side note, since I'm just getting started with VBA can you recommend any good sources to study (websites, books tutorials).
Thanks
Steve

[NeoPa]

It's all Linq's work really of course. I just added the format of how to reuse code in multiple events so that it's :

1. Clearly the same.
2. Maintainable in a single place.

I can give you a link to an index of our Articles section (Full List of Articles and Code in this Section). This may not have what you're looking for but I'm afraid I don't have any links to learning tutorials.

[missinglinq]

Fess up, NeoPa! You're just trying to sow confusion in a newbie so they'll have to come back!

Linq ;0)>

[NeoPa]

IT'S ALL TRUE!! <crying with shame>
I confess :(