Empty Query Causing an error in the code

[paulmcdonnell]

Hi huys,

this should be so simple but its driving me mad.

My code inspects a query to see how many entries there are.

If there are non and the query is empty I need it to ignore this and move on.

as my code is this works by causing an error and i'm using the On Error Goto
(i know this is not a good way to do it but which is the best?)

code is :
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Query_Appointments Tomorrow (PRM)")
'Count the number of appointments
'if there are non then jump to update teh table and exit this code.
'On Error GoTo UpdateforPRMDiaryCheckURGENTAPPSBROADCASTEMAIL
rst.MoveFirst
Do While Not rst.EOF
counting = counting + 1
rst.MoveNext
Loop

Any ideas would be very welcome. The only one I have come up with is to abandon the saved query all together and recreate it in the code .

Hoping for the help

Paul

 

[namliam]

so you want to know how many records there are??

rst.RecordCount

will do it much faster for you....
This will also return a simple 0 if the recordset is empty

 

[paulmcdonnell]

Thanks

God

sometimes you can feel like such an amature..

I blame the booze.

Paul

 

[RoyVidar]

Careful, now, .RecordCount needs the whole recordset to be "accessed" first to provide accurate count, so to be sure you get the correct count, you may need to issue a .movelast prior to fetching the .RecordCount. So after opening, you may want (air code)

if rst.recordcount > 0 then
    rst.movelast
    counting = rst.recordcount
else
    counting = 0
end if

 

[namliam]

This is mostly true for ODBC recordsets, internal Access recordsets return the correct numbers right away IIRC.

But your code is the best/most carefull way....

@Paul, dont worry, we all have our *DUH* moments which is why we have forums like this one

 

[RoyVidar]

I don't think you recall correct.

For recordsets based on queries, SQL statements and attached tables my recollection is that it will give the number of accessed records not the actual number of records. Look up DAO RecordCount property in the help file on , and read the Remarks section for more info.

Do also try something similar as the OP has posted, to verify (the OP is using a stored query)

dim rs as dao.recordset
set rs = currentdb.openrecordset("somestoredquery")
debug.print rs.recordcount

At least on my setups this is very consistantly showing 1, unless I specify only table name of a local table.

 

[KeithG]

I believe you are correct Roy.

 

[namliam]

well one would notice this very quickly in testing out your procedure...

I *think* I allways use something like:

If rs.eof then exit sub 'i.e. there are no records in the recordset
rs.movelast
debug.print rs.recordcount

Or replace the "Exit sub" bit with something to handle the empty recordset...