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

[MajP]

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.

 

[klsblues]

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.

I agree. I’ve spent so long trying different things but to no avail. It will be interesting to see what DBguy has done.

 

[Gasman]

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.

My interpretation from the link, was that for expression, you need to create placeholders and then modify those?

 

[MajP]

I tried doing the dummy FC and yes you can load more. But as soon as I try to modify more than 3 I get the same error.
FYI if anyone shows a working example using AcValue instead of AcExpression just disregard. This bug does not occur if using the AcValue and you can look at my demo to see that.

This error is really strange.
Here I load the dummy

With Me.JobStatusTxt.FormatConditions.Add(acExpression, , "statusID = " & StatusID)
       
       ' .BackColor = BackColor
       ' .ForeColor = ForeColor
       
     End With

which works if I do not try to add color formatting

But when I simply loop them after creating them

 For i = 1 to Me.JobStatusTxt.FormatConditions.Count
     Dim con As FormatCondition
     Set con = Me.JobStatusTxt.FormatConditions(i)
   
   Next i

The above errors on 3rd loop. I am bewildered on that.
I was planning to try to modify, but cannot even get a reference.

 

[theDBguy]

I agree. I’ve spent so long trying different things but to no avail. It will be interesting to see what DBguy has done.

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.

 

[Gasman]

I *think* you have to work from the last to the first.?

 

[MajP]

I *think* you have to work from the last to the first.?

I tried that also without luck.