How to check if recordset returned is empty?

  • Thread starter Thread starter Danny
  • Start date Start date
D

Danny

I been having problems checking when a recordset is empty.
I know that if I do the following then I can use NoMatch
and know if I didn't get a return:
Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("SELECT * FROM
[Address Book]", dbOpenDynaset, dbSeeChanges)
RS.FindFirst "[Alpha Name] = '" & UCase(Me.SoldTo)
& "'"
If RS.NoMatch Then Exit Sub
......

But since the table is too big to do a findfirst I prefer
do just put one whole SQL Statement in the
openrecordset. This way it's much faster. When I try to
do the following I even if I check for RS.NoMatch it
doesn't work. It would seem NoMatch only works for when
using Findfirst.

Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("SELECT * FROM
[Address Book] WHERE [Name] = '" & UCase(Me.TextBox)
& "'", dbOpenDynaset, dbSeeChanges)

Is there some how to check if RS is empty. I tried IsNull
(RS![Name]) but I get an error saying there is no return
value to rs. Any help would be appreciated.

Thanks.
 
Hi,
Use EOF and BOF. from Help:
If you open a Recordset object containing no records, the BOF and EOF properties are set to True
 
But since the table is too big to do a findfirst I prefer
do just put one whole SQL Statement in the
openrecordset. This way it's much faster.
Click to expand...

.... and much kinder on the network.
... It would seem NoMatch only works for when
using Findfirst.
Click to expand...

Completely correct.

Of course, if you only want to know if the record exists or not, then you
can get the server to do everything:

blnItsThere = 0 < _
DCount("*", "[Address Book]", "[Name]=""" & Me!TextBox & """")

By the way, using [Name] as a field name is just a bug waiting to
happen....

Hope that helps


Tim F
 
Back
Top