I am attempting to colour records in a continuous form based on 5 different conditions. Conditional format only has 4 options so I wondered if there was a way using VBA. I have managed to colour a report using a series of IF's in VBA and setting it to the OnPrint option, but as far as I am aware there is not an equivalent option in forms.

Good question. I was unable to find anything, although I know there must be code for the fill/backcolor somehow.

Here is a brute force way of doing it:

Make colored rectangles in your favorite application (PhotoShop, ImageReady, MS Paint) and save them as .jpg files.

Add all of them to your form so they cover the entire form area.

Make all of them invisible, and send them all to back.

Then with your code, on the form's OnCurrent event, select the image and make it visible depending on what is in the pertinent text box or other control.

This will work. A little kludy, I know, but no one here ever accused me of being elegant. I am sure Bob or Greg can come up with something pretty. This will work till they post.

HTH


mmcdonal

I forgot to mention, make one of them visible, and all the others invisible depending on the condition, else all of them invisible.

You should use Select Case.

HTH

mmcdonal

Hi
Did you ever get a workable answer to this? I have the same situation and am totally stumped.
Liz

This solution doesn't work. You can't get different colours/images in different records of continuous forms this way.

What will happen is that because it is set to the OnCurrent Event, as soon as a particular record gets the focus, its condition statement (either If or Select) will activate in the code (OnCurrent Event) and set the formatting of all records to the same result.

It looks like the only way to do it (without buying add-on software) is to use conditional formatting and be limited to the default formatting and the additional 3 conditions.

Another sneaky way might be to have two or more fields/labels overlapping, make them "disappear" using the third condition, so that the next overlapping field satisfies the condition in one of its conditions, if that makes sense.