Display msgbox when search did not find any match

[spacerocket]

How do I display a message box if a search does not have any match? I got
this code that works, but if the search doesn't match, it shows a filtered
form which is blank. Thanks a lot.

If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "Please select a field to search."

ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "Please enter a search string."

Else
strWhere = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
Form_F_Contacts_NoEdit.RecordSource = "select * from Contacts where
" & strWhere
Form_F_Contacts_NoEdit.Caption = "Contacts (" & cboSearchField.Value
& " contains '*" & txtSearchString & "*')"
DoCmd.Close acForm, "F_Search"
MsgBox "Search completed."
End If

 

[Carl Rapson]

You might try using your SQL string in a DCount before setting the form's
record source. If the DCount returns 0, you can then show the message box
without setting the record source.

Carl Rapson

 

[spacerocket]

Hello, I've read on how to use DCount from this forum and tried out some
codes, which don't work. Appreciate if you could help to point out where I
missed out. Thanks a lot.

strWhere = "CompanyName LIKE '*" & txtSearchCompany & "*'"
If DCount("*", "Contacts", "strWhere") < 1 Then
MsgBox " Search did not find any result."
Else
Form_F_Contacts_NoEdit.RecordSource = "select * from Contacts where " &
strWhere
MsgBox "Search completed. Contacts has been filtered according to the
search value."

 

[Gijs Beukenoot]

spacerocket heeft ons zojuist aangekondigd :

Hello, I've read on how to use DCount from this forum and tried out some
codes, which don't work. Appreciate if you could help to point out where I
missed out. Thanks a lot.

strWhere = "CompanyName LIKE '*" & txtSearchCompany & "*'"
If DCount("*", "Contacts", "strWhere") < 1 Then
MsgBox " Search did not find any result."
Else
Form_F_Contacts_NoEdit.RecordSource = "select * from Contacts where " &
strWhere
MsgBox "Search completed. Contacts has been filtered according to the
search value."

Remove the quotes around the strWhere and try again

 

[spacerocket]

Oh! It works Thanks

spacerocket heeft ons zojuist aangekondigd :

Remove the quotes around the strWhere and try again