Don't Display queries with Zero Results

B

BFarrell

I have a number of Edit Queries that I execute as part of an automated
process. I only want to display to the user the Edit queries that have
results. I have done this in the past but cannot find the code I used to not
display queries that had no results. Can someone please point me in the
right direction.

Thanks!
 
G

Graham R Seach

You haven't done this in the past, because it isn't possible. Queries have
no inherent "smarts", which just means they can't monitor their own
resultset, and they certainly can't act on stuff. If you want to only
display a query that has results, you have to use it as the RecordSource for
a report, and use the report's NoData event.

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There's nothing to show"
Cancel = True
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
R

Roger Carlson

The only way I can think of would be to open each query as a recordset in
code, move to the last record in the set, and then check the recordcount
property. Something like this:

Dim db as DAO.Database
Dim rs as DAO.Recordset
NumRec as Long
Set db = CurrentDb
Set rs = db.OpenRecordset("MyQuery")
rs.MoveLast
NumRec = rs.RecordCount

Of course, this sample just checks one query (which I called MyQuery).
You'd either have to repeat the last three lines for each query or (better
yet) create a loop that reads through all of your edit queries and returns a
list of values for ones that have records.

The problem with this method is if the resultsets are large, it could take a
while.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
B

BFarrell

Thanks, Roger. I've crafted a similar solution sending the qryname into a
SUB for evaluation. In the SUB I am opening each query as a recordset and
evaluating for EOF. When there are no records, EOF is true and I can EXIT
SUB. If the recordset is not at EOF then I go ahead and openquery for the
user.
(Since I don't care how many records there are, this avoids slow performance
if there are a lot of results.)
 
Top