PC Review


Reply
Thread Tools Rate Thread

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

 
 
Helen
Guest
Posts: n/a
 
      30th Mar 2005
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


 
Reply With Quote
 
 
 
 
=?Utf-8?B?SmFSYQ==?=
Guest
Posts: n/a
 
      30th Mar 2005
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

"Helen" wrote:

> 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
>
>
>

 
Reply With Quote
 
 
 
 
Helen
Guest
Posts: n/a
 
      31st Mar 2005
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

"JaRa" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "Helen" wrote:
>
>> 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
>>
>>
>>



 
Reply With Quote
 
Helen
Guest
Posts: n/a
 
      3rd Apr 2005
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


"JaRa" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "Helen" wrote:
>
>> 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
>>
>>
>>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding Record in form - Related Data from related tbl doesn't show Whitey Microsoft Access Forms 5 6th May 2009 02:22 AM
Related Records in SubForm Block Delete Record =?Utf-8?B?RGVzaWduIGJ5IFN1ZQ==?= Microsoft Access Form Coding 5 3rd Feb 2005 05:15 PM
Looking up a related record in a related form =?Utf-8?B?SmltIE5vcm1pbGU=?= Microsoft Access Forms 0 3rd Nov 2004 04:46 PM
Click on record in a subform datasheet to open another form displaying info related to that record??? edself Microsoft Access Forms 1 24th Nov 2003 08:28 PM
Click on a record in a subform datasheet to open another form displaying info related to that record??? edself Microsoft Access Forms 2 24th Nov 2003 07:20 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:02 AM.