Color fields in forms based on ctl value

[SERT1]

I have a problem with changing colours on a combobox field in a form based on a value. If am using Access 97, so it doesn't have the "conditional formatting" tool. To make it simple, here is the scenario. I have 3 records, each one with an Identifier and a combobox field. The combobox options are Yes, No, NA. When showing these fields on a continuous form, how would I be able to have record #1 combobox field show red (for equalling "No&quot, record #2 combobox show green (for equalling "Yes&quot and record #3 combobox show white (for equalling "NA&quot. I have tried code but nothing works; when the options are changed in the combobox, all the comboboxes are changed to the colour of the last change regardless of what the value of the field is. Is there any way in Access 97 to get around this?

 

[MikiH]

Somebody asked this question a week ago on a different forum. This is the answer I gave them. hope it helps



Sorry not much detail, could sit down for a hour and figure it out fully but this is basicly how I would do it. sorry but it not proper code

on change event

dim comboname as txt,
dim 1stcolour as long, 2ndcolour aslong...

(this is the bit I don't know its the RGB function)

1stcolour = RGB(255, 0, 0) (red)
2ndcolour = RGB(0, 0, 0) (black)
3rdcolour = rgb(???????)

select case comboname

Case "yes"
Me!comboname.ForeColor = 1stcolour
Case "no"
Me!comboname.ForeColor = 2ndcolour
case "NA"
Me!comboname.ForeColor = 3rdcolour

End Select

end sub


for more help use access help and search for "Select Case statement" and "forecolor property"

 

[SERT1]

Unless the "select case" part is different than an "If..elseif" statement, this does not work. What happens is all of the comboboxes in the continuous form turn the colour you've selected, regardless of the value in subsequent records. The "conditional formatting" in Access 2000 works fantastic, but I don't have Access 2000 at work, and on my home copy of Access 2000, I couldn't find where Access put the code for the "conditional formatting" option.