Delete a record on continious form.

MX,
That worked but I have a function call that creates an audit trail when a record is deleted
Call Delete_Rec(Me, "MFG_ID,MFG_NAME,", MFG_ID.Value) I tried putting it after vbExclamation + vbYesNo + vbDefaultButton2, "Delete Record") <> vbYes Then
        Cancel = True
but it didn't record the changes and caused the requery to bomb
       

Where is that code - relative to the code I posted ?

mx

MX
That has to run if the actual deletion takes place. I modified on delete event (Changes in bold)
but it throws a debug error.
Private Sub Form_Delete(Cancel As Integer)
    Beep
   'This message or something similar
    If MsgBox("Are you SURE you want to DELETE this record?." & Chr(13) & _
          "This operation cannot be undone", _
           vbExclamation + vbYesNo + vbDefaultButton2, "Delete Record") = vbYes Then
Call Delete_Rec(Me, "MFG_ID,MFG_NAME,", MFG_ID.Value)
Else  
      Cancel = True
    End If
End Sub

 

Try putting the code in the AfterDelete event - after the Requery.  This is when you know the original Delete has occurred successfully.

mx

Throws a different debug error

Weird.  Did you add *any* other code to the code I posted?

Is this a bound form ?

What happens if you comment out the Requery as a test ?

What does Delete_Rec() do ?  

Are you actually using a Transaction somewhere ?

mx

OK,
I commented out the requery

Private Sub Form_AfterDelConfirm(Status As Integer)
   ' Me.Requery
    Call Delete_Rec(Me, "MFG_ID,MFG_NAME,", MFG_ID.Value)
   DoEvents
 
End Sub

the function call "Delete_Rec" tracks record deletions for audit tracking when invoked it prompts to enter a reason for the deletion, this time it poped up twice and deleted the previous record, I thing it went into a loop. I only had 2 records in the table but I think if there were more it would continue. Here is the  code for the module

Option Compare Database
Option Explicit

Public gstrReason      As String

Public Function Delete_Rec(MyForm As Form, UniqID_Field As String, UniqID As String)
On Error GoTo Err_Delete_Rec

    MyForm.AllowEdits = True
    MyForm.AllowDeletions = True

    If MsgBox("Are you sure you want to delete this record", vbYesNo, "Delete this record?") = vbYes Then
        Dim ctl As Control
        Dim sUser As String
        Dim delvals As String
        
        Dim strSQL As String
        Const cQUOTE = """" 'That is 2 quotes in sequence
            
        Dim Action As String
        Action = "*** Record Deleted ***"
               
        sUser = LAS_GetUserName() 'get the users login name
        'sUser = Environ("UserName") 'get the users login name
                                              
        'Check each data entry control for change and record old value of the control.
        For Each ctl In MyForm.Controls
            'Only check data entry type controls.
            Select Case ctl.ControlType
                Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
                If ctl.Name Like "*" & "txt" & "*" Then GoTo TryNextControl 'Skip AuditTrail field.
                If Len(ctl.Value) > 0 Then delvals = delvals & "| " & ctl.Name & " = " & ctl.Value & " "
            End Select
            
TryNextControl:
Next ctl
                       
        'Broken down into 4 separate variables for ease of view and troubleshooting
        strSQL = "INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, [Action], Reason, DelValues)"
        strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
        strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
        strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & Action & cQUOTE & ", " & "[Reason for change] AS Expr1" & ", "
        strSQL = strSQL & cQUOTE & delvals & cQUOTE & ";"
                
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        
        DoCmd.SetWarnings False
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        DoCmd.SetWarnings True
        
    End If
    
    
    MyForm.AllowEdits = False
    MyForm.AllowDeletions = False
    
Exit_Delete_Rec:
    Exit Function
    
Err_Delete_Rec:
        Beep
        MsgBox Err.Number & " - " & Err.description
        'Resume Exit_Delete_Rec
        Exit Function
End Function

Select allOpen in new window

"when invoked it prompts to enter a reason for the deletion,"
So, is the user supposed to enter a reason *before* the actual Delete occurs?

mx

No, it is actual optional, it does however write the deleted data to the table prior to prompting for the reason

"write the deleted data to the table prior to prompting for the reason '
Well .. then that write would need to occur before the actual delete event, otherwise the data will be gone.  So ... no can do in the AfterDelete event as I mentioned, unless you capture the data somehow.

Not really sure what to say here.  You have a lot more going on besides the 'normal' delete a record scenario ... which is what the code I provided does.

mx

MX,
If I just put the function call in the onclick event of the delete button  it works.  I thought I tried that with all the other changes, guess not. Thanks for all your help as always.  The code you gave me works any ware the audit is not used and I will be using that code elsewhere.

ok.
Yes ... what you just did seems to make the most sense.  Glad you got it working.

thx.mx