IsNull and conditional formatting

[gschimek]

I've searched the forums and found what SHOULD be the answer to my problem, but it's not working for me.

I have a continuous form that lists information about individual insurance policies. One of the fields is called CancellationDate, and I want to check to see if there is a value in that field, and if so, change the color of the field, so I can easily see if the policy has been canceled. I've actually been trying to do it in reverse with the IsNull function. Here's what I've tried:

Private Sub Detail_Format()

If IsNull(CancellationDate) Then
    CancellationDate.BackColor = 255
End If

End Sub

But doing that doesn't do anything. The field just stays white, no matter if there's a value in it or not. Am I using the wrong Event? Or is there something wrong with the code? Or is there a better way to do it than to use the IsNull function?

Thanks.

 

[RexesOperator]

If you're using Access 2000 or higher, why not use conditional formatting that comes with it?

 

[gschimek]

I didn't know about that built in function. I figured out how to get that to work with one field. But what I actually want to do is highlight all the fields in that row if the one field has a value in it. From the look of it, conditional formatting can only do one field at a time.

So it seems that I have two options. I can either use the conditional formatting to alter the one field, and then use VBA to copy the formatting of that field to the other, or I can do the whole thing with VBA. If I knew how.

 

[RoyVidar]

In forms design view, select all the controls you wish to apply this conditional formatting to, select conditional formatting, chose "Expression Is" in the first drop down, Use either the Expression "[CancellationDate] Is Null" or "IsNull([CancellationDate])", without the quotes.

But - you say this is in a form. Forms do not have a Detail_Format event. Reports has.

If you are in a report, the programming should work. In a continuous form, programming wont work, as you don't really have more than one control, regardless of how many rows the form displays. What you see, is multiple instances of the same control. Change the backcolor of one programatically, it's changed on all instances of it.

 

[gschimek]

Looks good. Thanks a lot!