trying to delete parent and child records

[kronar30]

This is my code, as I step through line by line, at the JBrec.Delete line it jumps directly to the deleteRecordsError error routine?
The "Ready to delete" message shows proper record to delete
I do have records in 3 other child tables linked to this delBid
I thought I had the delete working at one point.

Private Sub cmdDeleteRecords_Click()
'Goes directly to deleteRecordsError on JBrec.Delete
'CAN'T GET THE REQUERY FORM TO WORK

Dim cnt As Integer
Dim delBid As String
On Error GoTo deleteRecordsError
Stop
Set db = CurrentDb()
Set JBrec = db.OpenRecordset("JB_Jobs", dbOpenDynaset)

'delete this job and all related records
delstrSql = "JB_Deletesw = true and JB_JoborBid = false"
JBrec.FindFirst delstrSql

Do While Not JBrec.NoMatch
delBid = JBrec(0)
MsgBox " Ready to delete bid " & strQuote & delBid & strQuote
JBrec.Delete

MsgBox "Deleted " & strQuote & delBid & strQuote & " Bid record"
JBrec.FindFirst delstrSql
If JBrec.NoMatch Then
MsgBox "findnextfirst Bid is no match"
Requery
End If
Loop
If JBrec.NoMatch Then
MsgBox "There are no Bid records to delete"
Call Form_Close
End If

Exit Sub

deleteRecordsError:
MsgBox "deleteRecordsError"
End Sub
Private Sub OLDcmdDeleteRecords_Click()
'CAN'T GET THE REQUERY FORM TO WORK
Dim cnt As Integer
Dim delBid As String
On Error GoTo deleteRecordsError
Stop
Set db = CurrentDb()
Set JBrec = db.OpenRecordset("JB_Jobs", dbOpenDynaset)
Set JBTrec = db.OpenRecordset("JBT_EmpTime", dbOpenDynaset)
Set JBMrec = db.OpenRecordset("JBM_JobMatList", dbOpenDynaset)
Set JBOrec = db.OpenRecordset("JBO_JobOverheads", dbOpenDynaset)

'delete this job and all related records
delstrSql = "JB_Deletesw = true and JB_JoborBid = false"
JBrec.FindFirst delstrSql

Do While Not JBrec.NoMatch
delBid = JBrec(0)
'Call DeleteAllChildRecords
MsgBox " Ready to delete bid " & strQuote & delBid & strQuote


'MsgBox "Found matching Job record"
JBrec.Delete
MsgBox "Deleted " & strQuote & delBid & strQuote & " Bid record"
JBrec.FindFirst delstrSql
If JBrec.NoMatch Then
MsgBox "findnextfirst Bid is no match"
Requery
End If
Loop
If JBrec.NoMatch Then
MsgBox "There are no Bid records to delete"
Call Form_Close
End If

Exit Sub

deleteRecordsError:
MsgBox "deleteRecordsError"
End Sub

 

[JoeAtWork]

First of all, I would highly advise in your error handlers to show the actual error to the user, i.e.

MsgBox "Error: " & Err.Description

It would be really helpful if you could tell us what the error is, than we wouldn't have to guess at the problem.

However, since you did mention there are child tables, than the most likely reason is that there are child records related to the record you are trying to delete. Unless you have cascade deleting set up, then you would get an error. What you would need to do is delete all the related child records first.

My final advice is not to use recordsets to delete records. They are slow and I've seen them cause errors once the tables become very large. Use straight SQL statements instead, such as:

CurrentDb.Execute "DELETE FROM JB_Jobs WHERE JB_Deletesw = true and JB_JoborBid = false"

Notice how much less code you have to use, and it will run much faster too.