I want to add a message box when there is no records found. Here is my code:
Code:
Private Sub btnSearchContacts_Click()
Dim strSQL As Variant
Dim l_strAnd As String
If (Me.txtSearchClientID & "" = "") 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
'strSQL = strSQL & " ORDER BY ???;"
On Error Resume Next
Me.RecordSource = strSQL
If Err <> 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
