How to check if recordset returned is empty?

[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.

 

[Dan Artuso]

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

 

[Tim Ferguson]

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.

.... and much kinder on the network.

... It would seem NoMatch only works for when
using Findfirst.

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