' Put this code in your form's before_update
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditTrail(Me, [SUArchiveID])
End Sub
' Put code below in your Audit Module
Enum AuditAction
Add
Delete
Edit
Bulk
DbgError
End Enum
Function getAuditEnumString(eValue As AuditAction) As String
Select Case eValue
Case Add
getAuditEnumString = "ADD"
Case Edit
getAuditEnumString = "EDIT"
Case Delete
getAuditEnumString = "DELETE"
Case DbgError
getAuditEnumString = "ERROR"
End Select
End Function
Public Sub AuditTrail(frm As Form, CourseID As String, recordID As String, Optional action As AuditAction)
'Track changes to data.
'recordid identifies the pk field's corresponding
'control in frm, in order to id record.
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String
Dim auditActionStr As String
On Error GoTo ErrHandler
If IsMissing(action) Then
auditActionStr = getAuditEnumString(AuditAction.Edit)
Else
auditActionStr = getAuditEnumString(action)
End If
Dim sqlCollection As New Collection
'On Error GoTo ErrHandler
'Get changed values.
For Each ctl In frm.Controls
With ctl
'Avoid labels and other controls with Value property.
Select Case .ControlType
Case acComboBox, acListBox, acTextBox
If IsOldValueAvailable(ctl) Then
If .value <> .oldValue Then
varBefore = .oldValue
varAfter = .value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "Audit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue, FormName, CourseID, Action) " _
& "VALUES (Now()," _
& cDQ & getLoggedInUserID & cDQ & ", " _
& cDQ & recordID & cDQ & ", " _
& cDQ & Replace(Left(frm.RecordSource, 255), Chr(34), "") & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ", " _
& cDQ & Left(frm.Name, 255) & cDQ & ", " _
& cDQ & CourseID & cDQ & ", " _
& cDQ & auditActionStr & cDQ & ")"
'sqlCollection.Add strSQL
CurrentDb.Execute strSQL
'MsgBox "Audit " + strSQL
End If
End If
End Select
End With
Next
Set ctl = Nothing
'Set AuditTrail = sqlCollection
Exit Sub
ErrHandler:
MsgBox "Audit Trail Error:" & Err.Description & vbNewLine _
& Err.number, vbOKOnly, "Error"
End Sub
' SQL Code below to create Audit table
CREATE TABLE [dbo].[Audit](
[EditedRecordID] [int] IDENTITY(1,1) NOT NULL,
[EditDate] [datetime] NULL,
[User] [nvarchar](255) NULL,
[RecordID] [nvarchar](255) NULL,
[SourceTable] [nvarchar](255) NULL,
[SourceField] [nvarchar](255) NULL,
[BeforeValue] [nvarchar](255) NULL,
[AfterValue] [nvarchar](255) NULL,
[FormName] [nvarchar](255) NULL,
[CourseID] [nvarchar](255) NULL,
[Action] [nvarchar](10) NULL,
CONSTRAINT [Audit$PrimaryKey] PRIMARY KEY CLUSTERED
(
[EditedRecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Comments