Access Diva Log Field Changes

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!)

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.

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!")
     Else
          Me.cboActionID = ""
     End If
 
End Sub