Results 1 to 6 of 6
  1. #1
    adams.bria is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2010
    Posts
    22

    Question Search - If no results

    So I have a search function that work great. However, now if my search returns no results, I would like to have a pop-up that tells the user no results were found. (Ideally I would like to from the pop-up to ask them if they want to enter the user, but I don't think that is possible) Here is my code, anyone have any ideas on where to stick an if no results logic?




    Thanks in advance.




    Code:
    Private Sub cmdGo_Click()
    On Error GoTo cmdGo_Click_Err
    
    
        If (Eval("[Forms]![frmClient_Center]![searchbox] Is Null")) Then
            ' Clear Filter when search box empty
            DoCmd.RunCommand acCmdRemoveFilterSort
            DoCmd.SetProperty "cmdShowAll", acPropertyEnabled, "0"
            End
        End If
        ' Handle "'s in search
        TempVars.Add "strSearch", Replace(Forms!frmClient_Center!searchbox, """", """""")
        ' Build the Filter
        TempVars.Add "strFilter", "([client_ID] Like "" * " & [TempVars]![strSearch] & " * "" )"
        TempVars.Add "strFilter", TempVars!strFilter & " OR ([ssn] Like "" * " & [TempVars]![strSearch] & " * "" )"
        DoCmd.ApplyFilter "", TempVars!strFilter, ""
        TempVars.Remove "strFilter"
        TempVars.Remove "strSearch"
        DoCmd.SetProperty "cmdShowAll", acPropertyEnabled, "1"
    
    
    
    
    cmdGo_Click_Exit:
        Exit Sub
    
    
    cmdGo_Click_Err:
        MsgBox Error$
        Resume cmdGo_Click_Exit
    
    
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can use a DCount with your search string, and warn the user if it returns 0.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    adams.bria is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2010
    Posts
    22
    Having some trouble with the Dcount sytax. Here is what I did

    Code:
        Records = DCount(Client_ID, strfilter)
        MsgBox (Records)
    All I really care about is the client ID part, as I will be deleting the ssn logic. However, when I run this for a record that doesn't exist I get "invalid use of null" on the dcount line.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The proper syntax for a DCount is:

    DCount("FieldName", "TableName", "Criteria")

    You've left out either the field or the table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    adams.bria is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2010
    Posts
    22
    Quote Originally Posted by pbaldy View Post
    The proper syntax for a DCount is:

    DCount("FieldName", "TableName", "Criteria")

    You've left out either the field or the table.
    But I am trying not to count how many are in the table, but how many are in the string. If I switch the "tablename" part to the table, yes I can get it to return how many are in the table. What I need to know is how many are being returned in the search the person is performing. What would I put in table name in order to accomplish this? Thanks!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Does this not work?


    DCount("*", "TableName", strfilter)

    Presuming strfilter contains the appropriate filter.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. how to highlight search keywords in results form?
    By Absolute_Beginner in forum Forms
    Replies: 2
    Last Post: 08-22-2011, 04:52 AM
  2. Replies: 1
    Last Post: 07-21-2011, 01:57 PM
  3. ComboBox Search - No Results
    By cvansickle in forum Forms
    Replies: 5
    Last Post: 03-27-2011, 03:37 PM
  4. Replies: 1
    Last Post: 10-24-2010, 04:01 PM
  5. Search Form Results Displayed In Report
    By warrenjburns in forum Forms
    Replies: 1
    Last Post: 03-18-2009, 02:08 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