Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    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
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    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.

    Ian

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Sherwood, OR, USA
    Posts
    11
    Thanks
    0
    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
    ctl.Requery
    Else
    ' User chose No.
    End If
    Else
    MsgBox "Please select a record.", vbOKOnly + vbInformation, "Delete Error"
    End If
    PROC_EXIT:
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    Exit Sub
    PROC_ERR:
    MsgBox Err.Number & " " & Err.Description, vbOKOnly + vbCritical, "Error"
    Resume PROC_EXIT
    End Sub
    Jack

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    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
  •