Full Version: Custom Conditional Formatting on Continuous Form
UtterAccess Forums > Microsoft® Access > Access Forms
JermD
Hello
I have a form and a subform. I needed to format the records in the subform, so I used conditional formatting. It works but is slow to refresh, and I need more than the three conditions now, so I looked into doing conditional formatting in code. This is what I used:
Dim objFrc As FormatCondition
Me![Ctl7].FormatConditions.Delete
Set objFrc = Me![Ctl7].FormatConditions.Add(acExpression, , Ctl7 = 4)
Set objFrc = Me![Ctl7].FormatConditions.Add(acExpression, , Ctl7 = 11)
Set objFrc = Me![Ctl7].FormatConditions.Add(acExpression, , Ctl7 = 18)
Set objFrc = Me![Ctl7].FormatConditions.Add(acExpression, , Ctl7 = 25)
With Me.[Ctl7].FormatConditions(0)
.BackColor = 32896
End With
With Me.[Ctl7].FormatConditions(1)
.BackColor = 52377
End With
With Me.[Ctl7].FormatConditions(2)
.BackColor = 6723891
End With
With Me.[Ctl7].FormatConditions(3)
.BackColor = -2147483633
End With
Oget the following error: 'The format condition number you specified is greater than the number of format conditions.' I'm not sure what is wrong. Ctl7 is a bound control in the continuous subform. If I make it unbound the code actually works, but formats the whole column. There must be a way to do this since it works in the conditional formatting interface provided. Any ideas what is wrong? Thanks in advance.
Jeremy
JermD
By the way, I put an error handler in the procedure to ignore the error message. Now it changes the back color of the whole column based on the value in the field that has the focus. There are five records total, and each one should have a different backcolor. Any suggestions?
theDBguy
Have you searched the Code Archive for demos on Multiple Conditional Formatting in Continuous Forms? Here's one thread with an attachment. It might help you out. Good luck.
JermD
I checked that out. The attachment had a single form that I changed to continous and same result, as each record got the focus, the whole column changed.
theDBguy
Try this one. HTH.
JermD
I got this to work:
im ctl As Control, n As Integer
For Each ctl In Me.Controls
If ctl.Tag = "?" Then
ctl.FormatConditions.Delete
For n = 1 To 3
With ctl.FormatConditions _
.Add(acExpression, , Choose(n, "[Ctl7] = 4", "[Ctl7] = 11", "[Ctl7] = 18"))
.BackColor = Choose(n, 32896, 52377, 6723891, 255)
End With
Next
End If
Next
Except I want to use DLookups in the Choose instead of "[Ctl7=11" and so on, just as I would in the conditional formatting interface. I will try your other example.
theDBguy
I may be wrong here (and somebody else can tell us), but I think part of your problem is that Access only has four possible states for conditional formatting. That is why people like John (from the link I gave you) creates demo to show us the workarounds. I hope you find a solution, and I would be interested to know how you did it. Good luck.
EFCoins
Your first code sample tried to use 4 conditions so it did not work
Your second code sample used 3 conditions so it did work
You can have the default color + 3 conditional colors.
If you want to use more than 4 colors, first make sure that your users will understand the meaning of each color
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.