Catch empty search result

[octsim]

Hey there,

I created a search form which can search in 3 different fields of a table.
I'm trying to get a Messagebox to pop up if the table does not contain anything with those search strings.
Is that possible and if yes, how?

Thanks in advance



octsim

[ItsMe]

Post the code to show us how you are searching the tables.

[octsim]

The SQL Code is this:

SELECT Tablename.Serial, Tablename.Keyword.... loads of more fields that need to be displayed
FROM Tablename
WHERE (((Tablename.Serial) Like "*" & [Forms]![SearchForm]![Serial] & "*") AND ((Tablename.Keyword) Like "*" & [Forms]![SearchForm]![Keyword] & "*") AND ((Tablename.Comment) Like "*" & [Forms]![SearchForm]![Comment] & "*"));

[ItsMe]

You can place some code in the form's load event.

Dim rcd As Recordset
Set rcd = Me.RecordsetClone
If rcd.RecordCount <= 0 Then
msgBox "Your query returned zero records"
docmd.close

Exit Sub
End If

[octsim]

That doesn't work.
I get an error in the "Set rcd = ... " line saying runtime error 7951
I guess that's because I have an unbound form.
My form has those 3 textboxes and calls a query (SQL code I have posted) when the "Search" button is pressed.
Do I have to restructure the whole search form?

[ItsMe]

Do I have to restructure the whole search form?

I don't think so.

You could include your SQL within DAO and open a recordset to get a recordcount.

Try the following. Pass your SQL to a string and then open a recordset

Dim strSQL as string
strSQL = "My SELECT Query inserted HERE"

Dim rcd As DAO.Recordset
Set rcd = CurrentDB.OPenRecordset (strSQL, dbOPenDynaset)
rcd.movelast
If rcd.RecordCount <= 0 Then
msgBox "Your query returned zero records"

End If

Set rcd = Nothing