Access 2010 - Audit Trail with composite primary keys

Question

I have been unable to find a solution to this problem, so I hope some of you may be of help. I'm trying to create an Audit Trail for an Access database to track changes. Many of my tables have composite primary keys (multiple fields combined to uniquely identify records). I obtained the following code for the Audit module:

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.

Select Case .ControlType

Case acTextBox
If .Value <> .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name
'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "Audit (EditDate, RecordID, SourceTable, " _
 & " SourceField, BeforeValue, AfterValue) " _
 & "VALUES (Now()," _
 & cDQ & recordid.Value & cDQ & ", " _
 & cDQ & frm.RecordSource & cDQ & ", " _
 & cDQ & .Name & cDQ & ", " _
 & cDQ & varBefore & cDQ & ", " _
 & cDQ & varAfter & cDQ & ")"
 '& cDQ & Environ("username") & cDQ & ", " _

'View evaluated statement in Immediate window.

 Debug.Print strSQL
 DoCmd.SetWarnings False
 DoCmd.RunSQL strSQL
 DoCmd.SetWarnings True
 End If

Case acComboBox
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


Case acListBox
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 Select

End With
Next
Set ctl = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"

End Sub

And then in the code for individual Forms I have the following code to run the module:

Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditTrail(Me, HeaderID)
End Sub

I have found when I substitute the field name where HeaderID is for the name of a single valued primary key, the code works fine. When my table has multiple fields making up the primary key I am not sure how to format 'HeaderID' to recognize the composite of those values. I also have look up tables in some forms, but I'm unsure if that is contributing to the problem.

The error messages I'm getting:

Compile Error: ByRef argument type mismatch

Any thoughts would be greatly appreciated!

Thank you, Tiffany

Answer 1

Your AuditTrail subroutine takes as its parameters the form and the control where the edit to the data happens. So when you pass 'Me' and the name of the field (control), you're actually passing the current form and the control to the subroutine. When you change 'HeaderID' to anything other than a control name you will get the 'Type Mismatch' error because your subroutine is expecting a control and not a piece of data.

You will need to adjust your AuditTrail subroutine to take the index value that you actually want to store in your table. If your combined ID is a string made up of three values then change the parameter of the AuditTrail subroutine from 'control' to 'string'.