Code:
Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
lngKeyValue As String, bWasNewRecord As Boolean) As Boolean
'On Error GoTo Err_AuditEditBegin
'Purpose: Write a copy of the old values to temp table.
' It is then copied to the true audit table in AuditEditEnd.
'Arugments: sTable = name of table being audited.
' sAudTmpTable = name of the temp audit table.
' sKeyField = name of the AutoNumber field.
' lngKeyValue = Value of the AutoNumber field.
' bWasNewRecord = True if this was a new insert.
'Return: True if successful
'Usage: Called in form's BeforeUpdate event. Example:
' bWasNewRecord = Me.NewRecord
' Call AuditEditBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
Dim db As DAO.Database ' Current database
Dim sSQL As String
'Remove any cancelled update still in the tmp table.
Set db = DBEngine(0)(0)
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL
' If this was not a new record, save the old values.
If Not bWasNewRecord Then
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
db.Execute sSQL, dbFailOnError
End If
AuditEditBegin = True
Exit_AuditEditBegin:
Set db = Nothing
Exit Function
'Err_AuditEditBegin:
' Call LogError(Err.Number, Err.Description, conMod & ".AuditEditBegin()", , False)
' Resume Exit_AuditEditBegin
End Function
Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable As String, _
sKeyField As String, lngKeyValue As String, bWasNewRecord As Boolean) As Boolean
'On Error GoTo Err_AuditEditEnd
'Purpose: Write the audit trail to the audit table.
'Arguments: sTable = name of table being audited.
' sAudTmpTable = name of the temp audit table.
' sAudTable = name of the audit table.
' sKeyField = name of the AutoNumber field.
' lngKeyValue = Value of the AutoNumber field.
' bWasNewRecord = True if this was a new insert.
'Return: True if successful
'Usage: Called in form's AfterUpdate event. Example:
' Call AuditEditEnd("tblInvoice", "audTmpInvoice", "audInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
Dim db As DAO.Database
Dim sSQL As String
Set db = DBEngine(0)(0)
If bWasNewRecord Then
' Copy the new values as "Insert".
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
db.Execute sSQL, dbFailOnError
Else
' Copy the latest edit from temp table as "EditFrom".
sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
" WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " & sAudTmpTable & ".audDate DESC;"
db.Execute sSQL
' Copy the new values as "EditTo"
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
"SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
db.Execute sSQL
' Empty the temp table.
sSQL = "DELETE FROM " & sAudTmpTable & ";"
db.Execute sSQL, dbFailOnError
End If
AuditEditEnd = True
Exit_AuditEditEnd:
Set db = Nothing
Exit Function
'Err_AuditEditEnd:
' Call LogError(Err.Number, Err.Description, conMod & ".AuditEditEnd()", , False)
' Resume Exit_AuditEditEnd
End Function