I have a set of code to keep an audit trail that calls a module:
Calls
I also am trying to keep track of modified information via a macro. I converted the macro to VBA and was given this:
I'm a VB beginner and haven't figured out how to run both of these events on Form_BeforeUpdate.
I'd appreciate any help!
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditTrail(Me, CurrentCYIDPK)
End Sub
Calls
Code:
Option Compare Database
Const cDQ As String = """"
Sub AuditTrail(frm As Form, recordid As Control)
'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
On Error GoTo ErrHandler
'Get changed values.
For Each ctl In frm.Controls
With ctl
'Avoid labels and other controls with Value property.
If .ControlType = acTextBox 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) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& cDQ & recordid.Value & cDQ & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")"
'View evaluated statement in Immediate window.
Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
End If
End With
Next
Set ctl = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End Sub
I also am trying to keep track of modified information via a macro. I converted the macro to VBA and was given this:
Code:
'------------------------------------------------------------
' LastModified
'
'------------------------------------------------------------
Function LastModified()
On Error GoTo LastModified_Err
With CodeContextObject
.ModifiedDate = Date
.ModifiedTime = Time()
End With
LastModified_Exit:
Exit Function
LastModified_Err:
MsgBox Error$
Resume LastModified_Exit
End Function
I'm a VB beginner and haven't figured out how to run both of these events on Form_BeforeUpdate.
I'd appreciate any help!