Evaluating empty recordset (1 Viewer)

[jekirksey]

I have the following code to test if a recordset is empty and if so delete a record, if not a msgbox telling them it is not empty basically. It ALWAYS seems to find matches in the recordset, even if there aren't any even if I set MySQL to a constant instead of Me!lstSchedule. help.

Private Sub Label14_Click()

Dim MyDB As DAO.Database
Dim MyRecs As DAO.Recordset
Dim MySQL As String

MySQL = "SELECT * FROM tblEnrolled WHERE eScheduleID= " & Me!lstSchedule

Set MyDB = CurrentDb
Set MyRecs = MyDB.OpenRecordset(MySQL)

If MyRecs.NoMatch Then
'Deletes from Schedule if no one is enrolled
DoCmd.RunSQL "DELETE * from tblSchedule WHERE ScheduleID =" & Me.lstSchedule & ";"
Else

MsgBox "There are people enrolled in the course. Please have them Unregister and then delete the course", vbOKOnly

End If
'Refreshes list
lstSchedule.Requery

End Sub

Thanks in advance for any help.

 

[chrismcbride]

I havn't tried to use the NoMatch property of a DAO recordset, however I suspect that you are not using it correctly. I think it is meant to be used in conjunction with the Seek method.
If you have a DAO recordset that contains no records, then ...

MyRS.RecordCount

... will equal 0

Chris

 

[llkhoutx]

After setting the recordset with

set rs = . . .

Add the following test for no records:

if rs.eof and rs.bof then (no records exist)