Using Audit Trail on a Subform with a Different Primary Key (1 Viewer)

A

andigirlsc

Registered User.
Local time
Today, 19:51
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.
  • 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
(2) You need an Audit Trail table called "tblAuditTrail"

(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:
M

Mick99

New member
Local time
Today, 15:51
Joined
Feb 19, 2016
Messages
14
Hi andigirlsc. I must say a big thank you as this works a treat, thank you very much for sharing the solution with everybody. Legend!!:)
 
A

andigirlsc

Registered User.
Local time
Today, 19:51
Joined
Jun 4, 2014
Messages
59
You are quite welcome! It can be difficult finding solutions online. Sometimes you find a solution summary when what you really need is an answer template instead.
 
F

fenhow

Registered User.
Local time
Today, 15:51
Joined
Jul 21, 2004
Messages
599
Hello, I am trying to use this code and it works perfect on main form. When I do exactly as shown for the subform when I update a tagged Audit field I get this error:
"Object Does Not Support This Property or Method"

Any Ideas?
Fen
 
A

andigirlsc

Registered User.
Local time
Today, 19:51
Joined
Jun 4, 2014
Messages
59
fenhow said:
Hello, I am trying to use this code and it works perfect on main form. When I do exactly as shown for the subform when I update a tagged Audit field I get this error:
"Object Does Not Support This Property or Method"

Any Ideas?
Fen
Click to expand...

@fenhow

I will do my best to help. I had the exact same problem with the "Object Does Not Support This Property or Method" error message and it turned out to be an area of the tab control on my subform was tagged that SHOULD NOT have been tagged because I used CTRL A to tag the entire subform. My solution is below.

Solution #1:
Try this first.
  1. Open your subform separately in design view
  2. Highlight the entire subform using CTRL A
  3. Add "Audit" to the Tag property (no quotes). This seems wrong, but we are going to remove all traces of the Audit Tag later all at once.
  4. Highlight the entire subform using CTRL A
  5. Remove Audit from the Tag property. This is so we can be sure all tags are removed all at once from the entire subform
  6. When adding the Audit Tag back this time, instead of highlighting the entire subform w/controls, individually click the controls on your subform that require the Audit Tag one control at a time.
  7. Save and test your form.

If this doesn't work, reply back and I will keep digging.
Best Regards,
 
H

hambees

New member
Local time
Today, 23:51
Joined
Apr 19, 2016
Messages
2
First off i want to thank you for this solution!!

I have been racking my brains for days now!

I have got another issue where if i am using table/query in the form or subform it doesn't seem to like it.

When i hit save i get "Object doesn't support this property or method", do you have any idea how i can get this to work with the audit trail without this error occurring?

I haven't used Access in years! I feel i have chosen a project a little to high for my capabilities now.

Also is there a way to get the audit trail to record all the new actions for a new entry rather than the ID was added? I also have another issue with the dropdown list that when recording the audit trail it records the ID rather than the data value, any ideas again.

Thanks in advance and i apologise if this is something simple.
 
A

andigirlsc

Registered User.
Local time
Today, 19:51
Joined
Jun 4, 2014
Messages
59
hambees said:
First off i want to thank you for this solution!!

I have been racking my brains for days now!

I have got another issue where if i am using table/query in the form or subform it doesn't seem to like it.

When i hit save i get "Object doesn't support this property or method", do you have any idea how i can get this to work with the audit trail without this error occurring?

I haven't used Access in years! I feel i have chosen a project a little to high for my capabilities now.

Also is there a way to get the audit trail to record all the new actions for a new entry rather than the ID was added? I also have another issue with the dropdown list that when recording the audit trail it records the ID rather than the data value, any ideas again.

Thanks in advance and i apologise if this is something simple.
Click to expand...

@hambees

It sounds like you may be missing a specific VBA library reference in Access needed to make the Audit Trail work. I will amend my original post to include it. My apologies for not including this step in my original post.

Solution:
You must have the Microsoft ActiveX Data Objects 2.8 Library added to your VBA Reference Library for the Audit Trail solution to work.

  1. Open your Access database
  2. Press CTRL+G to open the VBA window
  3. Click Tools | References
  4. Scroll to find the Microsoft ActiveX Data Objcts 2.8 Library
  5. Check the box to add the reference library
  6. Click OK
  7. Click Save in the VBA window
  8. Save Access
  9. Close and reopen your database
  10. Test the Audit Trail again
 
A

andigirlsc

