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

Discussion in 'Microsoft Access Form Coding' started by Helen, Mar 30, 2005.

  1. Helen

    Helen Guest

    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, Mar 30, 2005
    #1
    1. Advertisements

  2. Helen

    Guest Guest

    RE: code to delete a record in one subform when a record in a related

    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
    >
    >
    >
     
    Guest, Mar 30, 2005
    #2
    1. Advertisements

  3. Helen

    Helen Guest

    Re: code to delete a record in one subform when a record in a related

    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" <> wrote in message
    news:...
    > 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
    >>
    >>
    >>
     
    Helen, Mar 31, 2005
    #3
  4. Helen

    Helen Guest

    Re: code to delete a record in one subform when a record in a related

    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" <> wrote in message
    news:...
    > 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
    >>
    >>
    >>
     
    Helen, Apr 3, 2005
    #4
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Related Records in SubForm Block Delete Record

    Guest, Feb 2, 2005, in forum: Microsoft Access Form Coding
    Replies:
    5
    Views:
    211
    Guest
    Feb 3, 2005
  2. Guest

    Code to make a comma list from related table (subform)

    Guest, Mar 27, 2006, in forum: Microsoft Access Form Coding
    Replies:
    3
    Views:
    401
    Allen Browne
    Mar 28, 2006
  3. Scott Parmelee

    duplicate row, related rows, and their related rows

    Scott Parmelee, Jul 19, 2006, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    245
    Scott Parmelee
    Jul 20, 2006
  4. Guest

    Add a new record to related table on opening subform

    Guest, Aug 25, 2006, in forum: Microsoft Access Form Coding
    Replies:
    6
    Views:
    168
    Guest
    Sep 1, 2006
  5. Guest

    tabbed subform related new record.

    Guest, Aug 30, 2007, in forum: Microsoft Access Form Coding
    Replies:
    11
    Views:
    290
    Guest
    Sep 4, 2007
Loading...

Share This Page