Create filter with vba using Is Null

Question

Me.Filter = DateSent Is Null

Me.FilterOn = True

Error: Object required. Code is event procedure on the form containing the field DateSent

Answer 1

Aha! This makes more sense now. 

Since the button code is on frmMatterScheduleSelection, the Me. refers to that form, not to the form you're trying to open.

Try changing the code to

Private Sub cmdMatterSchedule_Click()

On Error GoTo Err

    DoCmd.OpenForm "frmMatterSchedule", _

         WhereCondition:="[DateSent] IS NULL"

    DoCmd.GoToControl ("Hdr")

Exit_Err:

    Exit Sub

Err:

    MsgBox Err.Description, , "Sorry"

    Resume Exit_Err

End Sub

 and omit the Filter code altogether.

You may also need to move the GoToControl line to the Load event of frmMatterSchedule.

Answer 2

No go, John. Gives the Enter Parameter Value dialog box.

Answer 3

I'd try

Me.Filter = "[DateSent] IS NULL"

with the brackets; this is assuming that DateSent is in fact a field in the form's Recordsource. This kind of glitch is one reason to consider using control names different that the control's bound field (name the textbox txtDateSent say) to remove the ambiguity.

Answer 4

Hi Scott,

I tried that...it gives me this:

Does it have something to do with the fact that both the txtbox and control source are called DateSent?

Answer 5

Me.Filter = "DateSent = Null"

or

Me.Filter = "IsNull(DateSent)"

The Filter property is a text string.