VBA Conditional Formatting error because I have more than 3 formatting requirements. (1 Viewer)

MajP said:
Curious what @theDBguy found out. My guess it is either a bug or a bad error message where some other error is actually happening. The code looks correct to me and you should definitely be able to do this on a modern version of Access. But stepping through it, it does what you say and loads the first 3 format conditions and then errors.

Definitely something strange. I modified this to apply formatting on the JobStatus Field using the value property instead of the acExpression
With Me.JObStatus.FormatConditions.Add(acFieldValue, acEqual, StatusID)
and it works fine and adds 11 format conditions. So there has to be some trick when using the AcExpression.
Click to expand...
My interpretation from the link, was that for expression, you need to create placeholders and then modify those?
 
klsblues said:
I agree. I’ve spent so long trying different things but to no avail. It will be interesting to see what DBguy has done.
Click to expand...
Sorry guys, I still can only respond using my phone. However, I have also been playing in the background and got the same results as @MajP. My demo from the other thread I linked to earlier was also using acValue, and it works, until I tried to change it to use acExpression. I also tried creating dummy conditions and use the Modify method to change them, but it still hit the ceiling and won't change more than 3 pre-existing conditions.

The solution I can offer at the moment is to convert the textbox into a combobox and use acEqual, that is until I find out more info on how to properly do it.
 
I *think* you have to work from the last to the first.?
 
the limit count for Conditional Format is only 3.
if you need more, use the Paint event of Detail section of your continuous form to set the fore/backcolor.
 
klsblues said:
Hi Dickyp, So does this mean that the user can't set more than three from a form - instead, I will have to 'pre-set' the CF using the conditional formatting button and not allow the user to choose their own colour scheme?
Click to expand...
The answer is no it doesn't mean that BUT you'll need some way of preventing them selecting more than three conditions.

I generally only pre-set CFs and as (by choice) my ribbons are hidden to users so I'd need some way of invoking a method of selection, but I can't see me doing it so I can't give advice on how to do it. Indeed my CFs have meanings and I wouldn't want any user changing them!
 
@klsblues :
The error "7966: The format condition number you specified is greater than the number of format conditions." is caused by having spaces in the condition of the third parameter of "FormatConditions.Add" around the operator.

In your case it is
Code: 
FormatConditions.Add(acExpression, , "JObStatus = " & StatusID)

Try to replace it by
Code: 
FormatConditions.Add(acExpression, , "JObStatus=" & StatusID)
 
AHeyne said:
The error "7966: The format condition number you specified is greater than the number of format conditions."
Click to expand...
it is not. i already test it on a small db. it exceeds the limit of 3 CF.
what he can do is add code to the Paint event of the Detail section.
see your database, Relink it first.


1.png

2.png
 

Attachments

Last edited:
As an aside one wonders if the three limit is the bug, or the redesign of the conditional formatting dialog to allow more than three to be defined?
 
This is a known bug regarding the limt of 3.

@arnelgp :
Did you try my suggestion at all?

I stripped the threadstarters code to a minimal sample, because I have no Outlook and don't get his huge sample running well:

Make a new form with a textbox named 'JobStatusTxt' and use this code in the form:

Code: 
Private Sub Form_Load()
    ApplyFormatConditions
End Sub

Public Sub ApplyFormatConditions()
    With Me.JobStatusTxt
        .FormatConditions.Delete

        Dim xIndex As Integer
        Do While xIndex < 4
            'Errors: With .FormatConditions.Add(acExpression, , "[JobStatus] = " & 1)
            'Works:  With .FormatConditions.Add(acExpression, , "[JobStatus]=" & 1)
            With .FormatConditions.Add(acExpression, , "[JobStatus]=" & 1)
                .BackColor = 2
                .ForeColor = 3
            End With

            xIndex = xIndex + 1
        Loop
    End With
End Sub

See comments and play with it.
 
@arnelgp - THANK YOU VERY MUCH. This is now working as it should and the end users are VERY happy!

Thank you to everyone who commented and for the time given to resolving this issue. I had spent hours and hours on this and to have it resolved is brilliant.

You guys are great.
 
UPDATE: I am leaving this thread in because it gives some insight but the solution is as simple @AHeyne describes. However, the error is very strange
see demo

VBA Conditional Formatting error because I have more than 3 formatting requirements.

