Deleting subform records then main form record

[Jeff H]

If I haven’t worn out my welcome here yet, I’ve got another one today.

Yesterday’s issue was about ensuring there was a complete record before saving and moving to a new record in the data-entry form, and you resolved that for me.

Today I’m setting up the ability to delete a record from this form. Here’s what I’ve got:

Code: Select all

Private Sub cmdDelete_Click()
Dim rsSub As Object
Dim rsMain As Object

On Error GoTo Err_cmdDelete_Click

Set rsSub = Me.sfrServicesProvided.Form.RecordsetClone
Set rsMain = Me.Form.RecordsetClone

'===Check for data
If rsMain.EOF And rsMain.BOF Then
    MsgBox "No data in the main form.", vbOKOnly
    GoTo Exit_cmdDelete_Click
End If

If rsSub.EOF And rsSub.BOF Then
    MsgBox "No data in the subform.", vbOKOnly
    blnNoMsg = True
    GoTo MainFormDelete
End If

'===First delete sub-form records
rsSub.MoveFirst
With rsSub
    Do While Not .EOF
        .Delete
        .MoveNext
    Loop
End With

'===Then delete the main record
MainFormDelete:
rsMain.Delete
rsMain.MoveNext

Exit_cmdDelete_Click:
Set rsSub = Nothing
Set rsMain = Nothing
Exit Sub

'===Error Notification
Err_cmdDelete_Click:
MsgBox "The following run-time error occurred:" & vbCrLf & _
    Err.Description, vbOKOnly, "ERROR"
GoTo Exit_cmdDelete_Click

End Sub

It works fine when only one record has been entered. But if I enter another record and try to delete it, I get an error message. Stepping through the code, I see that it correctly deletes any Services in the sub form, but then when it gets to the main form the error says there are related records in sfrServicesProvided.

Am I missing a line that finalizes the deletions in the subform? The removed Service entries do not appear in tblSvsProvided and I can manually delete the main form record with the form’s record selector. I can also go back to the first record and the Delete button will work fine.

- Jeff

[HansV]

When you define rsMain as Me.Form.RecordsetClone, this recordset is positioned at the first record, which is most probably not the current record in the form. So when you try to execute

rsMain.Delete

Access tries to delete the first record. To correct this, move to the current record before deleting:

'=Move to the current record of the form
rsMain.Bookmark = Me.Bookmark
'=Then delete the main record
rsMain.Delete

You can remove the line

rsMain.MoveNext

It has no effect on the form and isn't needed.

[Jeff H]

Yes! Perfect. Thank you.

- Jeff