MS Access Logging

PHOTO EMBED

Thu Mar 03 2022 12:50:39 GMT+0000 (UTC)

Saved by @paulbarry #vba 

' 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]
content_copyCOPY