Unanswered: Changing the color of a field depending on the value of the field
I have the following problem. I have created a form to display all the records in a table in a spreadsheet like format (not datasheet view). Depending on the value that one filed will have, for each different record, I want to be able to change the back color of that field for that record. For example lets say that we have textbox field1. This field can either have the value of 1, 2, 3 or 4. When the form loads I want to perform a check and if the value in field1 is 1 the back color of the field should be red, if the value is 2 black and so on. By using
if Me![field1].value = 1 Then
Me![field1].backcolor = 255
Elseif Me![field1].value = 2 Then
Me![filed1].backcolor = 0
it changes the color to all the field1 text boxes in the form i.e. for all the records of the table, to the color of the first correct if statement.
Could you please provide some help on this matter?
Re: Changing the color of a field depending on the value of the field
This is a limitation of Access.
The workaround is to place a number of unbound textboxs behind your control(transparent) with an iif statement to display #### or something which fills the field area. you need as many boxes on top of each other as colours.
So if my control is [FieldA], I would make it transparent with black font.
Behind it I would place [text1] with Red Bold font format with the code:
=iif([FieldA] = 1,"#####","")
which means if [FiledA]=1 then show #### else show nothing.
for green you would then put another box also behind it formatted green bold and put:
=iif([FieldA] = 2,"#####","")