code to delete a record in one subform when a record in a related subform is deleted

[Helen]

Hi all,

I'm automating the insert, update and deletion of records in one subform,
based on user actions in another subform. I've successfully written the code
(using DAO in ver 2000) to do the insert and update of a record in the
related recordset when that's the action carried out by the user in the
initiating subform. (My scenario is thatn when the user enters a record in
the subform for machine activity, a record is to be automatically
inserted/updated/deleted into the labour subform for a person who is
operating the machine)

I'm struggliing with the Delete part - how or at what point do I run the
code to do the delete? I have written the actual code to make the delete
happen, and have it on the Delete event of the initiating subform at the
moment - is that right? It seems to do the delete OK regardless of the
response from the user to my msgbox though, so that's one problem.

And I'm also getting the #Deleted in the subform, which I can make go away
with a refresh button on the main form, but I'd like to automate that as
part of the code. As you can see below I've tried mainform.requery on the
delete procedure from the subform but that's not doing it.

This is the code I have on the delete event on the initiating (Machine)
subform:

Private Sub Form_Delete(Cancel As Integer)
'the various dim statements
DoCmd.SetWarnings False

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblLabourOCFLineItems") 'the subform is
bound to this table, which stores the Labour data
Set oForm = Forms![frmLabourMachineOCFMain]![fsubLabourOCFLineItems].Form
intResponse = MsgBox("Do you want to delete this record and the related
record in the Labour subform?", _
vbYesNo, "Confirm Record Deletion")
If intResponse = vbYes Then
If Not IsNull(Me.LabourLineItemID) Then

With rst
.FindFirst "LabourLineItemID = " & Me.LabourLineItemID
If Not .NoMatch Then
.Delete
End If
End With
End If
Else
Cancel = True
End If
'oForm.Recalc
oForm.Refresh
Forms![frmLabourMachineOCFMain].Refresh 'this is the main form, and
my attempt to get rid of the #Deleted in the Labour subform
DoCmd.SetWarnings True

End Sub

I have a message how to stop it from happening if the user cancels the
delete from the message box that pops up in the initiating subform.
Any help/suggestions would be greatly appreciated.

Best regards,

Helen

 

[Guest]

Hi,

Can you try this (code is not tested)?


Private Sub Form_Delete(Cancel As Integer)
Dim cmd As New ADODB.Command

