How do I test for no records returned in code?

K

Kate

In a macro I have a select statement

SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,
AVAILABILITY.Room, AVAILABILITY.Day, AVAILABILITY.BookingID

WHERE AVAILABILITY.BookingDate >= EndDate

How do I test for no records returned?

Is there some variable set that I can test?

Thanks
 
N

Nikos Yannacopoulos

Kate,

Here's one way to do it:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL as String

strSQL = "SELECT AVAILABILITY.BookingDate, AVAILABILITY.Period,"
strSQL = strSQL & " AVAILABILITY.Room, AVAILABILITY.Day,
AVAILABILITY.BookingID"
strSQL = strSQL & " WHERE AVAILABILITY.BookingDate >= " & EndDate

Set db = CurrentDB()
Set rst = db.OpenRecordset(strSQL)

On Error GoTo No_Rec
rst.MoveLast
On Error GoTo 0

'Code to do what you want

No_Rec:
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

You will need to add the Microsoft DAO reference ( 3.51 for A97, 3.6 for A2K
or later) if you don't already have it. To do so go to menu item Tools >
References from the VB editor window, scroll down to find the Microsoft DAO
reference and click the tickbox next to it.

HTH,
Nikos
 
G

Guest

Try checking the record count in the recordse set.
RecordCountVatriable = recordsetname.recordcount
 
Top