Deleting a Form & SubForm Record

[Scott]

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

cmdDelete_Click_Exit:
Exit Sub

cmdDelete_Click_Err:
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
record?"
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"

DoCmd.RunSQL sSQL


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

DoCmd.RunSQL sSQL

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

Exit_Point:
Exit Sub

Errorhandler:

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

 

[strive4peace]

Hi Scott,

after the record is deleted -->

Me.Requery


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*