Remove filter if recordset not found

[Guest]

I have a form that uses a parameter query to pull a recordset. But when a
value is entered that does not exist, the form goes blank and i have to
remove the filter. I want to bind a button to a click event that will apply
the filter using the text property [of a txt box] if recordset exists, else
remove filter. The way i have it set up now is the button executes a
OpenQuery macro, and the criteria set to the text property. Can i define an
IIF statement that will accomplish something to that affect?
[bad syntax]
if select me.txtbox.text from tblA.FieldA = null, remove filter, apply filter
[/bad syntax]

 

[Allen Browne]

Remove the criteria from the query, and apply to the Filter of the form
instead. You can then test the RecordCount of the form's RecordsetClone, and
remove the filter if there were no records.

Example:

Private Sub txtbox_AfterUpdate()
Dim strWhere As String
If Not IsNull(Me.txtbox) Then
If Me.Dirty Then Me.Dirty = False 'Save first.
strWhere = "[SomeField] = """ & Me.txtbox & """"
Me.Filter = strWhere
Me.FilterOn = True
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No matches."
Me.FilterOn = False
End If
End If
End Sub

Note: If SomeField is a Number field, remove the extra quotes:
strWhere = "[SomeField] = " & Me.txtbox

If you need to filter by multiple search boxes, and respond to only the
boxes where the user enters something, see:
Search criteria
at:
http://allenbrowne.com/ser-62.html
View the code in the article, or download the sample database to see how it
works.