andigirlsc
Registered User.
- Local time
- Today, 08:48
- Joined
- Jun 4, 2014
- Messages
- 59
Solution
Here is the solution to getting the Audit Trail table to work on both the Main form and Subform simultaneously, even if they both have different primary keys and the Subform is based on a query (as in my case). As with most Subform/Audit Trail issues, the Main form sends data to the Audit Trail table, but the Subform does not. I have written out the exact code that will work based on the original code I found at fontstuff.com. All the leg work has been done for you, so enjoy!
Getting Started:
You must have the following in place before getting this code to work.
(1) You must have the Microsoft ActiveX Data Objects 2.8 Library added to your VBA Reference Library for the Audit Trail solution to work.
(3) You need the following fields in tblAuditTrail
Field Name, (Data Type)
- AuditTrailID, (Autonumber)
- DateTime, (Date/Time)
- UserName, (Text)
- FormName, (Text)
- Action, (Text)
- RecordID, (Text)
- FieldName, (Text)
- OldValue, (Text)
- NewValue, (Text)
(4) You need the Microsoft ActiveX Data Objects 2.8 Library.
To get it, open The VBA window and click | Tools | References | Scroll down to Microsoft ActiveX Data Objects 2.8 Library, check the box and click OK.
(5) Place the word "Audit" (without quotes) in the Tag property of all controls on the Main form and Subform.
(6) Create the Audit Trail module for the Main Form
Create a new module, name it "modAudit" and paste the following code. If your Audit Trail table is not named "tblAuditTrail" rename that table reference in your code after you paste it into your module. It's marked in blue.
(7) Call the Audit Trail module from the Main Form
In the Before Update Event of the Main form, paste the following code. Replace "MainFormPrimaryKey" with the name of your Main Form's Primary Key in both lines of code.
Now for the Subform...
Your Subform will have to call a separate module with slightly different code. This new module will have to be called from the Before Update Event of the Subform, not the Main form.
(8) Create the Audit Trail module for the Subform
Create a new module and name it "modAuditSub" and paste the following code. This will be the code used to call the Audit Trail from your Subform. The code in blue indicates the changes that make it possible for your Subform to trigger the Audit Trail while the Audit Trail simultaneously records changes from your Main form.
(9) Call the Audit Trail module from the Subform
In the Before Update Event of the Subform, paste the following code. Replace "SubFormPrimaryKey" with the name of your SubForm's Primary Key in both lines of code.
That's all there is to it.
Here is the solution to getting the Audit Trail table to work on both the Main form and Subform simultaneously, even if they both have different primary keys and the Subform is based on a query (as in my case). As with most Subform/Audit Trail issues, the Main form sends data to the Audit Trail table, but the Subform does not. I have written out the exact code that will work based on the original code I found at fontstuff.com. All the leg work has been done for you, so enjoy!
Getting Started:
You must have the following in place before getting this code to work.
(1) You must have the Microsoft ActiveX Data Objects 2.8 Library added to your VBA Reference Library for the Audit Trail solution to work.
- Open your Access database
- Press CTRL+G to open the VBA window
- Click Tools | References
- Scroll to find the Microsoft ActiveX Data Objcts 2.8 Library
- Check the box to add the reference library
- Click OK
- Click Save in the VBA window
(3) You need the following fields in tblAuditTrail
Field Name, (Data Type)
- AuditTrailID, (Autonumber)
- DateTime, (Date/Time)
- UserName, (Text)
- FormName, (Text)
- Action, (Text)
- RecordID, (Text)
- FieldName, (Text)
- OldValue, (Text)
- NewValue, (Text)
(4) You need the Microsoft ActiveX Data Objects 2.8 Library.
To get it, open The VBA window and click | Tools | References | Scroll down to Microsoft ActiveX Data Objects 2.8 Library, check the box and click OK.
(5) Place the word "Audit" (without quotes) in the Tag property of all controls on the Main form and Subform.
(6) Create the Audit Trail module for the Main Form
Create a new module, name it "modAudit" and paste the following code. If your Audit Trail table is not named "tblAuditTrail" rename that table reference in your code after you paste it into your module. It's marked in blue.
Code:
Option Compare Database
Option Explicit
Sub AuditChanges(IDField As String, UserAction As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM [COLOR="Blue"][B]tblAuditTrail[/B][/COLOR]", cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = Environ("USERNAME")
Select Case UserAction
Case "EDIT"
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Form.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Form(IDField).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
End If
End If
Next ctl
Case Else
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Form.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Form(IDField).Value
.Update
End With
End Select
AuditChanges_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume AuditChanges_Exit
End Sub
(7) Call the Audit Trail module from the Main Form
In the Before Update Event of the Main form, paste the following code. Replace "MainFormPrimaryKey" with the name of your Main Form's Primary Key in both lines of code.
Code:
Option Compare Database
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
'Calls modAudit function to record new records to Audit Trail
Call AuditChanges("[B][COLOR="blue"]MainFormPrimaryKey[/COLOR][/B]", "NEW")
Else
'Calls modAudit function to record edits to Audit Trail
Call AuditChanges("[B][COLOR="blue"]MainFormPrimaryKey[/COLOR][/B]", "EDIT")
End If
End Sub
Now for the Subform...
Your Subform will have to call a separate module with slightly different code. This new module will have to be called from the Before Update Event of the Subform, not the Main form.
(8) Create the Audit Trail module for the Subform
Create a new module and name it "modAuditSub" and paste the following code. This will be the code used to call the Audit Trail from your Subform. The code in blue indicates the changes that make it possible for your Subform to trigger the Audit Trail while the Audit Trail simultaneously records changes from your Main form.
Code:
Option Compare Database
Option Explicit
Sub AuditChangesSub(IDField As String, UserAction As String)
On Error GoTo AuditChangesSub_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = Environ("USERNAME")
Select Case UserAction
Case "EDIT"
For Each ctl In [COLOR="blue"][B]Screen.ActiveControl.Parent.Controls[/B][/COLOR]
If ctl.Tag = "Audit" Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = [COLOR="blue"][B]Screen.ActiveControl.Parent.Form.Name[/B][/COLOR]
![Action] = UserAction
![RecordID] = [B][COLOR="blue"]Screen.ActiveControl.Parent.Form(IDField).Value[/COLOR][/B]
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
End If
End If
Next ctl
Case Else
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = [COLOR="blue"][B]Screen.ActiveControl.Parent.Form.Name[/B][/COLOR]
![Action] = UserAction
![RecordID] = [COLOR="Blue"][B]Screen.ActiveControl.Parent.Form(IDField).Value[/B][/COLOR]
.Update
End With
End Select
AuditChangesSub_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
AuditChangesSub_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume AuditChangesSub_Exit
End Sub
(9) Call the Audit Trail module from the Subform
In the Before Update Event of the Subform, paste the following code. Replace "SubFormPrimaryKey" with the name of your SubForm's Primary Key in both lines of code.
Code:
Option Compare Database
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
'Calls modAuditSub function to record new data to Audit Trail
Call AuditChangesSub("[B][COLOR="blue"]SubformPrimaryKey[/COLOR][/B]", "NEW") 'original
Else
'Calls modAuditSub function to record edits to Audit Trail
Call AuditChangesSub("[COLOR="blue"][B]SubformPrimaryKey[/B][/COLOR]", "EDIT") 'original
End If
End Sub
That's all there is to it.

Last edited: