Please use code tags (highlight code and click the # button)
I've redone it here to make it readable
Code:
Private Sub cboStatus_AfterUpdate()
Dim MyFilter
MyFilter = ""
Me.Filter = ""
Me.FilterOn = False
MsgBox (Me.cboStatus)
Select Case Me.cboStatus.Value
Case "Pending Review"
If MyFilter = "" Then
MyFilter = "AuditCompletedBy = ''"
Else
MyFilter = MyFilter & " AND AuditCompletedBy = ''"
End If
Case "Completed"
If MyFilter = "" Then
MyFilter = "AuditCompletedDate Is Not Null"
Else
MyFilter = MyFilter & " AND AuditCompletedDate Is Not Null"
End If
Case Else
End Select
MsgBox (Me.cboStatus.Value)
MsgBox (Me.txtCompletedBy.Value)
If Len(MyFilter) > 0 Then
Me.Filter = MyFilter
Me.FilterOn = True
Else
Me.FilterOn = False
End If
Me.Refresh
End Sub
have you tried testing for null rather than a zls (zero length string)?
Code:
Case "Pending Review"
If MyFilter = "" Then
MyFilter = "AuditCompletedBy is null
or
Code:
Case "Pending Review"
If MyFilter = "" Then
MyFilter = "nz(AuditCompletedBy,'') = ''"
and I don't think it is opening a new blank record, I'm guessing your form is set to allow additions and what is happening is you are filtering out all the records because none of them have a zls