How do I test for no records returned in code?

  • Thread starter Thread starter Kate
  • Start date Start date
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
 
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
 
Try checking the record count in the recordse set.
RecordCountVatriable = recordsetname.recordcount
 
Back
Top