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.
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.
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?
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.
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.
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
@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.
The correct format provided by @AHeyne fixes the problem. This is not a bug but a required formatting. The only bug would be that it kind of works for three conditions without error thus causing confusion.
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 file demos a few things.
1. The StatusID field shows that easy to add more than 3 using the field value.
2. The Status description combob box shows that you can add more than 3 conditions in a combobox using the field value
3. The Fix Using Proper Format shows that using the proper syntax works for more than 3 in an expression
4. The last column shows a workaround that was identified in on other forums about adding dummy conditions and then modifying them. Not sure if there is really a need for this ever, but it demos how it would be done.
Also demos on other forms 26 format conditions, and using the OnPaint event to do formatting.
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
@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
Wait, that is completely pointless! No one had a problem with adding three or less format conditions. The problem was more than three. In order to add more than 3 format conditions you need to ensure that you have the correct syntax. That means square brackets and no spaces. Proving that it works with spaces and no brackets for three or less conditions is meaningless.
If you get rid of this code that limits it to three conditions, your code fails as expected. Which again is the whole point.
Code:
If i > 3 Then
Exit Do
End If
Then add the brackets and remove the spaces and it works for more than 3 conditions which again is the whole point.
Code:
With rs
.MoveFirst
Do Until .EOF
i = i + 1
'If i > 3 Then
' Exit Do
'End If
' see the Expression below, there is a Space!
Set cf = tBox.FormatConditions.Add(acExpression, , "[JobStatus]=" & !StatusID)
With cf
.BackColor = rs!BackColor
.ForeColor = rs!ForeColor
End With
.MoveNext
Loop
.Close
End With
however i tried to import your code on A2021, still it will not allow me to Add more than 3 CF using Expression.
i don't know which version are you using or the OP has (maybe same as mine).
@MajP :
Thank you for your approval and review. I was not aware of the fact regarding square brackets, as I have always used them there.
@arnelgp :
It would be very interesting to find out whether it shouldn't work like this under Access 2021.
I am currently using Access 2016 and have just been able to test it successfully with Access 2019.
However, I also have access to Access 2021 and will test this myself later today.
Has anyone who has provided the solutions which I am about to use (thanks) used the code to also apply formatting as well as the colours? eg, underlines and italics etc which I use when pre-applying CFs at design stage. I'll obviously find out when I try it but was just interested.
*** IGNORE This post -tried it on MajP example DB - works perfectly ***
Thanks for sharing!!!