how to change font color in combo box based on criteria

[ConfusedMay]

Hi experts,

Could you please help me with this situation? I'm using Access 2003 and I have a form with combo box containing lists of customer orders and status. What I need help on is when users choose customer order with status "C" then the font in the combo box for this customer order is red, else will remain black.
How can I do this? I tried using conditional formatting but it won't work.

Thank you so much...

[beacon]

You can use VBA to accomplish this. In the BeforeUpdate event for the combobox, enter the following:

Private Sub YourComboBox_BeforeUpdate()

     If Me.YourComboBox.Columns(2) = "C" Then 'This assumes that you only have 2 display columns in your combobox
          Me.YourComboBox.ForeColor = vbRed
     Else
          Me.YourComboBox.ForeColor = vbBlack
     End If

End Sub

[ConfusedMay]

Thank you for the quick response.

When I tried to put your code, it didn't work quite well. when I choose from my combo box, it didn't change the value, instead it went back to the first value on the combo box list, for example, when I choose cust order 11111, it displayed cust order 00000 on the combo box since 00000 is the first cust order on the list. Am I missing something?

[beacon]

Can you post your code?

[ConfusedMay]

Hi Beacon,

sorry for the late reply...

here is the code that I have

for combo box, I called it txtcustorder
on the property, it has bound column -- 2
Row source type -- table/query
Row source --

 SELECT cust_ORDER.cust_ID,cust_ORDER.STATUS FROM cust_ORDER GROUP BY cust_ORDER.cust_ID, cust_ORDER.STATUS ORDER BY cust_ORDER.cust_ID;

on the after update event:

 Private Sub txtcustorder_afterUpdate()

If Me.txtcustorder.Column(2) = "C" Then       '
Me.txtcustorder.ForeColor = vbRed
Else
 Me.txtcustorder.ForeColor = vbBlack
End If

End Sub

Please help....

[esperance]

Why is order status your bound column?

[OldBirdman]

In post #1, the problem is "...then the font in the combo box for this customer order is red, else will remain black." If that is the problem, then the answer is: It is not possible, at least in Access 2003.
Beacon in post #2 suggests:

Me.YourComboBox.ForeColor = vbRed

but this will change all the text to red, not just the selected line.
In post #3, the test

If Me.txtcustorder.Column(2) = "C" Then ...

is incorrect. Columns are 'Zero Based'. The columns for the combobox are .Column(0) and .Column(1). There is no .Column(2).
Post #6 asks "Why is order status your bound column?" but this isn't getting to the issue. txtcustorder is an unbound control, and ConfusedMay is never referencing txtcustorder.va lue, only txtcustorder.Co lumn(1). It is a good point to come back to, but not relevant to this question.
Why is the value 00000 when 11111 is selected? I would suspect there is more code somewhere that is setting txtcustorder to its original state. A ReQuery of the form perhaps

[slenish]

Hello ConfusedMay,

I might be able to help you with this. I need a little bit more information though. You say you have a combo box and when you select a name in the combo box you want it to turn red if the order status is "C". My first question is the table you are pulling from how many columns do you have? Second, is the status "C" in a seperate column?

If you could provide a little more info on this I might be able to help.

Slen

[beacon]

In post #1, the problem is "...then the font in the combo box for this customer order is red, else will remain black." If that is the problem, then the answer is: It is not possible, at least in Access 2003.
Beacon in post #2 suggests:

Me.YourComboBox.ForeColor = vbRed

but this will change all the text to red, not just the selected line.
In post #3, the test

If Me.txtcustorder.Column(2) = "C" Then ...

is incorrect. Columns are 'Zero Based'. The columns for the combobox are .Column(0) and .Column(1). There is no .Column(2).
Post #6 asks "Why is order status your bound column?" but this isn't getting to the issue. txtcustorder is an unbound control, and ConfusedMay is never referencing txtcustorder.va lue, only txtcustorder.Co lumn(1). It is a good point to come back to, but not relevant to this question.
Why is the value 00000 when 11111 is selected? I would suspect there is more code somewhere that is setting txtcustorder to its original state. A ReQuery of the form perhaps

OldBirdMan,

I tested my suggestion out and it works. True, all of the values in the combo box will turn red if the value selected is equal to the desired value, but it will return to black if a different value is selected.

Since it was my understanding that ConfusedMay simply wanted to the displayed/selected value to be red, this should have resolved the issue.

As for the column numbers, I tested a zero-based column and returned an error message, so I went with what worked for me despite my better judgement.

[beacon]

Hi Beacon,

sorry for the late reply...

here is the code that I have

for combo box, I called it txtcustorder
on the property, it has bound column -- 2
Row source type -- table/query
Row source --

 SELECT cust_ORDER.cust_ID,cust_ORDER.STATUS FROM cust_ORDER GROUP BY cust_ORDER.cust_ID, cust_ORDER.STATUS ORDER BY cust_ORDER.cust_ID;

on the after update event:

 Private Sub txtcustorder_afterUpdate()

If Me.txtcustorder.Column(2) = "C" Then       '
Me.txtcustorder.ForeColor = vbRed
Else
 Me.txtcustorder.ForeColor = vbBlack
End If

End Sub

Please help....

Hi ConfusedMay,

One thing that I noticed right off is that you used the AfterUpdate event and not the BeforeUpdate event, which is what I originally suggested.

After reading OldBirdMan's post, I feel it's necessary to ask you to clarify what you are asking for as the solution I provided you with will, in fact, turn all items in the combobox red if you view them when selecting the dropdown. The value will only be red or black in the text box portion of the combobox, but I'm now not sure if this is what you were after or not.

[ConfusedMay]

Hi all,

Thank you so much for all your replies and yes beacon, my bad, I created it on the after update when it's suppose to be on the before update.

before update is worked now...

Thank you....

[ConfusedMay]

Hi Beacon,

Just one quick question, why I need to put the code on the before update event instead of the after update event? I thought whatever that I need code change after whatever stuff that I enter, I need to put it on the after update event. Sorry if the question is kind of dumb, I'm new at VBA and still has a lot to learn.

Thank you...

[beacon]

Hi Beacon,

Just one quick question, why I need to put the code on the before update event instead of the after update event? I thought whatever that I need code change after whatever stuff that I enter, I need to put it on the after update event. Sorry if the question is kind of dumb, I'm new at VBA and still has a lot to learn.

Thank you...

I could be way off base on this, so don't take what I say as a statement of fact as it's only based on my understanding, however limited that may be.

With AfterUpdate, you're expecting the record to be saved before some action is taken. With a combobox, any change in the value of the combobox doesn't necessarily save/change the record unless explicitly coded to do so. That's the reason why nothing appeared to happen when you used AfterUpdate. However, had you moved to another record, and come back to the record you changed, chances are the value would have been red.

On the flip side, the BeforeUpdate event fires before the record is saved/changed, which makes it the likely event to use with most controls, especially those that don't involve saving a record. In my experience, BeforeUpdate is the best, if not the only option when modifying a form with unbound controls that are used specifically to improve the functionality for the user (such as modifying a control based on a different control with an expression that isn't bound to a field on an underlying table).

Like I said, this is only what I've been able to ascertain from developing my own databases and may be far from the reality of it all. There's some reading online if you want to study up on it...just search "difference between beforeupdate and afterupdate events".

Hope this helps...