Check whether a query has "No Current Record"

[iknowkungfu]

How can I check from VBA whether a query I have created has no current record?

I'm trying to prevent my code from opening a form based on this (crosstab) query if it is empty like this. I have tried using the Report_Error event and also GoTo Err technique inside the report (On NoData event doesn't work either) and in the calling code, but they don't seem to work. I would rather be able to tell that it is blank in my calling function so that I can handle it more cleanly.

Thanks

 

[Smart]

before the call to open the form

Dim nodata as string

nodata = nz(dlookup("[yourqueries field]","[Yourqueryname]"),"")

If nodata ="" then (your query has not returned data)
stop your form from opening

end if

 

[iknowkungfu]

Excellent, thanks Smart, that worked.

 

[iknowkungfu]

Sorry, that would work if it was an ordinary query, but because it is a crosstab query and I am using the Nz function to include records where there is no data, it is still returning a value even when there are "No current records".

The only solution I can see is to get it to load the query as a recordset, and then explicitly reference a cell somehow.

 

[namliam]

rs.eof?? Are you looking for that? Determaning the end of a recordset ...
Start offcourse would be rs.Bof