Only 3 Conditional formats? Help!

[Spiff1959]

I'm pulling data from an external (Advantage) database via ODBC and am trying to present an updatable form to the users.

All was well until I was asked to color code each records "status" in my continuous form to match the colors used in the retail software we use.

Conditional formatting would have been great, except there are 10 different status codes, with 10 different colors. Access 2003 seems to limit you to 3 conditional formats per field. I wrote some VB code that performs the function, but I don't know how to trigger it properly, or to make it reference each "status" field in the form individually. Presently, when loaded, it paints every 'status' field on the continuous form the same color as the first record should be. If you make a different record current, then all the 'status' fields switch to that color. How/when can I qualify individual records/fields on a cont. form and set the text color appropriate to that records 'status' value?

I searched the forums and found similar questions, but most were solved with conditional formatting + a VB macro. With me needing 10 color options depending on field value, I'm unsure how to procede.

Thank you

 

[TylerTand]

Your problem is that you have a continuous form and you are trying to make formatting changes based on individual records. With a continuous form the field can only have one formatting at a time. Since you are in reality just seeing the same field multiple times it can't display different colors at the same time. You are correct about 2003 and only having three conditional formatting options. I ran into the same problem. If you come up with a solution please post it.

 

[odin1701]

Yup. Easily solved if it wasn't a continuous form.

The only possible way I can think of to do this is to have mutliple text box fields for the same information, and then either show or hide them based on what data is in them. Format the different text boxes with the different colors - problem is that this would hide them on all records in a continuous form depending on which record was selected, effectively changing the color of all of them. Maybe someone can build off that idea and somehow hack it up to work but other than that I can't think of anything.

 

[Spiff1959]

Conditional Formatting (revisited)

Ok, guess I'm out of luck using a continuous form.
I can display the same data via a Data Access Page, set to display all records. Can I reference individual fields in each row and set attributes depending on field value in a DAP? How does one attach a macro/script/function to a field within a DAP?
I don't see options regarding 'events'.

Thanks again.

 

[boblarson]

See if this helps:

http://msdn2.microsoft.com/en-us/library/aa139965(office.10).aspx

 

[DCrake]

Simple Software Solutions

I have my old Access 97 Tutorial at home and I remember there was a topic where you could run a report that had a OLE field with a coloured box in it. When you ran the report it displayed the colour for each record. I am sure this could be bastardised to work on a continous form. Will dig it out tonight and get back to you.

CodeMaster:

 

[Spiff1959]

No success so far
Thank you boblarson, I was 2 paragraphs into reading that article and saw "The three-condition limit can be overcome by using VBA code" and I was about ready to come back here and post a big "KA-CHING!" for you. But, his method of overcoming wasn't all I envisioned it might be. Looks like he is redefining the three FormatCondition objects on-the-fly to manipulate the formatting of a single output field (one that appears only once on the form). On my repeating field, as soon as I redefined a FormatCondition object, any fields using that objects former attributes would also switch to the newly defined ones, and I still end up with only four concurrent states to play with: the default and the 3 conditional formats.

I'm still hoping maybe some HTML/Java code embedded in a Data Access Page could get me 10 background colors on the same repeating field depending on it's value???
One other question... Access 2007 does not have the 3-condition limit?

I do appreciate all of your responses

 

[boblarson]

Sorry it wasn't what you needed. As for Access 2007 having the limit, unfortunately it still does.

 

[Spiff1959]

I'd thought I'd seen something somewhere, and looked around... I found this on microsoft:

Number of conditions on a range is no longer limited by number. In Excel 2003, you could create conditional formatting rules with three conditions. In Excel 2007, you are no longer limited by number; you are only limited by system memory.

Of course, I found no mention of similar changes to Access. I guess MS doesn't want to be accused of being consistant