count records on form

  • Thread starter Thread starter Katrina
  • Start date Start date
K

Katrina

I would like to open a form to a filtered set of data.

However, before the form actually opens, I want to see if
there are even any records in it. If not, I don't want to
open the form.

I know I would use an IF statement, but I don't know what
I would use after IF... Any suggestions?

Thanks,
Katrina
 
try this:
Paste this on a module:
Public function HasRecordes(Table_Or_Qry_Or_Sql as string) as boolean
on error resume next
dim db as dao.database
dim rs as dao.recordset
set db = dbengine(0)(0)
set rs = db.openrecordset(table_or_qry_or_sql)
HasRecords = not (rs.eof and rs.bof)
set rs = nothing
set db = nothing
end function

now you can call that from anywhere you want.
for example if your form's (frmtmp) record source is tmpTable:

Sub testthis()

if hasrecords("tmpTable") then
docmd.openform "frmTMP"
end if

' or

if harecords("select tmptable.* from tmptable;") then
docmd.openform "frmTMP"
end if


' or

if harecords("select tmptable.* from tmptable where tblkey = 12;") then
docmd.openform "frmTMP"
end if

End Sub
 
Back
Top