cmd.CommandText = "DELETE * FROM tblLabourOCFLineItems WHERE
LabourLineItemID=?"
cmd.ActiveConnection = CurrentProject.Connection
cmd.Parameters.Refresh
If MsgBox("Do you want to delete this record and the related record in
the Labour subform?", vbYesNo, "Confirm Record Deletion") = vbYes Then
cmd.Parameters(0) = Me.LabourLineItemID
cmd.Execute
Me.Parent.Form.fsubLabourOCFLineItems.Requery
Else
Cancel = True
End If
Set cmd = Nothing

- Raoul

Hi all,

I'm automating the insert, update and deletion of records in one subform,
based on user actions in another subform. I've successfully written the code
(using DAO in ver 2000) to do the insert and update of a record in the
related recordset when that's the action carried out by the user in the
initiating subform. (My scenario is thatn when the user enters a record in
the subform for machine activity, a record is to be automatically
inserted/updated/deleted into the labour subform for a person who is
operating the machine)

I'm struggliing with the Delete part - how or at what point do I run the
code to do the delete? I have written the actual code to make the delete
happen, and have it on the Delete event of the initiating subform at the
moment - is that right? It seems to do the delete OK regardless of the
response from the user to my msgbox though, so that's one problem.

And I'm also getting the #Deleted in the subform, which I can make go away
with a refresh button on the main form, but I'd like to automate that as
part of the code. As you can see below I've tried mainform.requery on the
delete procedure from the subform but that's not doing it.

This is the code I have on the delete event on the initiating (Machine)
subform:

Private Sub Form_Delete(Cancel As Integer)
'the various dim statements
DoCmd.SetWarnings False

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblLabourOCFLineItems") 'the subform is
bound to this table, which stores the Labour data
Set oForm = Forms![frmLabourMachineOCFMain]![fsubLabourOCFLineItems].Form
intResponse = MsgBox("Do you want to delete this record and the related
record in the Labour subform?", _
vbYesNo, "Confirm Record Deletion")
If intResponse = vbYes Then
If Not IsNull(Me.LabourLineItemID) Then

With rst
.FindFirst "LabourLineItemID = " & Me.LabourLineItemID
If Not .NoMatch Then
.Delete
End If
End With
End If
Else
Cancel = True
End If
'oForm.Recalc
oForm.Refresh
Forms![frmLabourMachineOCFMain].Refresh 'this is the main form, and
my attempt to get rid of the #Deleted in the Labour subform
DoCmd.SetWarnings True

End Sub

I have a message how to stop it from happening if the user cancels the
delete from the message box that pops up in the initiating subform.
Any help/suggestions would be greatly appreciated.

Best regards,

Helen

 

[Helen]

Hi Raoul, thanks so much for your reply, I can't wait to try the code you
have given me. Unfortunately I'm having one of those weeks so I'll do it
over the weekend and let you know how I go. Thanks again.
rgds, Helen

Hi,

Can you try this (code is not tested)?


Private Sub Form_Delete(Cancel As Integer)
Dim cmd As New ADODB.Command

cmd.CommandText = "DELETE * FROM tblLabourOCFLineItems WHERE
LabourLineItemID=?"
cmd.ActiveConnection = CurrentProject.Connection
cmd.Parameters.Refresh
If MsgBox("Do you want to delete this record and the related record in
the Labour subform?", vbYesNo, "Confirm Record Deletion") = vbYes Then
cmd.Parameters(0) = Me.LabourLineItemID
cmd.Execute
Me.Parent.Form.fsubLabourOCFLineItems.Requery
Else
Cancel = True
End If
Set cmd = Nothing

- Raoul

Hi all,

I'm automating the insert, update and deletion of records in one subform,
based on user actions in another subform. I've successfully written the
code
(using DAO in ver 2000) to do the insert and update of a record in the
related recordset when that's the action carried out by the user in the
initiating subform. (My scenario is thatn when the user enters a record
in
the subform for machine activity, a record is to be automatically
inserted/updated/deleted into the labour subform for a person who is
operating the machine)

I'm struggliing with the Delete part - how or at what point do I run the
code to do the delete? I have written the actual code to make the delete
happen, and have it on the Delete event of the initiating subform at the
moment - is that right? It seems to do the delete OK regardless of the
response from the user to my msgbox though, so that's one problem.

And I'm also getting the #Deleted in the subform, which I can make go
away
with a refresh button on the main form, but I'd like to automate that as
part of the code. As you can see below I've tried mainform.requery on the
delete procedure from the subform but that's not doing it.

This is the code I have on the delete event on the initiating (Machine)
subform:

Private Sub Form_Delete(Cancel As Integer)
'the various dim statements
DoCmd.SetWarnings False

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblLabourOCFLineItems") 'the subform is
bound to this table, which stores the Labour data
Set oForm = Forms![frmLabourMachineOCFMain]![fsubLabourOCFLineItems].Form
intResponse = MsgBox("Do you want to delete this record and the related
record in the Labour subform?", _
vbYesNo, "Confirm Record Deletion")
If intResponse = vbYes Then
If Not IsNull(Me.LabourLineItemID) Then

With rst
.FindFirst "LabourLineItemID = " & Me.LabourLineItemID
If Not .NoMatch Then
.Delete
End If
End With
End If
Else
Cancel = True
End If
'oForm.Recalc
oForm.Refresh
Forms![frmLabourMachineOCFMain].Refresh 'this is the main form,
and
my attempt to get rid of the #Deleted in the Labour subform
DoCmd.SetWarnings True

End Sub

I have a message how to stop it from happening if the user cancels the
delete from the message box that pops up in the initiating subform.
Any help/suggestions would be greatly appreciated.

Best regards,

Helen

 

[Helen]

Hi Raoul,

I tried your code, many thanks again for your reply. I actually found it
worked similarly to mine. I had to lose the line cmd.Parameters(0) - it
generated and error, and didn't seem to be needed. I resolved the matter of
the second error message coming up from Access by setting a global boolean
variable and setting it to true. Then checking that from the
AfterDeleteConfirm event to suppress. I'm happy now even though I haven't
been able to dispense with the Refresh Data button. It all works as it
should... My code is now as follows for the delete bit, please feel free to
comment if you notice anything that looks risky or could be improved:

Option Compare Database
Public blnAutoDelete As Boolean

Private Sub Form_AfterDelConfirm(Status As Integer)
DoCmd.SetWarnings True
End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
If blnAutoDelete Then
DoCmd.SetWarnings False
Else
DoCmd.SetWarnings True
End If
End Sub

Private Sub Form_Delete(Cancel As Integer)
Dim cmd As New ADODB.Command

cmd.CommandText = "DELETE * FROM tblLabourOCFLineItems WHERE
LabourLineItemID= " & Me.LabourLineItemID
cmd.ActiveConnection = CurrentProject.Connection
cmd.Parameters.Refresh
If MsgBox("Do you want to delete this record and the related record in
the Labour subform?", _
vbYesNo, "Confirm Record Deletion") = vbYes Then
'cmd.Parameters(0) = Me.LabourLineItemID
blnAutoDelete = True
cmd.Execute
Me.Parent.Form.fsubLabourOCFLineItems.Requery
'neither this line or the one below are doing it actually, i'm still stuck
with DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70 'my button to refresh the data..
Else
Cancel = True
blnAutoDelete = False
End If
Set cmd = Nothing
End Sub

Your help greatly appreciated Raoul,
regards,
Helen

Hi,

Can you try this (code is not tested)?


Private Sub Form_Delete(Cancel As Integer)
Dim cmd As New ADODB.Command

cmd.CommandText = "DELETE * FROM tblLabourOCFLineItems WHERE
LabourLineItemID=?"
cmd.ActiveConnection = CurrentProject.Connection
cmd.Parameters.Refresh
If MsgBox("Do you want to delete this record and the related record in
the Labour subform?", vbYesNo, "Confirm Record Deletion") = vbYes Then
cmd.Parameters(0) = Me.LabourLineItemID
cmd.Execute
Me.Parent.Form.fsubLabourOCFLineItems.Requery
Else
Cancel = True
End If
Set cmd = Nothing

- Raoul

Hi all,

I'm automating the insert, update and deletion of records in one subform,
based on user actions in another subform. I've successfully written the
code
(using DAO in ver 2000) to do the insert and update of a record in the
related recordset when that's the action carried out by the user in the
initiating subform. (My scenario is thatn when the user enters a record
in
the subform for machine activity, a record is to be automatically
inserted/updated/deleted into the labour subform for a person who is
operating the machine)

I'm struggliing with the Delete part - how or at what point do I run the
code to do the delete? I have written the actual code to make the delete
happen, and have it on the Delete event of the initiating subform at the
moment - is that right? It seems to do the delete OK regardless of the
response from the user to my msgbox though, so that's one problem.

And I'm also getting the #Deleted in the subform, which I can make go
away
with a refresh button on the main form, but I'd like to automate that as
part of the code. As you can see below I've tried mainform.requery on the
delete procedure from the subform but that's not doing it.

This is the code I have on the delete event on the initiating (Machine)
subform:

Private Sub Form_Delete(Cancel As Integer)
'the various dim statements
DoCmd.SetWarnings False

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblLabourOCFLineItems") 'the subform is
bound to this table, which stores the Labour data
Set oForm = Forms![frmLabourMachineOCFMain]![fsubLabourOCFLineItems].Form
intResponse = MsgBox("Do you want to delete this record and the related
record in the Labour subform?", _
vbYesNo, "Confirm Record Deletion")
If intResponse = vbYes Then
If Not IsNull(Me.LabourLineItemID) Then

With rst
.FindFirst "LabourLineItemID = " & Me.LabourLineItemID
If Not .NoMatch Then
.Delete
End If
End With
End If
Else
Cancel = True
End If
'oForm.Recalc
oForm.Refresh
Forms![frmLabourMachineOCFMain].Refresh 'this is the main form,
and
my attempt to get rid of the #Deleted in the Labour subform
DoCmd.SetWarnings True

End Sub

I have a message how to stop it from happening if the user cancels the
delete from the message box that pops up in the initiating subform.
Any help/suggestions would be greatly appreciated.

Best regards,

Helen