Registered User.
Local time
Today, 19:51
Joined
Jun 4, 2014
Messages
59
hambees said:
First off i want to thank you for this solution!!

Also is there a way to get the audit trail to record all the new actions for a new entry rather than the ID was added? I also have another issue with the dropdown list that when recording the audit trail it records the ID rather than the data value, any ideas again.

Thanks in advance and i apologise if this is something simple.
Click to expand...

@hambees

Try this:
Limit new records to only the entry of the name of your person/product and the primary key (whether it is auto-numbered or not), then force the record window to close and train your Users to open the newly created record to enter the remaining details, which would force the Audit Trail to record the changes as an EDIT instead of a NEW record. This is a band-aid, but I need a few hours of free time to play with the code and am unable to do so at this time.

When things ease up for me, I will play with the code and see what I can come up with.

Hope this helps,
 
H

hambees

New member
Local time
Today, 23:51
Joined
Apr 19, 2016
Messages
2
@andigirlsc

Thanks so much for your help i tried using the 2.8 objects rather than the 2.1 and i still get that error message.

It normally only happens if the record has a value from a pick list running off a query, but it does save the audit trail entry.

Thanks for the suggestion on the band aid, i did think that as well :)
 
A

andigirlsc

Registered User.
Local time
Today, 19:51
Joined
Jun 4, 2014
Messages
59
fritz.panganiban said:
Hi andigirlsc

I'm humbly asking your favor if you could check out this thread and help me figure out the error on the code which will supposedly track changes made on a table shown in a subform.

http://www.access-programmers.co.uk/forums/showthread.php?t=288737

Thanks in advance
Click to expand...

I haven't been able to visit the forums lately, so I apologize for the late response. I'm busy with a new database myself, so I understand your frustration.

There are several ways to accomplish what you are trying to do, but my response below is based solely on the example I posted on 1/21/16. I haven't had time to study your code line by line, but it looks like the code you posted is incorrect in a few places. Also, it looks similar to the code I originally posted for the Main form, not the subform.

Even if you want to use this code for the Main form, it is still incorrect per my example, which is what my response is based on. The following lines of red code are incorrect for my suggestion to work properly.

Here is what you have for your subform VBA code:
Code: 
For Each ctl In [COLOR="red"]Screen.ActiveForm.ActiveControl.Form[/COLOR]
                If ctl.Tag = "Audit" Then
                    If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                        With rst
                            .AddNew
                            ![DateTime] = datTimeCheck
                            ![UserName] = strUserID
                            ![FormName] = [COLOR="red"]Screen.ActiveForm.ActiveControl.Form.Name[/COLOR]
                            ![Action] = UserAction
                            ![RecordID] = [COLOR="red"]Screen.ActiveForm.ActiveControl.Form(IDField).Value[/COLOR]
                            ![FieldName] = ctl.ControlSource
                            ![OldValue] = ctl.OldValue
                            ![NewValue] = ctl.Value
                            .Update

Here is what you should have for your subform VBA code per my example:
Code: 
For Each ctl In [COLOR="blue"]Screen.ActiveControl.Parent.Controls[/COLOR]
                If ctl.Tag = "Audit" Then
                    If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                        With rst
                            .AddNew
                            ![DateTime] = datTimeCheck
                            ![UserName] = strUserID
                            ![FormName] = [COLOR="blue"]Screen.ActiveControl.Parent.Form.Name[/COLOR]
                            ![Action] = UserAction
                            ![RecordID] = [COLOR="blue"]Screen.ActiveControl.Parent.Form(IDField).Value[/COLOR]
                            ![FieldName] = ctl.ControlSource
                            ![OldValue] = ctl.OldValue
                            ![NewValue] = ctl.Value
                            .Update

Here is my suggestion:

1. Start from scratch on the Audit Trail code for the Main form and the subform, following my example exactly except the name of the table you are using. Please do not skip any steps. I have tested this code and it works perfectly. If any lines of code are different except for the name of your form, the solution will not work.

2. You need separate VBA modules to store the code for the Main form and subform. You also need to call the Main form Audit Trail module from the Main form and the subform Audit Trail module from the subform in order for this to work. Each module needs to be named differently as well. All of this is listed in my instructions.

Let me refer you back to my original post on this issue on 1/21/16.
http://www.access-programmers.co.uk/forums/showthread.php?t=284008

Please let me know how things turn out.
 
F

faryusuf

