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

[AHeyne]

@arnelgp :
I have now had access to the PC of a colleague who uses Access 2021, and it works perfectly there too.

 

[arnelgp]

I have now had access to the PC of a colleague who uses Access 2021, and it works perfectly there too.

maybe you used majp's database for test?
create new db on A2021 and create New Form there and use his code.

 

[AHeyne]

Why? Is it like this on your PC? Only @MajP s database works on your PC but no new created one?

Suggestion: Upload a database where it is not working for you. Then I will take a look into it on my PC and on my colleagues.

 

[arnelgp]

open query1 form.

 

[AHeyne]

I am confused now.
Your form 'Query1' doesn't use 'FormatConditions.Add(acExpression, ...)' at all.

Instead it adds conditions with 'Me.txtDescription.FormatConditions.Add acFieldValue, acEqual, 1'
and tries to read a fourth condition with 'Set Con = Me.txtDescription.FormatConditions(i)', what doesn't exist and so raises error 7966.

 

[arnelgp]

Instead it adds conditions with 'Me.txtDescription.FormatConditions.Add acFieldValue, acEqual, 1'

the sub came from majp's database (check his "working" db, must be made on A2010).
if you are confused, use your own code and test.
btw i also used your code before and same error occurs.

 

[AHeyne]

Sorry, @arnelgp , I tested @MajP s database already and modified it for him to show him what I mean. He agreed, all is online, you can dig inside.
Something is mixed up here.
Maybe we stop here and use our precious weekend in a different way.

 

[arnelgp]

Sorry, @arnelgp , I tested @MajP s database already and modified it for him to show him what I mean.

do you understand or not?
obviously you tested majp's db (maybe made in A2010) and it work.
now look at the db (made in A2021) i posted With majp's code, does it work for you?

 

[AHeyne]

The situation is now somewhat complicated, but there is no point in being rude.
To answer your question: yes, I understand. The code and the things I am talking/writing about.

What I'm talking/writing about is the solution I propose all the time in this thread.
It works well when I patch @MajP s database and if I create a new one. I can say so because I use this code since a longer time and never had problems.
Additionally I tested it yesterday explicitely with Access 2016, 2019 and 2021.

The sample you uploaded lastly doesn't use this code approach. It works different. So obviously something went wrong in the communication here.

Since I could just repeat myself here, these are things you need to sort out for yourself, not me, sorry.

 

[arnelgp]

The sample you uploaded lastly doesn't use this code approach. It works different.

majp's db has this Public sub:

Public Sub CreateExpressionFormat()

that will add CF to txtDescription Textbox.
the same sub I copied to my Access 2021 db to add CF.
what i am trying to say here is that the db majp made (maybe using A2010), works.
i tested it on my A2021 and it works.

but when you create New Db in A2021 and incorporate the code. it will not work
on a Db created in A2021. Why, i don't know.

case dismissed.

 

[arnelgp]

The sample you uploaded lastly doesn't use this code approach. It works different. So obviously something went wrong in the communication here.

ok, i will upload new db Made In A2021 with Your Code (post #35).

 

[AHeyne]

Thanks for your upload, which makes it much more clear now. I took a look into your sample of #53.

These are the things I sorted out:

1. Your initial cleaning loop

For Each Con In Me.txtDescription.FormatConditions
    Con.Delete
Next Con

does not delete all existing format conditions.

You can test it with

Debug.Print Me.txtDescription.FormatConditions.Count

after the loop executed.

Instead you can use this to delete all format conditions:

Me.txtDescription.FormatConditions.Delete

I reworked that in the code.

2. You are referencing a field StatusID in your condition, which doesn't exist in the recordsource of your form (query Query1).
I reworked the condition to use JobStatus instead.

See uploaded sample, where it works well now.

Edit: Optimized the view of the code in this posting

 

[arnelgp]

ok i created new db and again incorporate your code
and it goes well.

thanks.

 

[AHeyne]

You're welcome.
Just for info: The database I uploaded has been DEcompiled/compacted twice before uploading to avoid any trouble with precompiled modules.