Changing background color by record more than 3 colors

[jen]

I am trying to change the background color of each field in a record
based on the value of 1 field. There are 12 different status/12
different colors that could be set, conditional formatting only allows
for up to 3 criteria and that would be set per field. The datasheet
view does not really allow for it so I have it set up as a continuous
form for multiple records with each record having about 30 fields. The
data had previously been in an excel spreadsheet and the person is used
to looking at the color coded lines to find her information. The form
needs to open with the rows correctly color coded as well as allowing
for data entry which when the status is set would change the color of
the new row. I wonder if there would be a way to flip the color for
each row as a unit based on the Status field. I am at a loss as to how
to proceed. I have tried using a select case statement on OnCurrent for
the Form but that doesn't allow for each record. Any help would be
appreciated.

 

[gibsonsgman]

there is a fairly easy way you could do this, it may not be the best
way, but it will certainly work. all you do is go into your table in
design view and add a new field called 'Color' or something to that
effect. then, under data type change it to OLE object. Save the table
and open the datasheet view of the table. then under the field just
double click and insert a bitmap image. paint will open and just pick
a color and fill in the background on paint. save it and that field
will have a solid color box/rectangle/whatever shape you want in it.
then in form view, simply drag that field onto your form from the field
list like any other field. let me know if this is not what you are
looking for, there may be other ways.

~Brian

 

[Guest]

I don't know if this will work very effectively because I would still need 12
different boxes with different colors. I guess I am not sure how that would
work (or not work in my case) vs what I have already tried to do using the
status codes and the color numbers. Maybe I am not following you. I am also
concerned with the fact that there are as many fields as there are which
depending on how the code is written could really slow things down.
Unfortunately, this isn't just a want, it is something the person feels they
need.

 

[gibsonsgman]

yes, you are right, my method would slow things down a lot if there are
a lot of records, but there is no code the way I described. There are
several ways of doing this. Maybe you could describe the criteria a
little better? You said there were 12 colors so is there only one
specific value associated with each color? if thats the case, then your
problem is easily solved. You could just go to the 'Form_Current'
event and insert several lines of code like this:
If txtCriteria = "Some Value" Then txtColor.backcolor = 65280
then just set the text box to be disabled and locked, then as you
scroll through the records,the text box will change color along with
the criteria. let me know how this sounds and i might be able to hlep
you out more if this isnt what you are looking for.

 

[Marshall Barton]

I am trying to change the background color of each field in a record
based on the value of 1 field. There are 12 different status/12
different colors that could be set, conditional formatting only allows
for up to 3 criteria and that would be set per field. The datasheet
view does not really allow for it so I have it set up as a continuous
form for multiple records with each record having about 30 fields. The
data had previously been in an excel spreadsheet and the person is used
to looking at the color coded lines to find her information. The form
needs to open with the rows correctly color coded as well as allowing
for data entry which when the status is set would change the color of
the new row. I wonder if there would be a way to flip the color for
each row as a unit based on the Status field. I am at a loss as to how
to proceed. I have tried using a select case statement on OnCurrent for
the Form but that doesn't allow for each record.

There are two other approaches to getting that many colors.
The old way using stacked text boxes and a solid block color
is a combination of the ideas at:
http://www.mvps.org/access/forms/frm0024.htm
http://www.mvps.org/access/forms/frm0055.htm

Since Conditional Formatting was introduced in A2K, another
way is to use four transparent text boxes using the solid
block font and with expressions to determine if CF should be
applied to the text box. The general idea is:
=IIf(status IN(1,2,3), "AAAA", Null)
=IIf(status IN(4,5,6), "AAAA", Null)
. . .
Then set the first text box's CF 3 conditions to:
[status] = 1 and ForeColor to the color for status 1
[status] = 2 and ForeColor to the color for status 2
[status] = 3 and ForeColor to the color for status 3
and the second text box:
[status] = 4 and ForeColor to the color for status 4
[status] = 5 and ForeColor to the color for status 5
[status] = 6 and ForeColor to the color for status 6
etc.

Note that because CF takes some time to perform all those
checks, it may be way better to set your existing text boxes
BackStyle to Transparent and put a single set of color text
boxes behind them (Format - Send to Back). You can size the
color text boxes to the entire detail section or any part.