Deleting a Form & SubForm Record



I have a form/subform that contains a "Delete" Record command button that I
created. When I click the button, the below code executes and deletes the
current main form data record and successfully deletes the sub form data.
However, after the code fires, the form remains on the current "deleted"
record and displays the "#Deleted" message in the sub form fields. The main
form still displays the deleted data values as if they still remain, even
though they are actually deleted.

If I exit the form and re-enter, the data is gone. This is strange because
the below code has worked fine on other form/sub form scenerios for me. In
the other databases, the below code deletes the data, requiries the form and
displays the next record.

Can someone tell me how I could modify the below code to requery the form
and either display the previous or next record in the recordset? I know I
could issue a form requery in my code, but it would leave the user at the
1st record. I seem to remember someone writing about using the "bookmark"
method to keep the users record to the last current record, but I've never
used the bookmark method before.

Any suggestions?

CODE : ***********************

Private Sub cmdDelete_Click()
On Error GoTo cmdDelete_Click_Err

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDelete

Exit Sub

MsgBox Error$
Resume cmdDelete_Click_Exit

End Sub

Private Sub Form_Delete(Cancel As Integer)
On Error GoTo Errorhandler

Dim Msg As String, BoxResponse As String
Dim Title As String, Style As Integer
Dim sSQL

Msg = "Are you absolutely sure you want to permanently delete this
Style = vbYesNo + vbExclamation
BoxResponse = MsgBox(Msg, Style, Title)

'Set the Cancel value (passed argument) to True or False
'Depending on the user's response.
If BoxResponse = vbYes Then
Cancel = False
DoCmd.SetWarnings False
SendKeys "{enter}", False

sSQL = "DELETE ID FROM tblSubData " & _
"WHERE mainID =Forms!f_data!mainID"


sSQL = "DELETE ID FROM tblMainData " & _
"WHERE mainID =Forms!f_data!mainID"


DoCmd.SetWarnings True
Exit Sub
DoCmd.SetWarnings False
SendKeys "{enter}", False
DoCmd.SetWarnings True
Exit Sub
Cancel = True
End If

Exit Sub


Select Case Err.Number
Case 2001, 2501 ' "Cancel" error codes
Resume Exit_Point
Case Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End Select

End Sub


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question