• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1677
  • Last Modified:

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

0
DrLechter
Asked:
DrLechter
  • 6
  • 6
  • 5
  • +1
3 Solutions
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
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 ArchitectCommented:
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 ArchitectCommented:
paint ?
0
 
Rey Obrero (Capricorn1)Commented:
yes PAINT
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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 ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 6
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now