problem deleting current record (Access 2003)

[Kalaine]

Access 2003:
The following code is intended to delete the current record and all child records but results in an error, help please, thx.

Private Sub CmdDelBSR_Click ()
On Error GoTo Err_CmdDelBSR_C lick
Dim strSQL As String

DoCmd.SetWarnin gs (False)
strSQL = "delete from bsr_offers_T where bsr_id = " & Me.BSR_ID
DoCmd.RunSQL strSQL
strSQL = "delete from bsr_price_plan_ T where bsr_id = " & Me.BSR_ID
DoCmd.RunSQL strSQL
strSQL = "delete from bsr_attachments where bsr_id = " & Me.BSR_ID
DoCmd.RunSQL strSQL

strSQL = "delete from bsr_t where bsr_id = " & Me.BSR_ID
DoCmd.RunSQL strSQL

DoCmd.Requery '<== this line produces the error "Microsoft Access Can't find the field "|" refered to in your expression
DoCmd.GoToRecor d , , acFirst '<== error message Goto not available if I dont have the requery line
DoCmd.SetWarnin gs (True)

Exit_CmdDelBSR_ Click:
Exit Sub

Err_CmdDelBSR_C lick:
MsgBox Err.Description
Resume Exit_CmdDelBSR_ Click
End Sub

[Kalaine]

To Clarify, the code is behind a command button on a multi page form that has sub forms tied to other table records based on BSR_ID. Any help? I must be missing something simple. Thank you.

[Kalaine]

Solved it....

Private Sub CmdDelBSR_Click ()
On Error GoTo Err_CmdDelBSR_C lick
Dim strSQL As String

DoCmd.SetWarnin gs (False)
strSQL = "delete from bsr_offers_T where bsr_id = " & Me.BSR_ID
DoCmd.RunSQL strSQL
strSQL = "delete from bsr_price_plan_ T where bsr_id = " & Me.BSR_ID
DoCmd.RunSQL strSQL
strSQL = "delete from bsr_attachments where bsr_id = " & Me.BSR_ID
DoCmd.RunSQL strSQL

'strSQL = "delete from bsr_t where bsr_id = " & Me.BSR_ID
'DoCmd.RunSQL strSQL 'replaced with the following

DoCmd.RunComman d acCmdDeleteReco rd

DoCmd.GoToRecor d , , acFirst

DoCmd.SetWarnin gs (True)

Exit_CmdDelBSR_ Click:
Exit Sub

Err_CmdDelBSR_C lick:
MsgBox Err.Description
Resume Exit_CmdDelBSR_ Click
End Sub

[Kalaine]

Last solution was creating a ghost record. The following code works. Current record and children record are successfully deleted and no new record is created.

Private Sub CmdDelBSR_Click ()
On Error GoTo Err_CmdDelBSR_C lick
Dim strSQL As String

DoCmd.SetWarnin gs (False)
If MsgBox("You are about to delete bsr " & Me.BSR_ID & "and all its associated price plans and offers. Do you wish to continue and delete the record?", vbYesNo) = vbNo Then
Else

strSQL = "delete from bsr_offers_T where bsr_id = " & Me.BSR_ID
DoCmd.RunSQL strSQL
strSQL = "delete from bsr_price_plan_ T where bsr_id = " & Me.BSR_ID
DoCmd.RunSQL strSQL
strSQL = "delete from bsr_attachments where bsr_id = " & Me.BSR_ID
DoCmd.RunSQL strSQL

'strSQL = "delete from bsr_t where bsr_id = " & Me.BSR_ID
'DoCmd.RunSQL strSQL

DoCmd.RunComman d acCmdDeleteReco rd

If Me.NewRecord = True Then

'Added this line to prevent new record
DoCmd.RunComman d acCmdUndo
End If
'DoCmd.Requery '<== this line produces the error
DoCmd.GoToRecor d , , acFirst

DoCmd.SetWarnin gs (True)
End If
Exit_CmdDelBSR_ Click:
Exit Sub

Err_CmdDelBSR_C lick:
MsgBox Err.Description
Resume Exit_CmdDelBSR_ Click
End Sub