Curious what @theDBguy found out. My guess it is either a bug or a bad error message where some other error is actually happening. The code looks correct to me and you should definitely be able to do this on a modern version of Access. But stepping through it, it does what you say and loads the...
www.access-programmers.co.uk

Bottom Line is you have to be exact in your expression when adding a format condition the string needs to be in this format
[ControlName]=Value
You cannot have any spaces in front or after the equal sign and must have square brackets
Set Con = Me.txtDescription.FormatConditions.Add(acExpression, , "[StatusID]=" & rs!StatusID)

Any of these will fail
'Set Con = Me.txtDescription.FormatConditions.Add(acExpression, , "[StatusID] = " & rs!StatusID)
'Set Con = Me.txtDescription.FormatConditions.Add(acExpression, , "StatusID=" & rs!StatusID)


The below is correct but not necessary. Why it works, I am not sure.
----------------------------------------------------------- Previous Post ---------------------------------------------------------------------


Facts
1. You can definitely add more than 3 (I think it is 50) format conditions in code. However you can only ADD more than 3 using the ACFIELDVALUE parameter of the first argument.
2. You can then modify all the FC to be ACEXPRESSION except for the first 3 FCs. That seems to be the bug.

Workaround
1. Determine how many conditions you need and add 3 more. These must be added using AcFieldValue and not AcExpression. The problem is if you try to add more than 3 dummy conditions with a common expression using AcExpression it will not show an error but only add 3.
2. Now starting at the third FC you can modify these to Expressions. If you start before 3 you will get the known error.
3. As far as I can tell you must put your field / control in square brackets in the expression.

Code
Code: 
Public Sub CreateExpressionFormat()
  Dim rs As DAO.Recordset
  Dim Con As FormatCondition
  Dim i As Intt
  Dim reccount As Integer
 
  Set rs = CurrentDb.OpenRecordset("tblStatus")
  rs.MoveLast
  rs.MoveFirst
  reccount = rs.RecordCount
 
 
  For Each Con In Me.txtDescription.FormatConditions
    Con.Delete
  Next Con
  'add dummy conditions + 3
  'Must add dummy conditions using the value
 
  For i = 1 To reccount + 4
    Me.txtDescription.FormatConditions.Add acFieldValue, acEqual, 1
  Next i
  Debug.Print "Format conditions added: "; Me.txtDescription.FormatConditions.Count & " Record Count " & reccount
  i = 3
 ' i = 0
  rs.MoveFirst
  Do While Not rs.EOF
   Set Con = Me.txtDescription.FormatConditions(i)
 
   With Con
     'Must have [] around the field value
     .Modify acExpression, , "[StatusID] = " & rs!StatusID
     Con.BackColor = rs!BackColor
     Con.ForeColor = rs!ForeColor
   End With
   i = i + 1
   rs.MoveNext
 Loop

End Sub

Here is proof:
1. StatusID demos what was previously said. This shows that you can add more than 3 FCs using the ACFIELDVALUE argument.
2.StatusDescription demos the work around using the ACFIELDVALUE argument but using a combobox to dispay the text
3. The third column is the actual workaround using ACEXPRESSION

Formats.png
 

Attachments

Last edited:
MajP said:
Here is the actual workaround for this bug. Everything posted on line is incomplete and no one as far as I can find has ever posted a working solution. Here is an actual working solution.

Facts
1. You can definitely add more than 3 (I think it is 50) format conditions in code. However you can only ADD more than 3 using the ACFIELDVALUE parameter of the first argument.
2. You can then modify all the FC to be ACEXPRESSION except for the first 3 FCs. That seems to be the bug.

Workaround
1. Determine how many conditions you need and add 3 more. These must be added using AcFieldValue and not AcExpression. The problem is if you try to add more than 3 dummy conditions with a common expression using AcExpression it will not show an error but only add 3.
2. Now starting at the third FC you can modify these to Expressions. If you start before 3 you will get the known error.
3. As far as I can tell you must put your field / control in square brackets in the expression.