New member
Local time
Tomorrow, 02:51
Joined
Jun 17, 2017
Messages
2
Hello andigirlsc,
My sincere thanks for posting this wonderful solution that worked like a charm for my Audi Trail requirements I was working on to manage the network shared database. It records all the EDIT details very well. However, during test, I found the following 2 concerns:

Situation: I have a Main Form (Name: Transaction) with Key Field as "Case ID" and a Sub-Form (Name: Action Records) with Key Field as "ID". The Sub-Form is linked to Main Form through the field "Case ID" and thus tracks all Action Records linked to a particular Transaction through "Case ID" (that is Primary Key on Main Form but not a Primary Key on Sub-Form).

1: When I DELETE a record from Main Form (Transaction), it does not record the Key Field ("Case ID") of the Main Form in Audit Table. This is why I cannot find which record from Transaction (Main Form) is deleted by a user. It records the User ID very well though.
2: When a record from sub-form (Action Records) is deleted, it does record the Key Field ("ID") from the sub-form but the recorded value probably takes the next record ID. For example, if I deleted Action Record with ID # 2342, in Audit Trail Table, it shows record with ID # 2343 is deleted, whereas the same record still exist in the table.

I am so close to getting the Audit Trail completed for the project and also used your codes above (step-by-step), for which I am grateful to you and your efforts. It would be nice if you can guide me to resolve the above issues.

Thank you in advance.
 
jdraw

jdraw

Super Moderator
Staff member
Local time
Today, 18:51
Joined
Jan 23, 2006
Messages
15,378
See this post for a modified version of the Martin Green Audit material that works with form and subform and includes a sample.

If you want to see the dialog that led to this, see this thread, specifically the issues identified by PSSMargaret.
 
Last edited:
F

faryusuf

New member
Local time
Tomorrow, 02:51
Joined
Jun 17, 2017
Messages
2
Thank you very much jdraw for sharing the link, much appreciated. I'll start reading the discussion to begin and understand the concept and codes there.

Thanks again.
 
A

andigirlsc

Registered User.
Local time
Today, 19:51
Joined
Jun 4, 2014
Messages
59
@ faryusuf:

I too could not get the delete Audit Trail code to work during testing either. I'm sorry you ran into trouble. I did not mention it because I never used the delete portion of the Audit Trail code for the systems I designed as the records were required to be tracked permanently.

@ jdraw:

Kudos to jdraw for finding and posting a solution! I will have to test it when I get a chance.
 
W

wotterbed

New member
Local time
Today, 18:51
Joined
Apr 4, 2018
Messages
2
@andigirlsc thanks for this super helpful code! If we have multiple subforms (I have 3), should we create an Audit Trail module for every subform and then for every subform, paste the same Before Update Event on each subform?
 
M

Mick99

New member
Local time
Today, 15:51
Joined
Feb 19, 2016
Messages
14
Yes, i created a new module for each instance and renamed the sub using the same code.
 
W

wotterbed

New member
Local time
Today, 18:51
Joined
Apr 4, 2018
Messages
2
If I have 3 fields as my primary key on my subform, how would I call the Audit Trail module from the subform? As in, what should I put in the line below where it says "SubformPrimaryKey":

Call AuditChangesSub("SubformPrimaryKey", "NEW") 'original


Thanks!
 
A

aestampa

New member
Local time
Today, 16:51
Joined
Sep 5, 2018
Messages
7
Hello Sir,
Thank you for your help, your mod/submod audit trail works perfect on my database.
Only think i need is how to add 'pcname' on tblaudittrail. I would like also to track pcname on audit. Thanks again.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:51
Joined
Aug 30, 2003
Messages
36,125
aestampa said:
Hello Sir,
Thank you for your help, your mod/submod audit trail works perfect on my database.
Only think i need is how to add 'pcname' on tblaudittrail. I would like also to track pcname on audit. Thanks again.
Click to expand...

Welcome to the site. I deleted your duplicate post (it was moderated anyway). You can get the computer name with the Environ() function:

Whatever = Environ("computername")
 
You must log in or register to reply here.

Similar threads

mike60smart
Audit Trail
2
Replies
22
Views
1,710
Pat Hartman
P
B
Solved AuditChanges not capturing ID for NEW additions
Replies
7
Views
586
Pat Hartman
P
T
Audit Trail - Tabbed Control
Replies
2
Views
837
jdraw
jdraw
K
Error 13 | Audit Log
Replies
4
Views
653
The_Doc_Man
The_Doc_Man
RickHunter84
Getting Error 91 on Audit trail
Replies
7
Views
891
Minty
Minty

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom