Conditional Formatting through VBA: On Undo

[Remster]

Folks

I've moved all my conditional formatting to VBA because of performance issues with Access's built-in conditional formatting facility, but there's one feature I can't replicate. Some of my formatting involves highlighting fields if they're blank. This works fine for On Current and After Update, but I can't get it to work for On Undo: if I undo changes and thereby leave a field blank, I can't get the conditional formatting to kick in again. Am I missing something about On Undo, or is there another way of doing this?

Thanks

Remster

[ajetrumpet]

I don't know if you are or not bud, but could you check NULL in a field AND apply one of the conditions in the conditional formatting collection?? that's another solution for you.

[Remster]

No, that's not how I'm doing it. I've read that it would perform in the same way as the built-in conditional formatting facility (plus it looks complicated). I'm just using this sort of thing:

If IsNull(Forms!Form1!Field1.Value) Then
    Forms!Form1!Field1.BackColor = 65535
Else
    Forms!Form1!Field1.BackColor = -2147483643
End If

The problem is that it doesn't work in On Undo.

[ajetrumpet]

are you sure you're understanding exactly when the UNDO even fires?? I don't even know...

[Remster]

Yeah, I've got that bit covered. I've even discovered that I can get the format to change if I omit the condition 'If IsNull(Forms!Form1!Field1.Value)', which tells me that Access doesn't recognise the undone field as null (or empty – I've tried "" too). Oddly, though, if I navigate away from and back to the record, it is recognised as null for the purposes of the On Current event!

[Remster]

Nailed it. Rather than

If IsNull(Forms!Form1!Field1.Value) Then ...

I need

If IsNull(Forms!Form1!Field1.OldValue) Then ...

Evidently the event fires between my clicking on the Undo button and the field's being returned to its previous state.

[ajetrumpet]

wow! I don't think I helped at all. What a consulting job I did...