Conditionally Format All Fields via VBA 1

[Knicks]

I have a continuous form with about 20 fields. What I would like to do is conditionally format all these fields with the same rules.

Something like:

If criteria = p
make background = red

If criteria = L
make background = purple

If criteria = Z
make background = yellow

If criteria = M
make background = blue


I understand that the normal criteria through interface only allows for '3' rules, I would like about 6-7 if possible. Even if I limited it to '3' rule I would like to able to loop through my controls to set the conditional formatting rules. Nearly all controlsw would have the same rule applied.

Access 2007

 

[dhookom]

If all the controls (not fields) would be the same background color, you might be able to use one large, locked & disabled text box in the behind the 20 controls that are set to transparent.

I don't think you can set any more than 3 conditions in Access 2007.

Duane
Hook'D on Access
MS Access MVP

 

[Knicks]

Hmmm

No I was hoping to get the color in the field for that particular record - I have a ton of fields and didn't want to have to manually code using the conditional format interface.

I thought I read that through code you could trick it to do more conditions? Looping through the code with just 3 conditions for all the controls would be an improvement over having to input the code for every control.

 

[dhookom]

I think you could use code to create and set the conditional formatting while the form is in design mode. For me, it would take more time to search for or write the code than manually setting the conditions.

I think you have more conditions available in Access 2010.

Duane
Hook'D on Access
MS Access MVP

 

[Knicks]

Does that manual code get put in an area that is viewable in VBA? Then at least I could do some cutting and pasting, the same rule applies to all 20+ fields....the thing is, I have 12 forms that have 20+ fields with the SAME rules, so it would be over 200 times inputting that data. That is why I didn't want to use Access interface

 

[MajP]

Do not need to write code or select one at a time. Select them all at once. It is the same as changing any property for multiple controls. Select all the desired controls. Keep them selected and select conditional formatting.

You can only have 3 conditions at a given time. For a single form view you can fake it to look like you have more (not a whole lot of utility in my mind, because it is easier to do that in code)by changing which 3 are in play based on certain conditions.

 

[MajP]

So you have to do it only 12 times.

 

[MajP]

multiple controls conditional formatting.
The below creates alternating row color.

 

[Knicks]

Cool!

That is a serious start to making the process quicker! Thanx, that will really help