DAO Delete record loop not working

[pablavo]

Hi,

I've been working on this code that I'll put behind a command button of a form. The code will delete the parent records child records, using the ID text box on the form for criteria so that, only that parent records child records are deleted.

Because I wont to delete all child records I need the loop, however, the code is deleting all records from the table. Here's the code.

Dim strEmpID As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("dbo_rpdbAuditHistory", dbOpenDynaset, dbSeeChanges)


With rst
.FindFirst "[EmpDetailID]=" & Me.txtEmpDetailID 'ID text box on form

If Not .NoMatch Then
.Delete

Else
MsgBox "No records"

End If
End With
Me.Refresh

If (rst.RecordCount > 0) Then

rst.MoveFirst

Do While Not rst.EOF

rst.Delete
rst.MoveNext
Loop
End If

MsgBox "Records have been deleted.", vbInformation, "Message"

I've been told not to use Delete cascade and just to note, the tables are SQL Server tables.

I'm not sure how to write the loop code so that this works. Any help I can get will be greatful.

Thanks

 

[namliam]

1)
Please use [ code ] and [/ code ] (without the spaces) around your code, this helps to preserve the indenting making it much more readable.

2)
As to your problem

If you need to enforce referential integrity upon delete of a record...
Why not just delete the child records?
Currentdb.execute "Delete from yourtable where key = " & yourvalue

Let the DB do the work instead (action query) of your poor little old client (do while loop)

 

[pablavo]

Sorry about that, I forgot to put the code tags in.

You're right, that would be a lot easier; action query.

Thanks