Kayleigh
Member
- Local time
- Today, 17:32
- Joined
- Sep 24, 2020
- Messages
- 709
Hi,
I'm setting up an audit trail for all elements in my DB. It should record when updates are made and store in tblAudit.
This is the main code:
I have enclosed a very basic sample DB where I have applied this. The problem is:
a) It will not work for combo boxes or other controls which are not textboxes.
b) When executed in my main DB it will flag data mismatch error '13' - possibly due to a value in the control but have not managed to resolve.
Can anyone assist me here please?
I'm setting up an audit trail for all elements in my DB. It should record when updates are made and store in tblAudit.
This is the main code:
Code:
Option Compare Database
Option Explicit
Const cDQ As String = """"
Public 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
Dim user As Integer
user = Forms!frmLogin!cmbstaff.Value
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 " _
& "tblAuditTrail (fldEditDate, fldUser, fldRecordID, fldSourceTable, " _
& " fldSourceField, fldBeforeValue, fldAfterValue) " _
& "VALUES (Now()," _
& cDQ & user & 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 SubI have enclosed a very basic sample DB where I have applied this. The problem is:
a) It will not work for combo boxes or other controls which are not textboxes.
b) When executed in my main DB it will flag data mismatch error '13' - possibly due to a value in the control but have not managed to resolve.
Can anyone assist me here please?
