Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012

    Unanswered: Displaying No Results From A Search

    So I have the Following Code which does a search for Location from the text box txtSearch:

    Option Compare Database
    Option Explicit

    Private Sub cmdSearch_Click()

    On Error GoTo SearchButton_Error

    Dim strItem As String
    Dim strSQLSearch As String
    Dim blnSQLWhereStarted As Boolean
    Dim FieldIn As String

    strSQLSearch = ("SELECT * FROM [ItemList] WHERE Location=""" & Me!txtSearch & """")

    strItem = Nz(Me.txtSearch, "")

    If Len(strItem) > 0 Then
    FieldIn = "Item"
    If blnSQLWhereStarted = False Then
    strSQLSearch = strSQLSearch
    End If
    blnSQLWhereStarted = True
    MsgBox "Bringing you to the results"
    DoCmd.OpenForm "SearchForm"
    With Forms!SearchForm
    .RecordSource = strSQLSearch
    .Visible = True
    End With
    ElseIf strItem = "" Then
    MsgBox "Please Fill in the Item you want to search for"
    DoCmd.GoToControl "Location"
    End If

    Exit Sub

    MsgBox Err.Description
    Resume SearchButton_Exit

    End Sub

    My question being how can I display a Message Box displaying "No Results Found" If when running the strSQLSearch it ends up finding no results from the table?
    Last edited by DrewMan; 05-09-12 at 18:18.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    You can use a domain function (ideally DCount()), or you can use:
        If CurrentDb.OpenRecordset("SELECT * FROM [ItemList] WHERE Location=""" & Me!txtSearch & """").BOF = True Then
            MsgBox "No Results Found"
            ' Open the Form "SearchForm"
        End If
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts