How to format color in a combo box depending on value?

[jaad]

I have a combo box that I would like to shade the background depending on the value.

this is what I have but it doesn't work:

Private Sub Form_Current()
If ([Provenance].Value = "Appointment") Then [Provenance].BackColor = ADC0D9
ElseIf ([Provenance].Value = "Journal") Then [Provenance].BackColor = DFA7A5
ElseIf ([Provenance].Value = "Project") Then [Provenance].BackColor = BFB2CF
ElseIf ([Provenance].Value = "Repairs") Then [Provenance].BackColor = CDDCAF
ElseIf ([Provenance].Value = "Symbiosis") Then [Provenance].BackColor = F9CDAA
 

any idea on how to fix this?
I am using access 2007

[beacon]

Are you using a continuous form?

If not, try using the BeforeUpdate event for the combobox instead of the Form_Current event.

[jaad]

Yes was in a continuous form, I made the default view data sheet now as it seem a little clearer to read. I have a 3 conditional formatting on a date field which work but the other field [Provenance] requires 5 different colors. I am going to try to put in the before update as you suggested and will report back.

[jaad]

ok I've tried it and it doesn't work. Maybe I should mention that the combo box use a value list and the field type is text; I didn't mentioned that in my original post. I tried using [1] instead of [Provenance] because Appointment is the first one on the list but that doesn't work either

[Stewart Ross]

Hi. Had this one been possible your value tests would have been done from the AfterUpdate event of the combo rather than its BeforeUpdate event - it is fired after the change of value takes place, and at that point you can certainly change the backcolor property of the control if necessary.

However, as you are using a continuous form this approach will not work. The reason is that any change you make to the backcolor property of the combo will apply simultaneously to ALL the data rows currently shown in your form. In design view you see just one control, and that single instance is repeated for all the rows displayed in the continuous form. Whatever is done to its properties in code affects all instances of that control at the same time.

In datasheet view you are even more restricted, as you don't get the chance to display custom colours at all.

It was this kind of limitation that conditional formatting addressed, but as you have said the limitation to three conditions does not fulfil your needs.

-Stewart

[mshmyob]

If you are looking to change the background of different controls on a Continous subform then you can check out my demo here http://bytes.com/topic/access/insigh...imited-colours in the insights.

You can have as many conditional colour changes as you want and apply them to as many controls as you want or even an entire row. This technique only works in AC2007.

cheers,

[beacon]

Thank you for correcting me Stewart. I was in a rush when I posted and obviously didn't think it through very well.