Conditional formatting WITH Hide Columns combination

[AlanM]

Hi All

Has anyone experienced this:

I have a datasheet as a sub-form on a main form. Because the main form is accessed from 2 different forms in the system (let's call them A and B), I hide a column on the datasheet that is not relevant when the access is from Form B. This was working fine.

Until I added a new field to the datasheet/subform with some conditional formatting (background colour change dependent on field content). Now the hide column function causes an error which locks the database. I removed the conditional formatting and the hide column functions works fine. Please note the column being hidden and the column with the conditional formatting are not the same column.

Seems the combination of hide column AND conditional formatting in the same datasheet is the problem.

Any ideas?

Cheers
Alan

 

[SteveSchapel]

Alan,

On which event are you hiding the column? If the Load event of the subform, try changing to the Open event.

If that doesn't help, then you could try doing the column-hiding code on an event on the main form, let's say its Load event.

Another alternative is to set the subform's Record Source to blank, and then use code on an event on the main form to set the subform's Record Source to a valid query.

All of these ideas are attempts to manipulate the order in which things happen. When you have conditional formatting on a subform, it is actually a bit tricky, because the subform's data loads before the main form's data, and then if the Link Master Fields / Link Child Fields properties of the subform are such that it restricts the subform's data to related records, sometimes various events on the subform such as its Current event, as well as any conditional formatting, end up being done twice.

So anyway, I feel confident there will be a solution, but exactly what will possibly require a bit of experimentation.