Log Record Deletions...

While I tend to use Allen Browne's Audit Trail for most databases, sometimes I come across a situation where I need something a little different.  This is for those situations...
3.  Select the [Event Procedure] for the AfterUpdate event of a field that will be changed prior to the deletion (it could be a checkbox).  In my case it's cboActionID which must be changed to say *(D)elete* or the record will not Delete.  (Make sure to change the control names to match your own!)
1.  Create tblLog...
Can be either TEXT or NUMERIC depending on the Data Type of the ID's you want to store.
2.  Go the Forms VB Editor in which the RecordSource contains the records you want to log if they are deleted and copy/paste the below into the VB Editor.
The difficult I do immediately, the impossible takes a little bit longer.
Public Function LogDeletion(lngDGAMS As Long, strForm As String, strNotes As String)
'From http://www.access-diva.com/
     Dim strSQL As String
     strSQL = "INSERT INTO tblLog ( lID, lLogDate, lUserID, lNotes, lSystemForm )" & _
               " SELECT " & lngID & " AS ID, Now(), GetUserID(), '" & strNotes & "', '" & strForm & "'"
     CurrentDb.Execute strSQL, dbFailOnError
End Function
Public Function GetUserID() As String
     GetUserID = Environ("Username")
End Function 
Private Sub cboActionID _AfterUpdate()
Dim intResp As Integer
     intResp = MsgBox("You are about to DELETE this record, are you sure?", vbYesNo + vbExclamation, "Delete")
     If intResp = vbYes Then
          Call LogDeletion( Me.txtID , "frmYourForm", "ID Number " & Me.txtID & " has been Deleted!")
          Me.cboActionID = ""
     End If
End Sub 
Tips (Main)
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Create a Table with Dynamic Field Names
Snippets for Exporting to Excel
Log Field Changes
Check for Duplicate Values
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
Looping Records to Send eMail
Click to send feedback...
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.