• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

Delete a record on continious form.

I have a form as a continuous view with a delete button for each record I converted the delete macro to VBA so that I could add my own Message box. but the default access message displays first then my custom message, I would like my message to be the one displayed. I have tried removing all the code except the DoCmd.RunCommand acCmdDeleteRecord.    
Private Sub btnDelete_Click()
On Error GoTo btnDelete_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
     Select Case MsgBox("You are about to delete this record., do you want to continue?", vbYesNo Or vbExclamation Or vbDefaultButton1, "Delete Record")
     
        Case vbYes
     
        Case vbNo
     
     End Select
 '  End If


btnDelete_Click_Exit:
    Exit Sub

btnDelete_Click_Err:
    MsgBox Error$
    Resume btnDelete_Click_Exit

End Sub

Open in new window

0
skull52
Asked:
skull52
  • 7
  • 6
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You basically need these four pieces to implement deleting a record:

Create a Button on the form to Delete:

Private Sub btnDelete    
    On Error Resume Next    'To trap case where user cancels Delete
    DoCmd.RunCommand acCmdDeleteRecord
    If Err.Number = 0 Or Err.Number = 2501 Then
        'no action needed.  0 means no error occurred; 2501 means Delete was cancelled in the Delete event below.
    Else
        MsgBox "An unexpected error has occurred when attempting to Delete this record:" & vbCrLf & vbCrLf & _
                  Err.Number & "  " & Err.Description, vbOKOnly, "DELETE RECORD"
    End If
End Sub
-------------------------

Then,put this code in the following three Form events:

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
        Cancel = True
    End If
End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
  Response = acDataErrContinue
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)    
    Me.Requery
    DoEvents
End Sub
---------------
mx
0
 
skull52Author Commented:
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
       
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Where is that code - relative to the code I posted ?

mx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
skull52Author Commented:
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

 Debug Error
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try putting the code in the AfterDelete event - after the Requery.  This is when you know the original Delete has occurred successfully.

mx
0
 
skull52Author Commented:
Throws a different debug error Error1 Error2
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
skull52Author Commented:
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

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
0
 
skull52Author Commented:
No, it is actual optional, it does however write the deleted data to the table prior to prompting for the reason
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
0
 
skull52Author Commented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok.
Yes ... what you just did seems to make the most sense.  Glad you got it working.

thx.mx
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now