Results 1 to 9 of 9
  1. #1
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212

    Arrow How to remove filter when the search critieria match no record

    I need to add something to this code, and cannot find the right place. When the search criteria does not find a match, the results form goes blank, (I think because there are no records), if I remove the filter on the ribbon, my form comes back with all records, which is the behavior that I want. But I do not know where to put that instruction or the correct syntax.


    Function FastSearch(SourceForm$, ResultsForm$)
    'Compile and execute a search using the criteria in SourceForm$
    'and displaying the results in ResultsForm$.
    'SourceForm$ is closed before ResultsForm$ is opened.
    'Only controls in SourceForm$ with the tag Search are used in
    'the search. These controls must have a name identical to the
    'coresponding field in the database.
    'NOTE: The OpenArgs property of the results form will be set to
    'Search by this code alowing a form to determine how it was opened.

    On Error Resume Next 'Disable error messages

    SearchTag$ = "Search"
    SearchFilter$ = ""

    For ControlNumber% = 0 To Forms(SourceForm$).Count - 1
    If Forms(SourceForm$)(ControlNumber%).Tag = SearchTag$ Then
    'We are interested in this control. What does it contain?
    If Not (IsNull(Forms(SourceForm$)(ControlNumber%))) Then
    If (Err = 0) Then
    'It's not empty. Add an AND to the search filter
    If SearchFilter$ <> "" Then SearchFilter$ = SearchFilter$ + " And "
    'Does it contain a wildcard?
    If InStr(Forms(SourceForm$)(ControlNumber%), "*") > 0 Then
    'Yes, there's a wildcard here


    SearchFilter$ = SearchFilter$ + "([" & Forms(SourceForm$)(ControlNumber%).Name & "] Like """ & Forms(SourceForm$)(ControlNumber%) & """)"
    Else
    'No wildcard. Look for an exact match
    SearchFilter$ = SearchFilter$ + "([" & Forms(SourceForm$)(ControlNumber%).Name & "] = """ & Forms(SourceForm$)(ControlNumber%) & """)"
    End If
    End If
    End If
    End If
    Err = 0
    Next ControlNumber%
    DoCmd.Close A_FORM, SourceForm$ 'Close the search criteria form
    DoCmd.OpenForm ResultsForm$, , , SearchFilter$, , , SearchTag$ 'Open the search results form
    Forms(ResultsForm$)("FilterSQL") = SearchFilter$ 'Make Filter available to other objects
    Err = 0


    End Function
    I have tried to insert code like this, but cannot make it work.....

    With CodeContextObject
    'The results form is opening.
    If (Not (IsNull(.OpenArgs)) And RecordCount = 0) Then
    'If no matching records, display message
    Beep
    MsgBox "No records match your search criteria.", vbInformation, "No Matching records"
    'Operates Cancel Button if no matching records
    DoCmd.OpenForm MainScreenNew, , , FilterOn = Off
    End If
    End With
    Any help would be appreciated.

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Before you open the form ResultsForm, do a DCount or a DLookup using SearchFilter as your criteria. If the result is 0 then do what you want when the search returns no records.

  3. #3
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212
    Thanks for your response. I finally understand why I must use the SearchFilter$, but maybe I have other syntax wrong?

    I put the code below in after DoCmd.Close A_Form, SourceForm$ and before DoCmd.OpenForm Resultsform$,,,SearchFilter$,,,SearchTag$

    If DCount"[Titleid]", "SearchFilter$") = 0 Then
    Beep
    MsgBox "No records match your search criteria.", vbinformation, "No Matching records"
    DoCmd.Close acform, MainScreenNew
    DoCmd.OpenForm MainScreenNew, acNormal
    DoCmd.ShowAllRecords
    End If

    I now see the message for all three conditions I am testing for. 1. A search that finds something, 2. a search that doesn't find anything 3. Back out of search form without entering any criteria

    Whenever, the search finds nothing, I end up with one new record, not back where I started which is where I would like to be.

    If I remove the filter, using the ribbon, I'm back where I want to be.

    I close and open MainScreenNew because it is always open and easily loses focus when other screens/forms are opened from it.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The syntax is
    DCount ( expr , domain [, criteria] )


    There is no domain in yours so the dcount has no place to look.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It looks like the code in Post #1 was written for Access 2.

    I would suggest re-writing the code to least Access 2000 standards.


    Good luck with your project....

  6. #6
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212
    Thanks for all comments, really helped me solve this problem.

    "By George, I think I've got it" , but as I only tinker with code, and do not write it, if you see any lurking problems, I would be glad to hear about them.


    Option Compare Database 'Use database order for string comparisons
    Option Explicit
    Function FastSearch(SourceForm, ResultsForm)
    'Compile and execute a search using the criteria in SourceForm$
    'and displaying the results in ResultsForm$.
    'SourceForm$ is closed before ResultsForm$ is opened.
    'Only controls in SourceForm$ with the tag Search are used in
    'the search. These controls must have a name identical to the
    'coresponding field in the database.
    'NOTE: The OpenArgs property of the results form will be set to
    'Search by this code alowing a form to determine how it was opened.

    On Error Resume Next 'Disable error messages
    Dim Searchtag As String
    Dim SearchFilter As String
    Dim ControlNumber As Integer
    Dim MainScreenNew As Form
    Dim Recordset As Recordset
    Searchtag = "Search"
    SearchFilter = ""

    For ControlNumber = 0 To Forms(SourceForm).Count - 1
    If Forms(SourceForm)(ControlNumber).Tag = Searchtag Then
    'We are interested in this control. What does it contain?
    If Not (IsNull(Forms(SourceForm)(ControlNumber))) Then
    If (Err = 0) Then
    'It's not empty. Add an AND to the search filter
    If SearchFilter <> "" Then SearchFilter = SearchFilter + " And "
    'Does it contain a wildcard?
    If InStr(Forms(SourceForm)(ControlNumber), "*") > 0 Then
    'Yes, there's a wildcard here
    SearchFilter = SearchFilter + "([" & Forms(SourceForm)(ControlNumber).Name & "] Like """ & Forms(SourceForm)(ControlNumber) & """)"
    Else
    'No wildcard. Look for an exact match
    SearchFilter = SearchFilter + "([" & Forms(SourceForm)(ControlNumber).Name & "] = """ & Forms(SourceForm)(ControlNumber) & """)"
    End If
    End If
    End If
    End If
    Err = 0
    Next ControlNumber
    DoCmd.Close A_FORM, SourceForm 'Close the search criteria form
    If DCount("Titleid", "QMainScreenNew", SearchFilter) = 0 Then
    Beep
    MsgBox "No records matched the search criteria", vbOKOnly, "No Records Found"
    DoCmd.Close acForm, MainScreenNew
    DoCmd.OpenForm MainScreenNew
    DoCmd.ShowAllRecords
    Screen.ActiveForm.Recordset.MoveLast
    GoTo Line1
    End If
    DoCmd.OpenForm ResultsForm, , , SearchFilter, , , Searchtag 'Open the search results form
    Screen.ActiveForm.Recordset.MoveLast
    'DoCmd.GoToRecord acDataForm, MainScreenNew, acLast
    Forms(ResultsForm)("FilterSQL") = SearchFilter 'Make Filter available to other objects
    Err = 0
    Line1: Exit Function

    End Function
    Last edited by Abacus1234; 11-21-2016 at 08:18 AM.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    First a note: If you use the tags [ CODE ] and [ /CODE ] (without the spaces) instead of the quote tags, you can enter code and retain the indents.
    To use the CODE tags, click on the # mark in the menu or type them in like above but without the spaces.

    Here is my modifications to the code:
    Code:
    Function FastSearch(pSourceForm As String, pResultsForm As String)
        'Compile and execute a search using the criteria in SourceForm$
        'and displaying the results in ResultsForm$.
        'SourceForm$ is closed before ResultsForm$ is opened.
        'Only controls in SourceForm$ with the tag Search are used in
        'the search. These controls must have a name identical to the
        'coresponding field in the database.
        'NOTE: The OpenArgs property of the results form will be set to
        'Search by this code alowing a form to determine how it was opened.
    
        On Error Resume Next    'Disable error messages  --- actually this ignores errors - not really a good thing to do. An Error handler would be best
    
        Dim Searchtag As String
        Dim SearchFilter As String
        Dim ControlNumber As Integer
        '    Dim fMainScreenNew As Form   '<< declared as a Form but never used
        Dim sMainScreenNew As String   '<< now declared as a string but never initialized. What is/should be the name of the MainScreenNew?
    
        '  sMainScreenNew = ""
        Searchtag = "Search"
        SearchFilter = ""
    
        For ControlNumber = 0 To Forms(pSourceForm).Count - 1
            If Forms(pSourceForm)(ControlNumber).Tag = Searchtag Then
                'We are interested in this control. What does it contain?
                If Not (IsNull(Forms(pSourceForm)(ControlNumber))) Then
                    If (Err = 0) Then
                        'It's not empty. Add an AND to the search filter
                        If SearchFilter <> "" Then
                            SearchFilter = SearchFilter & " And "
                        End If
                        'Does it contain a wildcard?
                        If InStr(Forms(pSourceForm)(ControlNumber), "*") > 0 Then
                            'Yes, there's a wildcard here
                            SearchFilter = SearchFilter & "([" & Forms(pSourceForm)(ControlNumber).Name & "] Like '" & Forms(pSourceForm)(ControlNumber) & "')"
                        Else
                            'No wildcard. Look for an exact match
                            SearchFilter = SearchFilter & "([" & Forms(pSourceForm)(ControlNumber).Name & "] = '" & Forms(pSourceForm)(ControlNumber) & "')"
                        End If
                    End If
                End If
            End If
            Err = 0
        Next ControlNumber
    
        DoCmd.Close acForm, Me.Name, pSourceForm     'Close the search criteria form
    
        If DCount("Titleid", "QMainScreenNew", SearchFilter) = 0 Then
            Beep
            MsgBox "No records matched the search criteria", vbOKOnly, "No Records Found"
            DoCmd.Close acForm, sMainScreenNew   '  << should be the Name of a form but variable never initialized
            DoCmd.OpenForm sMainScreenNew        '  << should be the Name of a form variable never initialized
            DoCmd.ShowAllRecords
            '        Screen.ActiveForm.Recordset.MoveLast
            DoCmd.GoToRecord , , acLast
        Else
            DoCmd.OpenForm pResultsForm, , , SearchFilter, , , Searchtag   'Open the search results form
            '        Screen.ActiveForm.Recordset.MoveLast
            DoCmd.GoToRecord , , acLast
    
            Forms(pResultsForm)("FilterSQL") = SearchFilter    'Make Filter available to other objects
    
            Err = 0
        End If
    
    
    End Function
    I added prefixes to some of the variables. It makes it easier to keep track of the variables. "p" is for parameter.

    The + operator can be used to concatenate strings. However, to eliminate ambiguity, you should use the & operator instead.
    If the components of an expression created with the + operator include both strings and numerics, the arithmetic result is assigned. If the components are exclusively strings, the strings are concatenated.

  8. #8
    Abacus1234 is offline Competent Performer
    Windows 8 Access 2013 32bit
    Join Date
    Nov 2011
    Posts
    212
    Thanks again for the rewrite and the comments. I shall save a day over the holidays to update my code and maybe add some error handling modules throughout. My project is about ready to be unveiled, but it is likely that the law it is based on will be changed early in 2017 so maybe I'll have to start over. Still all good fun. Once again, thanks for your help.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Hope it helps......

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-18-2015, 01:29 AM
  2. Query critieria on Sum of Record
    By ice051505 in forum Queries
    Replies: 2
    Last Post: 03-08-2013, 05:20 PM
  3. Remove filter
    By jalb in forum Forms
    Replies: 6
    Last Post: 01-25-2012, 02:56 PM
  4. How do I remove a filter
    By cowboy in forum Forms
    Replies: 7
    Last Post: 04-21-2010, 10:13 AM
  5. Remove Filter and GoToRecord
    By ombadboy in forum Programming
    Replies: 0
    Last Post: 09-04-2009, 07:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums