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 After_Update 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)
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
Public Function GetUserID() As String
GetUserID = Environ("Username")
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 = ""