Code
Code: 
Public Sub CreateExpressionFormat()
  Dim rs As DAO.Recordset
  Dim Con As FormatCondition
  Dim i As Integer
  Dim reccount As Integer
 
  Set rs = CurrentDb.OpenRecordset("tblStatus")
  rs.MoveLast
  rs.MoveFirst
  reccount = rs.RecordCount
 
 
  For Each Con In Me.txtDescription.FormatConditions
    Con.Delete
  Next Con
  'add dummy conditions + 3
  'Must add dummy conditions using the value
 
  For i = 1 To reccount + 4
    Me.txtDescription.FormatConditions.Add acFieldValue, acEqual, 1
  Next i
  Debug.Print "Format conditions added: "; Me.txtDescription.FormatConditions.Count & " Record Count " & reccount
  i = 3
 ' i = 0
  rs.MoveFirst
  Do While Not rs.EOF
   Set Con = Me.txtDescription.FormatConditions(i)
 
   With Con
     'Must have [] around the field value
     .Modify acExpression, , "[StatusID] = " & rs!StatusID
     Con.BackColor = rs!BackColor
     Con.ForeColor = rs!ForeColor
   End With
   i = i + 1
   rs.MoveNext
 Loop

End Sub

Here is proof:
1. StatusID demos what was previously said. This shows that you can add more than 3 FCs using the ACFIELDVALUE argument.
2.StatusDescription demos the work around using the ACFIELDVALUE argument but using a combobox to dispay the text
3. The third column is the actual workaround using ACEXPRESSION
Click to expand...
@MajP Good work! 👏 Thanks for sharing!!!
 
@MajP : That workaround by adding dummies (and in my workaround also deleting them later on) was the solution I used until I saw that it is not necessary any more when removing the spaces around the operator in the condition (see above).
Getting rid of the dummies also brought a speed up.

Edit:
I reworked the procedure 'CreateExpressionFormat' of your last uploaded sample (see comments):

Code: 
Public Sub CreateExpressionFormat()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("tblStatus")
  rs.MoveLast
  rs.MoveFirst

  Dim Con As FormatCondition
  For Each Con In Me.txtDescription.FormatConditions
    Con.Delete
  Next Con

  rs.MoveFirst
  Do While Not rs.EOF
    '// Does not work:
   'Set Con = Me.txtDescription.FormatConditions.Add(acExpression, , "[StatusID] = " & rs!StatusID)
    '// Works well:
   Set Con = Me.txtDescription.FormatConditions.Add(acExpression, , "[StatusID]=" & rs!StatusID)

   With Con
     Con.BackColor = rs!BackColor
     Con.ForeColor = rs!ForeColor
   End With

   rs.MoveNext
 Loop
End Sub
 
Last edited:
Thanks. I will edit my response.
 
UPDATE: I am leaving thread #35 because it gives some insight to the issue, but unnecessary. The solution is as simple @AHeyne describes. However, the error and error message is very strange and confusing.

see updated demo

VBA Conditional Formatting error because I have more than 3 formatting requirements.

Curious what @theDBguy found out. My guess it is either a bug or a bad error message where some other error is actually happening. The code looks correct to me and you should definitely be able to do this on a modern version of Access. But stepping through it, it does what you say and loads the...
www.access-programmers.co.uk

Bottom Line is you have to be exact in your expression when adding a format condition. The string needs to be in this format
[ControlName]=Value

You cannot have any spaces in front or after the equal sign and must have square brackets
This works
Set Con = Me.txtDescription.FormatConditions.Add(acExpression, , "[StatusID]=" & rs!StatusID)

Any of these will fail
'Set Con = Me.txtDescription.FormatConditions.Add(acExpression, , "[StatusID] = " & rs!StatusID)
'Set Con = Me.txtDescription.FormatConditions.Add(acExpression, , "StatusID=" & rs!StatusID)

In @klsblues original thread the code that failed was
With Me.JobStatusTxt.FormatConditions.Add(acExpression, , "JObStatus = " & StatusID)
It had missing brackets and had spaces.

Replacing that line with
With Me.JobStatusTxt.FormatConditions.Add(acExpression, , "[JObStatus]=" & StatusID)
Fixed the problem.
 
Last edited:
You must log in or register to reply here.

Similar threads

C
Working in 32 bit but not in 64.
Replies
19
Views
432
Catalina
C
N
VBA MS Access Error Handling
Replies
5
Views
299
Pat Hartman
P
N
VBA Code error on the counter
Replies
5
Views
207
cheekybuddha
cheekybuddha
N
Solved Caching recordset in MS Access VBA
2
Replies
26
Views
1,751
tvanstiphout
T
C
Optional Variant in Inventory Control code
Replies
7
Views
412
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

  • Total: 2 (members: 1, guests: 1)
    Back
    Top Bottom