Convert Delete Command Macro to VBA

[chrisjames25]

Hi

My question is two fold.

Overview - I have an Parent table named Tbl_Invoice and a child table named Tbl_InvoiceDetails. I currently have a relationship between the 2 linking the Invoice_ID. I have enforced cascading delete

Question 1 - should i have enforced cascading delete or would it be better to code it in vba. I know sometime in access people in the know say dont use that in built function.

Question 2 - I added a command box to the form and using the wizard made it dlete current record. I have attached the macro. Ideally i would love to know how to convert that macro into vba as there are other things i want to code with the command button.

Many thanks in advance.

 

[June7]

I don't see any reason to not use cascade delete. Depends on your situation. I have one db that does not depend on relationships to maintain data integrity, all controlled by code.

However, record deletion should be a rare event.

Access has a wizard to convert macros https://support.office.com/en-us/ar...4-4121-9277-70649e33be4f#bmconvertmacrostovba

 

[chrisjames25]

Hi

THanks for response. Did as you suggested and get the following (slightly confusing code - well confusing to me anyhow)

Private Sub Cmd_Close_Click()
On Error GoTo cmd_close_Click_Err

   
    On Error Resume Next
    DoCmd.GoToControl Screen.PreviousControl.Name
    Err.Clear
    If (Not Form.NewRecord) Then
        DoCmd.RunCommand acCmdDeleteRecord
         
    End If
    If (Form.NewRecord And Not Form.Dirty) Then
        Beep
    End If
    If (Form.NewRecord And Form.Dirty) Then
        DoCmd.RunCommand acCmdUndo
    End If
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
            End If


Cmd_close_Click_Exit:
    Exit Sub

cmd_close_Click_Err:
    MsgBox Error$
    Resume Cmd_close_Click_Exit
End Sub

As suggested above dont totally get what all lines of code are doing but get the majority.

What is confusing me is i want to add some more to it and trying to figure out where to put extra code. Odd thing im finding is if i start adding a new record in a form and then click the above cancel button it clears it and resets it to a new record. I then add a breakpoint and start of code and start new record and click cancel.I follow the code through line by line to end and then at end it hasnt cleared the record.

Any ideas why this may be?

 

[June7]

I have never used Screen. That might be what is causing the code to fail in step debug because the form is not the active screen. Try Me.PreviousControl instead. Never used PreviousControl either.

Macro to VBA conversions are seldom perfect, usually require 'tweaking'.