CODEWITHSUNDEEP
vbams-accessms-access-2010
Olivia Rose
Olivia Rose

Reputation: 43

Message box for search box returning no results, Access 2010

I'm new to VBA, but I wanted to create a search box for a split form with multiple fields. I started out with a Macro and then ended up converting it to VBA, so there might actually be errors in the code.

Anyways, I used the apply filter command, and I would like a message box to pop up when there are no results, but this code doesn't work:

  Private Sub cmdSearch_Click()
        On Error GoTo cmdSearch_Click_Err
        DoCmd.ApplyFilter "", "[IDTag] Like
   ""*"" & [Forms]![Main Menu]![txtSearch] & 
     ""*"" Or [Title] Like ""*"" 
   & [Forms]![Main Menu]![txtSearch] & ""*""",""

cmdSearch_Click_Exit:
Exit Sub

cmdSearch_Click_Err:
MsgBox Error$
Resume cmdSearch_Click_Exit

End Sub

Upvotes: 2

Views: 249

Answers (1)

Andre
Andre

Reputation: 27634

You can tell by the syntax coloring in your question that your string construction doesn't work. It is much easier using single quotes '. And some variables.

Private Sub cmdSearch_Click()

    On Error GoTo cmdSearch_Click_Err
    Dim sSearch As String, sFilter As String

    sSearch = "'*" & [Forms]![Main Menu]![txtSearch] & "*'"
    sFilter = "[IDTag] Like " & sSearch & " Or [Title] Like " & sSearch
    Debug.Print sFilter  ' Use Ctrl+G to see Debug output

    DoCmd.ApplyFilter "", sFilter

For showing a MsgBox when there are no result, see the linked duplicate.

Upvotes: 2

Related Questions