Turn filtered off - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Turn filtered off

Robert Freeman

3 years ago

Hi. I have a search button created after watching Richard's video and that works fine. Unfortunately when it is run by a user that has read only access and there is nothing to find it returns a blank page and, because the form is read only, will not let you manually turn the filter off.
Is there a way to display a message box "nothing found" and then turn the filter off using VBA.
The current vba script is currently as Follows:

Private Sub SearchFirstNameBtn_Click()
    Dim S As String    
    S = InputBox("Enter Search Criteria", "FirstName", "Enter First Name or Part Of First Name ?")
    If S = "" Then Exit Sub    
    Me.Filter = "FirstName LIKE ""*" & S & "*"""
    Me.FilterOn = True      
End Sub

	Adam Schwanz @Reply 3 years ago
You could just DCount to check how many records have "FirstName LIKE """ & S & """" and if there aren't any, then turn off the filter.

	Kevin Robertson @Reply 3 years ago
Here is a slightly more complicated solution that allows the user to search based on first name or last name while eliminating the need for duplicate code.

	Kevin Robertson @Reply 3 years ago

Kevin Yip @Reply

3 years ago

In VBA, a form's recordset's recordcount property gives you the number of rows in the form:

     Me.Recordset.RecordCount     ' Current form
     Me.MySubform.Form.Recordset.RecordCount     ' Current form's subform
     Forms!MyForm.Recordset.RecordCount     ' Any form
     Forms!MyForm.MySubform.Form.Recordset.RecordCount     ' Any form's subform

If you put a textbox in the form's footer with the control source =Count(*) , it will also show the form's row count, but it will not show zero when there is no record.  And it's a headache to make it show zero in that situation.  Let me know if you need this method.

	Robert Freeman OP @Reply 3 years ago
Thank you all. It's now working fine with Kevin's code.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View