Workaround for Format Conditions limited to 3 (1 Viewer)

Royce

Access Developer
Local time
Today, 05:17
Joined
Nov 8, 2012
Messages
98
As many of you know, you can set lots (Some say 32, some say 50) of Format Conditions in Design View by right clicking a text box and Selecting Conditional Formatting. But you try it in code and you will get errors if you try to set more than 3. (Internally, there is a Microsoft BUG, not a design limit, that resets the count when you touch conditions 0-3) I found the following will let me bypass that limit.

Does anybody know how to report a bug to Microsoft? :( Do they even acknowledge that this is a bug?:mad:

Code:
Public Sub SetConditionalFormating()
'---------------------------------------------------------------------------------------
' Procedure : SetConditionalFormating
' Author    : Royce Fessenden
' Date      : 11/22/2014
' Purpose   : Sample workaround for the Access bug that limits the number of Format
'           : Conditions that can be set in code to 3.
'           :
'           : ToUse:  In design mode set up 3 or more FormatConditions.  Conditions 0,1,2
'           : (First thru third) can be conditions that will never change, or conditions
'           :  that will never be matched.
'           :For example, if lngStatusId is always positive you can set the first conditions
'           : to [lngStatusId] < 0.
'           :
'           :This has not been extensively tested, but it fixed my problem.  I use Condition 0
'           :for one that Is not changeable by the User.  Conditions 1,2 are "never matches",
'           : and the user can edit a table to set up conditions past the third one.
'           :
'           :
'---------------------------------------------------------------------------------------
'
    
    Dim i As Integer
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
   On Error GoTo Proc_Err
    
    
    strSQL = "SELECT * From tblStatusCodes WHERE ysnFormatCondition = true"
    Set rst = dbLocal.OpenRecordset(strSQL, dbOpenSnapshot)
    If Not rst.BOF Or Not rst.EOF Then
        With Me.txtFirstField.FormatConditions
                       
            rst.MoveFirst
            
            i = 3 ' Never touch 0,1,2 or it will limit the count to 3.
           
            Do While Not rst.EOF
                Debug.Print "Start Loop: "; i, Me.txtFirstField.FormatConditions.Count; " Formats"
                If i < .Count Then
                    .Item(i).Modify acExpression, acEqual, "[lngStatusId]= " & CStr(rst!lngStatusId)
                    .Item(i).BackColor = Nz(rst!lngBackColor, vbWhite)
                    .Item(i).FontBold = Nz(rst!ysnBold, False)
                    .Item(i).ForeColor = Nz(rst!lngTextColor, vbBlack)
                Else
                    Debug.Print "At Add: "; i, Me.txtFirstField.FormatConditions.Count; " Formats"
               
                    .Add acExpression, acEqual, "[lngStatusId]= " & CStr(rst!lngStatusId)
                    .Item(i).BackColor = Nz(rst!lngBackColor, vbWhite)
                    .Item(i).FontBold = Nz(rst!ysnBold, False)
                    .Item(i).ForeColor = Nz(rst!lngTextColor, vbBlack)

                End If
                
                rst.MoveNext
                i = i + 1
            Loop

            
        End With
    End If
       
Proc_Exit:
   On Error Resume Next
   rst.Close
   Set rst = Nothing
   
   Exit Sub

Proc_Err:
    MsgBox "Nasty Bug!"
    'LogError Err.Number, Err.Description, mcModuleName, "SetConditionalFormating", vbNullString, gcGENERAL_ERROR, False
    Resume Proc_Exit

    
End Sub
 

llkhoutx

Registered User.
Local time
Today, 05:17
Joined
Feb 26, 2001
Messages
4,018
I think that "Access bugs" are undisclosed features.:banghead:
 

cemmott

New member
Local time
Today, 10:17
Joined
Oct 7, 2019
Messages
3
Hi, this is my first post - just registered so i could post this reply.
I've been struggling with this limit of 3 FC's via VBA for a long while, and have discovered a workaround. I'm using Access 2010. By accident i found that i could add more than 3 FCs in one control but got the familiar error in another.
It's clear there is an MS bug here, but i found you can add 4 or more FC's so long as they're all the same! So first you add the nth by copying the (n-1)th, and after this you can use Modify to change it to what you really wanted.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:17
Joined
Oct 29, 2018
Messages
21,357
Hi, this is my first post - just registered so i could post this reply.
I've been struggling with this limit of 3 FC's via VBA for a long while, and have discovered a workaround. I'm using Access 2010. By accident i found that i could add more than 3 FCs in one control but got the familiar error in another.
It's clear there is an MS bug here, but i found you can add 4 or more FC's so long as they're all the same! So first you add the nth by copying the (n-1)th, and after this you can use Modify to change it to what you really wanted.

Hi. Welcome to AWF! Thanks for the update. Would you mind posting a sample code too? Cheers!
 

cemmott

New member
Local time
Today, 10:17
Joined
Oct 7, 2019
Messages
3
I made a form with a text control called 'testing', and put this in the open event...

Private Sub Form_Open(Cancel As Integer)
Dim fc As FormatCondition, MyControl As control

Set MyControl = Me.testing

MyControl.FormatConditions.Delete
Set fc = MyControl.FormatConditions.Add(acFieldHasFocus)
Set fc = MyControl.FormatConditions.Add(acExpression, , "[DUMMY]")
Set fc = MyControl.FormatConditions.Add(acExpression, , "[DUMMY]")
Set fc = MyControl.FormatConditions.Add(acExpression, , "[DUMMY]")
Set fc = MyControl.FormatConditions.Add(acExpression, , "[DUMMY]")
Set fc = MyControl.FormatConditions.Add(acExpression, , "[DUMMY]")
Set fc = MyControl.FormatConditions.Add(acExpression, , "[DUMMY]")
Set fc = MyControl.FormatConditions.Add(acExpression, , "[DUMMY]")


Set fc = MyControl.FormatConditions(0)
fc.BackColor = RGB(255, 255, 255)
Set fc = MyControl.FormatConditions(1)
fc.BackColor = RGB(255, 255, 0)
Set fc = MyControl.FormatConditions(2)
fc.BackColor = RGB(255, 0, 255)
Set fc = MyControl.FormatConditions(3)
fc.BackColor = RGB(0, 255, 255)
Set fc = MyControl.FormatConditions(4)
fc.BackColor = RGB(255, 0, 0)
Set fc = MyControl.FormatConditions(5)
fc.BackColor = RGB(0, 255, 0)
Set fc = MyControl.FormatConditions(6)
fc.BackColor = RGB(255, 0, 0)
Set fc = MyControl.FormatConditions(7)
fc.BackColor = RGB(0, 0, 0)
fc.Modify acExpression, , "[HELP]"
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:17
Joined
May 7, 2009
Messages
19,169
you have same 7 fc with 7 diff backcolors?
what is the Expression "[DUMMY]", do you mean when DUMMY=True?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:17
Joined
Oct 29, 2018
Messages
21,357
I made a form with a text control called 'testing', and put this in the open event...

Private Sub Form_Open(Cancel As Integer)
Dim fc As FormatCondition, MyControl As control

Set MyControl = Me.testing

MyControl.FormatConditions.Delete
Set fc = MyControl.FormatConditions.Add(acFieldHasFocus)
Set fc = MyControl.FormatConditions.Add(acExpression, , "[DUMMY]")
Set fc = MyControl.FormatConditions.Add(acExpression, , "[DUMMY]")
Set fc = MyControl.FormatConditions.Add(acExpression, , "[DUMMY]")
Set fc = MyControl.FormatConditions.Add(acExpression, , "[DUMMY]")
Set fc = MyControl.FormatConditions.Add(acExpression, , "[DUMMY]")
Set fc = MyControl.FormatConditions.Add(acExpression, , "[DUMMY]")
Set fc = MyControl.FormatConditions.Add(acExpression, , "[DUMMY]")


Set fc = MyControl.FormatConditions(0)
fc.BackColor = RGB(255, 255, 255)
Set fc = MyControl.FormatConditions(1)
fc.BackColor = RGB(255, 255, 0)
Set fc = MyControl.FormatConditions(2)
fc.BackColor = RGB(255, 0, 255)
Set fc = MyControl.FormatConditions(3)
fc.BackColor = RGB(0, 255, 255)
Set fc = MyControl.FormatConditions(4)
fc.BackColor = RGB(255, 0, 0)
Set fc = MyControl.FormatConditions(5)
fc.BackColor = RGB(0, 255, 0)
Set fc = MyControl.FormatConditions(6)
fc.BackColor = RGB(255, 0, 0)
Set fc = MyControl.FormatConditions(7)
fc.BackColor = RGB(0, 0, 0)
fc.Modify acExpression, , "[HELP]"
End Sub
Hi. Thanks!
 

isladogs

MVP / VIP
Local time
Today, 10:17
Joined
Jan 14, 2017
Messages
18,186
Hi cemmott
Like arnel I'm a little unclear how your code actually works.
I've added it to a form but can't see how the colours would be triggered.
Sorry if I'm being dumb
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:17
Joined
Oct 29, 2018
Messages
21,357
Hi cemmott
Like arnel I'm a little unclear how your code actually works.
I've added it to a form but can't see how the colours would be triggered.
Sorry if I'm being dumb
Hi Colin. I see the same thing, but I took it as "seeing" the effect was not as important as demonstrating the code worked and was able to assign more than 3 format conditions to one control using VBA. I verified this by adding a separate routine to examine the Format Conditions applied to the control after the form has opened, and I was able to see all 8 conditions.
 

isladogs

MVP / VIP
Local time
Today, 10:17
Joined
Jan 14, 2017
Messages
18,186
I can see the code compiles but do I need to change 6 of the DUMMYs to something else? Why the HELP line at the end? As I said maybe I'm being dense
Perhaps either Cemmott or DBG could post a sample from as I'm still unclear how to proceed. Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:17
Joined
Oct 29, 2018
Messages
21,357
I can see the code compiles but do I need to change 6 of the DUMMYs to something else? Why the HELP line at the end? As I said maybe I'm being dense
Perhaps either Cemmott or DBG could post a sample from as I'm still unclear how to proceed. Thanks
Fair enough. Challenge accepted. Please see attached demo. Hope I did it correctly. If not, please let me know what I missed.
 

Attachments

  • CF Demo.zip
    27.7 KB · Views: 442

isladogs

MVP / VIP
Local time
Today, 10:17
Joined
Jan 14, 2017
Messages
18,186
OK thanks for doing that. Now I understand.
However I changed the formulas and wondered why the multiple shades of green didn't change.
It would clearly be better to have removed the shades of green rules you applied using the CF wizard. :D
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:17
Joined
Oct 29, 2018
Messages
21,357
OK thanks for doing that. Now I understand.
However I changed the formulas and wondered why the multiple shades of green didn't change.
It would clearly be better to have removed the shades of green rules you applied using the CF wizard. :D
LOL. You know it!
 

cemmott

New member
Local time
Today, 10:17
Joined
Oct 7, 2019
Messages
3
Ok yes my code doesn't actually get triggered with real data as the DUMMY is an invalid reference so is never true. But the point was to demo that you can view (in Layout mode) the different colours in the field formatting after doing this.
I've since found that you can continue adding further FC's after these - the ones you really want - then delete the dummy ones afterwards. Seems you just need to add duplicate FC's until you've got past the 3 limit. Code for that is here...


'delete the dummy FC's no longer needed (reverse order as the count reduces as they get deleted)
For FCNo = MyControl.FormatConditions.Count - 1 To 0 Step -1
Set fc = MyControl.FormatConditions(FCNo)
If fc.Expression1 = "[DUMMY]" Then fc.Delete
Next
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:17
Joined
Oct 29, 2018
Messages
21,357
Ok yes my code doesn't actually get triggered with real data as the DUMMY is an invalid reference so is never true. But the point was to demo that you can view (in Layout mode) the different colours in the field formatting after doing this.
I've since found that you can continue adding further FC's after these - the ones you really want - then delete the dummy ones afterwards. Seems you just need to add duplicate FC's until you've got past the 3 limit. Code for that is here...


'delete the dummy FC's no longer needed (reverse order as the count reduces as they get deleted)
For FCNo = MyControl.FormatConditions.Count - 1 To 0 Step -1
Set fc = MyControl.FormatConditions(FCNo)
If fc.Expression1 = "[DUMMY]" Then fc.Delete
Next
Hi. Understood. Thanks for the additional information.
 

isladogs

MVP / VIP
Local time
Today, 10:17
Joined
Jan 14, 2017
Messages
18,186
Hi cemmott
To me, your latest suggestion does sound much the same as that given by Royce originally.:D
Anyway many thanks for your contributions
 

Joce1

New member
Local time
Today, 11:17
Joined
Jun 7, 2020
Messages
3
Hi, I had the same problem, you greattly helped, couldn't get it to work with acExpression though

I finally found the answer here : utteraccess.com/forum/index.php?showtopic=2034035

Access being so dumb making you lose sevral hours on this stupidity :rolleyes:

Thx again :)

edit: bad link correction
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 03:17
Joined
Oct 29, 2018
Messages
21,357
Hi, I had the same problem, you greattly helped, couldn't get it to work with acExpression though

I finally found the answer here : utteraccess.com/forum/index.php?showtopic=20340

Access being so dumb making you lose sevral hours on this stupidity :rolleyes:

Thx again :)
Hi. Welcome to AWF!
 

Users who are viewing this thread

Top Bottom