Multiple Record Deletion

[rookery]

I'm sure this is very simple to do but how would I delete a series of related records from a recordset. For instance, all the records that belong to a certain customer? I have entered the following code so far but the code keeps hanging as if it's stuck in a loop.

If MsgBox("Do You Want To Delete The Record?", 4, "Deletion?&quot = 6 Then
Set dbs = CurrentDb
Set MyRs = dbs.OpenRecordset("ClientNotes", dbOpenDynaset)
strCriteria = "[CustID]= " & (Me!CustID) & ""
MyRs.MoveFirst
MyRs.FindFirst strCriteria

If MyRs.NoMatch Then
Me.Text24 = ""
Me.List1 = ""
Else
MyRs.Delete

Do Until MyRs.EOF
MyRs.FindNext strCriteria
If MyRs.NoMatch Then
Me.Text24 = ""
Me.List1 = ""
Else
MyRs.Delete
End If
Loop

MyRs.Close

End If

 

[KarMac21]

Try this:

If MsgBox("Do You Want To Delete The Record?", 4, "Deletion?&quot = 6 Then

Currentdb.Execute "Delete * From ClientNotes Where CustID = " & Me("custid&quot

End If

hope this helps - Shane

 

[Craig0201]

Easiest way is to use referential integrity....delete the customer and cascading deletes happen....

Next easiest way is the SQL.....

Finally, the recordset.....and the reason it loops is that you've forgot to put in MyRS.MoveNext

Craig

 

[rookery]

Thanks for your help guys. I've never seen the SQL method before and it works a treat!! However Craig, out of curiosity, whereabouts would I have inserted the MyRs.MoveNext command? My hunch is between "MyRs.Delete" and the "End If" commands....