S. van Beek wrote:
Is there a VBA code to check the status of a record set, record set empty
(no records) or with records?
I do this two ways, one for when a form with an underlying
recordset/query/sql statement is opened and the other when I'm doing
operations on the recordset and the form is open.
The first, on _FIRST_ opening the form:
if forms!frmName.r ecordsetclone.e of then
'no records, do your stuff. If this is used in the on open event, set
cancel = true to avoid opeing the form.
I don't like using the recordsetclone for checking this sort of thing
when the form is open. Too much fiddling with having to make sure .move
first, etc.
I use a function like this (air code)
Function fCheckRecords() as Integer (or long if large numebrs of records
are involved)
dim strS as string
dim rst as dao.recordset
dim dbs as dao.database
'I personally don't use database variables in procedures anymore - I
use the David Fenton/Michka function see
http://tinyurl.com/8nvl3
set dbs = Access.currentd b
strS = "Select count(*) as No from <enter rest of your tables and
criteria - idea is to have a query that returns one value)
set rst = dbs.openrecords et(strs, dbopensnapshot)
fCheckRecords = rst.fields!No
rst.close
dbs.close
set rst = ntohing
set dbs = nothing
end function
This function is used like:
If fcheckRecords = 0 then <do stuff for empty recordset>
--
Tim
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me