Is Null Conditional Formatting - MS Access Forms

Question

Something that I would assume comes up a lot...

I'd like to know if there's a way to, in Access' Conditional Formatting, format all blank fields. In my case, all fields generally need to be entered, but not in all cases. So, instead of writing a bunch of conditional code to restrict the user to writing it in there, I just want some red backgrounds in my fields as a reminder "hey, there's nothing in here.. sure that's what you wanted?"

It's on a tablet so Message Boxes would be annoying. So conditional formatting it is. I know you can have "Is Null([Field]) but that requires me to go through my 20+ forms on 30+ fields and ensure proper field names etc, then type the condition for them individually. Is there a way I can simply multi-select my fields, do a conditional format on Multiple, and use maybe "Is Equal To: NULL"?

I've tried "equal to: Null" and it doesn't work.. nor does "equal to: "" " (using the Access constants). Ideas why? Or how I can get around this? Also, it's only necessary for non-touched fields, so if the user starts to type then deletes back to blank, I don't care; it can stay unformatted or go back to red, so if there's a better way to do this I'm all eyes.

EDIT: I've started doing some VBA code which I will paste into all my forms:

Private Sub Form_Load()
Dim ctl As Control
Dim reqCol As Long
Dim focusCol As Long
Dim doneCol As Long
Dim format As FormatCondition

reqCol = RGB(246, 180, 180)
focusCol = RGB(252, 249, 238)
doneCol = RGB(255, 255, 255)

For Each ctl In Me.Controls
    With ctl
        Me.Controls(ctl.Name).FormatConditions.Delete 'Delete the existing conditions.
        Me.Controls(ctl.Name).BackColor = doneCol 'Set the background color to the done color.

        Select Case .ControlType
            Case acTextBox
                'Create the format objects.
                format = Me.Controls(ctl.Name).FormatConditions.Add(acFieldValue, acEqual, "")
                format = Me.Controls(ctl.Name).FormatConditions.Add(acFieldHasFocus)

                'Format the filled in boxes (ie set back to red)
                With Me.Controls(ctl.Name).FormatConditions(0)
                    .BackColor = reqCol
                    .Enabled = True
                End With

                'Format the current field color (ie set to beige)
                With Me.Controls(ctl.Name).FormatConditions(1)
                    .BackColor = focusCol
                    .Enabled = True
                End With
        End Select
    End With
Next ctl
End Sub

Problem is that FormatConditions.Add(acFieldValue, acEqual, "") doesn't work for the same reason... how do I get around this? Seeing as VBA and the built-in conditions are both flawed, seems like a bug. Or I'm missing something right in front of me..

Answer 1

In Access 2016 I was unable to find the default formatting option that is the solution provided by @SeanC. Instead I found that to get my Combo Box to format properly I had to use an Expression with ISNULL.