Combo Box Formatting with VB

[Markus]

When I use the Conditional Format from the Format Menu on
the field value on my combo box the conditions hold true
when I scroll between records on the form. Unfortunately
this limits you to 3 criteria. Instead I am trying to use
VB to conditionally format the cells since I have 6
criteria. Here is what I am using...
Private Sub QC10600_AfterUpdate()
Select Case .Value
Case "EV"
.BackColor = 16711680
.ForeColor = 16777215
.FontBold = True
Case "CA"
.BackColor = 57600
.ForeColor = 16777215
.FontBold = True
Case "RE"
.BackColor = 8388736
.ForeColor = 16777215
.FontBold = True
Case "TR"
.BackColor = 65535
.ForeColor = 16777215
.FontBold = True
Case "TO"
.BackColor = 255
.ForeColor = 16777215
.FontBold = True
Case "PR"
.BackColor = 435844
.BackColor = 16777215
.FontBold = True

End Select
End With
End Sub

This is set to the AfterUpdate Event of the combo box but
when I scroll through records it keeps the format of the
last update I made regardless of what the value is in the
new record. I want the format to change depending on what
the value is in the field of the combo box when it is
changed and when I someone scrolls through the records.
Thanks for any assistance.

 

[Wayne Morgan]

Is it a multicolumn combo box? If so, is the value you have listed the value from the
Bound Column? Also, the beginning of your With statement is missing. Does it change when
you move away from the combo box to the next control?

 

[Markus]

The Combo Box List is bound to TBLReason which has Reason
Code(EV,CA,RE,...)and ID (1,2,3,...). It is bound to
column 1 and Limit to List is set at No in the QC10600
properties. The format stays as I move from one control to
the next. The problems is that the format stays when I
change records, I want the format to change as the value
changes. I posted the VB incorrectly, there is a with
statement.

Private Sub QC10600_AfterUpdate()
With Me.QC10600
Select Case .Value
Case "EV"
.BackColor = 16711680
.ForeColor = 16777215
.FontBold = True
Case "CA"
.BackColor = 57600
.ForeColor = 16777215
.FontBold = True
Case "RE"
.BackColor = 8388736
.ForeColor = 16777215
.FontBold = True
Case "TR"
.BackColor = 65535
.ForeColor = 16777215
.FontBold = True
Case "TO"
.BackColor = 255
.ForeColor = 16777215
.FontBold = True
Case "PR"
.BackColor = 435844
.BackColor = 16777215
.FontBold = True

End Select
End With
End Sub

-----Original Message-----
Is it a multicolumn combo box? If so, is the value you have listed the value from the
Bound Column? Also, the beginning of your With statement

is missing. Does it change when

 

[Wayne Morgan]

I just tried the following and it worked fine:

With Me.Combo0
Select Case .Value
Case Is < 15
.BackColor = 16711680
.ForeColor = 16777215
.FontBold = True
Case 15 To 45
.BackColor = 57600
.ForeColor = 16777215
.FontBold = True
Case Is > 45
.BackColor = 8388736
.ForeColor = 16777215
.FontBold = False
End Select
End With

You say it is bound to column 1, but what is column 1? Is column 1 the text you are
testing for or is column 1 a hidden column holding the ID number that you are storing in
the table? The bound column will give you the Value of the combo box, not the text
displayed in the combo box unless that is the bound column. You could use the .Text value
in the AfterUpdate event, but that won't help you when you move between records because
the control has to have the focus to use the .Text value. I suspect your "EV", etc are in
the second column. Try changing

..Value
to
..Column(1) 'the column statement here is ZERO based, so 1 is the 2nd column. Not so in
the Properties sheet.

The other option is to use the ID numbers instead of the text in your tests. To make this
change as you move between records you would need to run this in the Form's OnCurrent
event. You can either retype the code or call this procedure from the OnCurrent event.

Example:
QC10600_AfterUpdate