Solved

Delete a record on continious form.

Posted on 2011-03-17
13
343 Views
Last Modified: 2013-11-27
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
Comment
Question by:skull52
  • 7
  • 6
13 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 35160073
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
 

Author Comment

by:skull52
ID: 35160939
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
 
LVL 75
ID: 35160976
Where is that code - relative to the code I posted ?

mx
0
 

Author Comment

by:skull52
ID: 35165901
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
 
LVL 75
ID: 35166859
Try putting the code in the AfterDelete event - after the Requery.  This is when you know the original Delete has occurred successfully.

mx
0
 

Author Comment

by:skull52
ID: 35166906
Throws a different debug error Error1 Error2
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 75
ID: 35166935
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
 

Author Comment

by:skull52
ID: 35167179
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
 
LVL 75
ID: 35167231
"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
 

Author Comment

by:skull52
ID: 35167685
No, it is actual optional, it does however write the deleted data to the table prior to prompting for the reason
0
 
LVL 75
ID: 35167773
"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
 

Author Comment

by:skull52
ID: 35167818
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
 
LVL 75
ID: 35167838
ok.
Yes ... what you just did seems to make the most sense.  Glad you got it working.

thx.mx
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now