Link to home
Start Free TrialLog in
Avatar of wsturdev
wsturdevFlag for United States of America

asked on

Control BackColor of textbox on continuous forms

I know this question has been asked and answered before, but thought I would try again anyway in case someone has found/devised a way of doing it...

I am working in Access 2003.  I have a sub form that is continuous.  I want to colorize either the Detail BackColor or the BackColor of a text control depending upon value.  The problem is I have more than the 4 colors that would be allowed in conditional formatting (the original plus 3 conditional).  In my specific case, I have 19 records, and I want 19 different colors.

Avatar of jpipkins

Use conditional formatting.  Select the control you want, then on the Format menu, select Conditional formatting.  Enter your conditions and your formatting and presto!
Uh...sorry!  I read your question the first time and by the time I got around to figuring out what I thought was the answer, I forgot that you'd already tried CF.  Sorry.

I guess the reason I went back to that was because I couldn't make it work the way you wanted it to.  Normally, you'd use the oncurrent event of the form to handle that kind of thing.  But on a continuous form, the oncurrent event only fires once.  I'll keep working at it and let you know.
Avatar of jpipkins

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It might be a good idea to have a column in your imagetable that documents the color.

I only have colors in my imagetable for personnel_no's 1,2, and 3.  I tried to make the BOF be the same size as the control, but it just overtook it and you couldn't see the data when you weren't on the row.  Even though the control was in front of the BOF.

Even though it sounds complicated, it really isn't.  You can see it took me less than an hour to figure it out and document it.

Good luck!
Avatar of wsturdev


This is terrific!!!!!  I will go ahead and award points, but hope you will continue to monitor this post in case I have follow-ons.  I will also let you know how it works out.

Essentially, I am showing a subform with 2 fields: field A is a value, and field B is a count of the number of times the value in field A occurs in the database.  To the right of the subform is a "donut" graph showing the percentage each field B represents when compared to the total of all field Bs.

I do not have the space to add labels or a key to the donut graph, so colorizing the subform's continuous records to match the corresponding sections of the donut graph is the best way to do this.

Usually, the number of values in field A ranges between 15 and 20, but there will never be more than 39 field As.

I already have a color table that I use to control the colors of the sections of the donut.  There are 39 colors listed in the table.  I will follow your suggestion and expand the table.
I will keep monitoring the question.  I don't use graphs too often, so it'll be interesting to me to see how you match the colors in the form to those on the graph.
This is a brilliant solution!!!  The only tedious thing is translating a list of colors, as shown by numbers in the BackColor property into bitmaps.  I will post another question about how I might automate that process.
<how you match the colors in the form to those on the graph>

The Color Table has the desired colors listed in order with a sequence number.  I already have code that loops through the records in the subform.  Record i of the subform corresponds to Sequence 1 in the color table (16763904 -- mxlSkyBlue -- wdColorSkyBlue), which is applied to segment 1 of the graph.

I will dynamically generate a sequence number (hidden) in the subform.

With your solution, by doing a Left Join between the dynamically generated sequence number in the record source for the subform and the sequence number in the Color Table, the background of each continuous record will also have the same corresponding color.

Excellent!  All you need is a table of all the colors that the graph uses and you are in business.  Would you mind exporting your table to another mdb and posting it here when you finish?  I'd like to have a copy of that, as I'm sure others would as well.
Here is the Color table in a small MDB.  The name has been modified to include an extension (.txt) that is acceptable to the upload.