Conditional Formatting - more than 3 conditions

Is there a way to "hack" Access to allow more than 3 conditional formatting conditions on a field?  I actally need 7.  The purpose of all this is to have one big subform in datasheet mode where each row is colored differently (7 colors) depending on some data condition.l

LVL 4
DrLechterAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
This came up a couple of times a while back. Wherein I originally thought that you could have more than 3 using VBA, that was proven to not be the case.  So, 3 appears to be the limit.

Also ... note that if you end of with 3 conditions and a LOT of records say in a datasheet or continuous form view, CF gets pretty slow ... especially when scrolling.

mx
0
Rey Obrero (Capricorn1)Commented:
this is possible using continuous form, not datasheet.
0
DrLechterAuthor Commented:
Does anyone know where the conditional formatting expressions are stored?  Perhaps if they're in a system table, the table could be hacked to add additional (more than 3) conditions.

0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
You can't hack system tables.  You can only Read from system tables.

mx
0
shaydieCommented:
There is a limit of three conditional formatting settings. It's too bad, it would be nice if there were more. I'd love to find a workaround and have done a lot of research into that but have not ever found any work around to this limitation when using a datasheet.
Something that might work for you though...
You have a lot more control with form views and reports. Why don't you try using a continuous form for your subform. Format it to look similar to a datasheet. In your detail paint event of the subform color your text boxes like this:


Private Sub Detail_Paint()
Select Case MyValue
Case Condition1
Me.TextBox1.Backcolor = 16740484
Me.TextBox2.Backcolor = 16740484
Me.TextBox3.Backcolor = 16740484
Me.TextBox4.Backcolor = 16740484
Case Condition2
Me.TextBox1.Backcolor = vbYellow
Me.TextBox2.Backcolor = vbYellow
Me.TextBox3.Backcolor = vbYellow
Me.TextBox4.Backcolor = vbYellow
Case Condition3
Me.TextBox1.Backcolor = vbRed
Me.TextBox2.Backcolor = vbRed
Me.TextBox3.Backcolor = vbRed
Me.TextBox4.Backcolor = vbRed
Case Else
Me.TextBox1.Backcolor = vbBlue
Me.TextBox2.Backcolor = vbBlue
Me.TextBox3.Backcolor = vbBlue
Me.TextBox4.Backcolor = vbBlue
End Select
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrLechterAuthor Commented:
shaydie:

I can't find the On Detail Paint event you're referring to.  Would you elaborate this?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I'm guessing:

Private Sub Detail_Print ()   ' PRINT

mx
0
Rey Obrero (Capricorn1)Commented:
DrLechter,
Are you using a continuous form as suggested at http:#a32168269
0
Rey Obrero (Capricorn1)Commented:
Detail_Print  is for reports..

Detail_paint is for continuous and datasheet forms
0
Rey Obrero (Capricorn1)Commented:
Detail_Print  is for reports..

Detail_paint is for continuous and datasheet forms and reports
 
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
paint ?
0
Rey Obrero (Capricorn1)Commented:
yes PAINT
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
where ?
0
DrLechterAuthor Commented:
Capricorn:

Yes, I am using a continuous form.   I just don't see this detail_paint event in any of the event lists.  Where is it?
0
Rey Obrero (Capricorn1)Commented:
0
DrLechterAuthor Commented:
Hmm.  Is that a 2007 thing?  I'm using 2003 at the moment.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
A2007.

mx
0
DrLechterAuthor Commented:
Thanks for the help everyone.  Maybe one day, MS will allow more than 3 conditions.  For now, we'll have to suffer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.