Query shows no results

Discuss Query shows no results in the Microsoft Access Development forum on Dev Articles. Query shows no results Microsoft Access Development forum to discuss problems and solutions with this popular DBMS. Use Access to build and modify database tables, or full-featured applications.

Right, I have a form that runs off of a query. The query has a user specified criteria.
If the criteria is not found, the form shows up blank. Is it possible to have textbox appear
that says "No records found matching the search criteria".

I would use a simple command to count the records, if no records exist then display a message box. DCount("*",qryName) to count the records. Then your message box in the Open Event of your form if not already open. If the form is already open use it on the AfterUpdate event of the control that opens the query. Set the message box to cancel the event if no records are found. There are other ways, but this is the easiest one I can think of.

Dim iCount as integer

iCount = DCount("*", qryName)

If iCount = 0 Then
If Msgbox("No records found matching the search criteria")=vbOk Then
Cancel=-1
End If
End If
lwells

I would use a simple command to count the records, if no records exist then display a message box. DCount("*",qryName) to count the records. Then your message box in the Open Event of your form if not already open. If the form is already open use it on the AfterUpdate event of the control that opens the query. Set the message box to cancel the event if no records are found. There are other ways, but this is the easiest one I can think of.

Dim iCount as integer

iCount = DCount("*", qryName)

If iCount = 0 Then
If Msgbox("No records found matching the search criteria")=vbOk Then
Cancel=-1
End If
End If
lwells

-------------------------------------------------------------------------------------------------------------------------------
As the query my form was based on uses a parameter DCount didn't work (it works fine if I run a query without parameter).
The next code does work for me :

Private Sub Form_Open(Cancel As Integer) <-- the OnOpen event in the Form-properties
If Me.Recordset.RecordCount < 1 Then
If MsgBox("No records found matching the search criteria") = vbOK Then
Cancel = -1
End If
End If
End Sub