Message Box Using DCount()

[tarcona]

I want to create a new message box saying there are no results when no results are found using the search using DCount(). I need someone to show me how to do this.

My code is:

Private Sub btnSearchContacts_Click()
    Dim strSQL As Variant
    Dim l_strAnd As String
    
    If (Me.txtSearchClientID & "" = "") And (Me.txtSearchCompany & "" = "") And (Me.txtSearchFirst & "" = "") And (Me.txtSearchLast & "" = "") And (Me.txtSearchCompany1 & "" = "") Then
        ' If the search criteria are Null, use the whole table as the RecordSource.
        Me.RecordSource = "Accounts"
    Else
        l_strAnd = ""
        strSQL = "SELECT distinctrow Accounts.* " _
            & "FROM Accounts INNER JOIN ClientInformation " _
            & "ON Accounts.[Account ID] = ClientInformation.[Account ID] " _
            & "WHERE "
                        
        If Me.txtSearchClientID.Value & "" <> "" Then
            strSQL = strSQL & l_strAnd & "ClientInformation.[Client ID] = " & Me.txtSearchClientID
            l_strAnd = " AND "
        End If
        
        If Me.txtSearchCompany1.Value <> "" Then
            strSQL = strSQL & l_strAnd _
                & "Accounts.[Company] Like '" _
                & Me.txtSearchCompany1.Value & "*'"
            l_strAnd = " AND "
        End If
        
        If Me.txtSearchFirst.Value <> "" Then
            strSQL = strSQL & l_strAnd _
                & "ClientInformation.[First Name] Like '" _
                & Me.txtSearchFirst.Value & "'"
            l_strAnd = " AND "
        End If
        
        If Me.txtSearchLast.Value <> "" Then
            strSQL = strSQL & l_strAnd _
                & "ClientInformation.[Last Name] Like '" & Me.txtSearchLast.Value & "'"
        End If
    
        On Error Resume Next
        Me.RecordSource = strSQL
        If strSQL <= 0 Then
            MsgBox "No records matching filter." 'This indicates a problem with the code, not the matching criteria.
            Me.RecordSource = "Accounts"
        End If
    End If
End Sub

 

[pbaldy]

Handy reference here on getting record counts; second one deals with forms:

http://www.mdbmakers.com/forums/showthread.php?t=5085