Best way to test for no records in query

[Design by Sue]

I have a form that allows the user to select a date range, then click a button to open a report that shows the results based on the user input. I need to add VBA to check for no results in the query/report and give a message box telling there are no records available. I tried adding code to the NoData Event but it was not triggered by the code. From my research it looks like a count function might work but I can't seem to find how to set up the VBA. What is the best way to check for no records and add a message box? or is DCount a possibility?

Thanks

 

[arnelgp]

use DCount() function against your table/query:

If Dcount("*","yourQuery","DateField = #" & [dateTextboxOnForm] & "#") = 0 Then
' no record was returned
Else
'open the report here
End If

 

[Gasman]

I have a form that allows the user to select a date range, then click a button to open a report that shows the results based on the user input. I need to add VBA to check for no results in the query/report and give a message box telling there are no records available. I tried adding code to the NoData Event but it was not triggered by the code. From my research it looks like a count function might work but I can't seem to find how to set up the VBA. What is the best way to check for no records and add a message box? or is DCount a possibility?

Thanks

Cannot think why?

{code]
Private Sub Report_NoData(Cancel As Integer)
MSGBOX "No data to show"
End Sub
[/code]

QryNoData

SELECT TestTransactions.*, TestTransactions.ID
FROM TestTransactions
WHERE (((TestTransactions.ID)=0));

 

[jdraw]

Richard Rost has a current youtube video on this subject. See this

 

[Design by Sue]

Thanks all - just got back in - will check these suggestions and let you know what I get to work

 

[The_Doc_Man]

I need to add VBA to check for no results in the query/report and give a message box telling there are no records available.

The way you asked this makes me think you want the report itself to detect this. Seems to me that if this query is the result of a bound query to a form or report, there will be the .Recordset and there will also be its silent partner, the .RecordSetClone, which is an "ordinary" recordset. You should be able to test for .RecordSetClone.EOF and .RecordSetClone.BOF both being true at the same time - which means "no records." Maybe try testing that?

And if the query in question ISN'T bound but is just a free-standing recordset query, then its .BOF and .EOF would both be set at the same time. In either case, once the query in question has been run, those two properties would be immediately available.

If there IS no recordset then I would need to know more about its context, but a DCount as suggested earlier would do the trick externally to the report - which probably would be the preferable choice in terms of overhead. I.e. why bother to open something if you can know that it would be pointless to do so?

 

[Design by Sue]

use DCount() function against your table/query:

If Dcount("*","yourQuery","DateField = #" & [dateTextboxOnForm] & "#") = 0 Then
' no record was returned
Else
'open the report here
End If

This is exactly what I was looking for - thank you for providing the code to work with!

Sue

 

[Design by Sue]

This is exactly what I was looking for - thank you for providing the code to work with!

Sue

I thought that would work but the query is based on a date range from the form so I can't use the DateField on the form to compare to that in the query because there may not be a date that matches either the start or end date on the form.

Going to try the nodata again - I think I had it on the form not the report.

 

[Design by Sue]

Yep - I was putting the nodata on the form not the report. I added aDoCmd.Close acReport, "Report" to close the report and return to the form.

Again I really appreciate your help

 

[Gasman]

I thought that would work but the query is based on a date range from the form so I can't use the DateField on the form to compare to that in the query because there may not be a date that matches either the start or end date on the form.

Going to try the nodata again - I think I had it on the form not the report.

I believe you can use exactly the same criteria for the DCount() ?

 

[Design by Sue]

I am sure that I could but the nodata was easy!