Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    North Carolina, USA
    Thanked 0 Times in 0 Posts

    Tracking deleted table records

    Hi, all! Happy new years!! I'm trying to figure out a VBA code to append the selected record that will be deleted from a form (on datasheet view) when the user press the yes option from the warning prompt into another table that keeps track of deleted items. I saw the On delete event but dont know how to code something that will execute after the user click "yes". Any help will be super appreciated!!!

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Manchester, United Kingdom
    Thanked 17 Times in 16 Posts
    Hi Lastcall

    I'm doing something similar in Visual Basic (not VBA unfortunately) and while I don't have an exact solution for you I can suggest some things.

    Hang your code from the "after del confirm" event. That fires when the user clicks out of the confirm dialog (or the "delete" event is cancelled in VBA). It carries a status of acDeleteOK for Yes and acDeleteUserCancel for No (acDeleteCancel if cancelled in VBA). Although the deleted row has gone from the grid at this point, it is still there in the form properties so you can pick up the values from the form controls as usual and bang them into a new row in your archive table. Of course, if the user has selected several rows you'll need to look at some other property to pick up the data.


  3. #3
    New Lounger
    Join Date
    Dec 2009
    Sherwood, OR, USA
    Thanked 0 Times in 0 Posts
    If you use a Listbox control to display the records on the form, you can set the control to allow multi-select and have a command button to process the selected records.

    Private Sub cmdDelete_Click()
    On Error GoTo PROC_ERR
    Dim ctl As Control
    Dim varItm As Variant

    Set ctl = Me.ListDetails 'Name of Listbox control
    If ctl.ItemsSelected.Count Then
    Dim strMsg As String
    Dim strStyle As String
    Dim strTitle As String
    Dim strSQL As String

    strMsg = "Are you sure you want to delete '" & ctl.Column(1) & "'" & vbCrLf
    strMsg = strMsg & "'" & ctl.Column(2) & ", " & ctl.Column(3) & ", " & ctl.Column(4) & "' ?"
    strStyle = vbYesNo + vbCritical + vbDefaultButton2
    strTitle = "Delete Confirmation"
    If MsgBox(strMsg, strStyle, strTitle) = vbYes Then
    'Loop through selected records
    For Each varItm In ctl.ItemsSelected
    DoCmd.SetWarnings False
    DoCmd.Hourglass True

    'Add code here to insert records into the other table

    'Delete selected record
    strSQL = "DELETE FROM TableName WHERE KeyName =" & ctl.Column(0, varItm) 'Numeric key
    CurrentDb.Execute strSQL
    Next varItm
    ' User chose No.
    End If
    MsgBox "Please select a record.", vbOKOnly + vbInformation, "Delete Error"
    End If
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    Exit Sub
    MsgBox Err.Number & " " & Err.Description, vbOKOnly + vbCritical, "Error"
    Resume PROC_EXIT
    End Sub

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Melbourne, Australia
    Thanked 27 Times in 27 Posts
    You could include a field in the table with a Yes/No value to record if the record has been deleted. Then just change the query behind the form to not include deleted records.
    You could then have an archive function to copy deleted records into another table or database and remove them from the original table, this would free up space in your database that holds the form's table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts