By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,324 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

How to get SubForms to work with AuditTrail in MS Access 2013

P: 11
So I have a form and Subform, the below scritp tracks the main form data great, I understand I need to make a loop to track the subform data also, the subform name is "SO_frm_Subform_Data_Input", where the data on this is being pulled from an outside table, the SO main data feeds some of the subform, either way, I have been able to track the changes on the main form, but where and what do I enter for the action to loop into the subform and track and post back to the audit table. The key fields in both the form and subform is "SO DESIGN ID"
Below is my script:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Sub SOAuditChanges(IDField As String)
  4.     On Error GoTo SOAuditChanges_Err
  5.     Dim cnn As ADODB.Connection
  6.     Dim rst As ADODB.Recordset
  7.     Dim ctl As Control
  8.     Dim datTimeCheck As Date
  9.     Dim strUserID As String
  10.     Set cnn = CurrentProject.Connection
  11.     Set rst = New ADODB.Recordset
  12.     rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
  13.     datTimeCheck = Now()
  14.      strUserID = Environ("USERNAME")
  15.     For Each ctl In Screen.ActiveForm.Controls
  16.         If ctl.Tag = "SOAudit" Then
  17.             If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
  18.                 With rst
  19.                     .AddNew
  20.                     ![DateTime] = datTimeCheck
  21.                     ![Username] = strUserID
  22.                     ![FormName] = Screen.ActiveForm.Name
  23.                     ![RecordID] = Screen.ActiveForm.Controls("SO DESIGN ID").Value
  24.                     ![FieldName] = ctl.ControlSource
  25.                     ![OldValue] = ctl.OldValue
  26.                     ![NewValue] = ctl.Value
  27.                     .Update
  28.                 End With
  29.             End If
  30.         End If
  31.     Next ctl
  32. SOAuditChanges_Exit:
  33.     On Error Resume Next
  34.     rst.Close
  35.     cnn.Close
  36.     Set rst = Nothing
  37.     Set cnn = Nothing
  38.     Exit Sub
  39. SOAuditChanges_Err:
  40.     MsgBox Err.Description, vbCritical, "ERROR!"
  41.     Resume SOAuditChanges_Exit
  42. End Sub
Jun 15 '17 #1

✓ answered by NeoPa

Hi Marc.

That's not so straightforward as I don't have your database to hand. However, I can illustrate some of the points where I suggested to make changes.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. ...
  5.  
  6. Public Sub SOAuditChanges(frmMe As Form, IDField As String)
  7. ...
  8.     For Each ctl In frmMe.Controls
  9. ...
  10. End Sub
That means it can be called from both the Main Form AND the Sub Form just as easily. You'll need to ensure you call it at the appropriate place, of course. that was covered earlier.

Share this Question
Share on Google+
18 Replies


NeoPa
Expert Mod 15k+
P: 31,492
Before we start you should certainly know about Require Variable Declaration. Please check it out.

Otherwise, am I right to conclude you're going through each control on the main form and logging a separate record in the audit for each?

You are looking for help to include all the data in the subform too? It seems to me that would be better done in the Form_AfterUpdate() event procedure of the relevant form if I'm honest.

Is the code you have for a specific form. It looks like it may be in a standard module. The code is far more clumsy than it needs to be if it's for a specific form.
Jun 15 '17 #2

P: 11
I only have two items on the Main Form that are being recorded for changes, a Date and Notes field, I can get them working with no issues, but on the Sub Form, I have another date that they want to be tracked for changes, all other items are pretty much free text for the employee to use as needed, minus a work flow date. The plan was to use the date changed from the audit table, then make a query against the ID of the change with the Old and New data to make a metric out of. If the date does not get changed for a long time, a note must say why in the primary form and such.
Once I get this work on this primary form and sub, I will edit to work on two other sets of forms from a different section working almost the same formatting.
Jun 15 '17 #3

NeoPa
Expert Mod 15k+
P: 31,492
If you were to handle the code in the Form's module then references to the Form and SubForm would all be using the keyword Me.

However, without a compelling argument otherwise, or even a much clearer and fully explained description of your requirements, I would still suggest that any work auditing the data in a SubForm should be handled in the module of the Form itself (The one used in the SubForm that is).

MarcM:
I only have two items on the Main Form that are being recorded for changes, a Date and Notes field, ...
I suspect these controls both have SOAudit in their tags then yes?

Remember, with Object Oriented Programming, which is how Event-Driven programming works, you can have two separate bits of code working together in a seamless way. It doesn't all need to be coded within the same space to work properly together. I hope that makes sense.

I'm afraid the rest of your latest post all relies on me being aware of your system to an extent that I'm not, so tells me very little. IE. Without the context it's meaningful only to you.

PS. Please confirm before going any further that you have at least updated your existing code with the Option Explicit line at the top. As explained in the linked article this is a bare minimum for any serious system.
Jun 15 '17 #4

P: 11
So as I stated, I can get the code to work in form by itself, but when I run it in a Subform, it does not return any changes, does not track the edits and post to the tblAuditTrail. What additional lines am I missing. A total of three fields need to be tracked, 2 dates and 1 notes. Once I get this corrected, I can edit this for two subforms. I have a total of three subforms, all with the same primary key but are providing different data to different users.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. 'The base code is from Martin Green
  5. 'http://www.fontstuff.com/access/acctut21.htm
  6. 'Code on the Event Procedure on the field
  7. 'Will Be Private Sub SO_SCHEDULE_DATE_BeforeUpdate(Cancel As Integer)
  8. '    If Not Me.NewRecord Then Call SOAuditChanges("SO SCHEDULE DATE")
  9. 'End Sub
  10. 'Under Tag of the field, is SO Audit
  11. 'Need to get this to run in a Subform for a total of three fields for
  12. 'tracking in tblAuditTrail
  13.  
  14. Sub SOAuditChanges(IDField As String)
  15.     On Error GoTo SOAuditChanges_Err
  16.     Dim cnn As ADODB.Connection
  17.     Dim rst As ADODB.Recordset
  18.     Dim ctl As Control
  19.     Dim datTimeCheck As Date
  20.      Dim strUserID As String
  21.     Set cnn = CurrentProject.Connection
  22.     Set rst = New ADODB.Recordset
  23.     rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
  24.     datTimeCheck = Now()
  25.      strUserID = Environ("USERNAME")
  26.     For Each ctl In Screen.ActiveForm.Controls
  27.         If ctl.Tag = "SOAudit" Then
  28.             If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
  29.                 With rst
  30.                     .AddNew
  31.                     ![DateTime] = datTimeCheck
  32.                     ![UserName] = strUserID
  33.                     ![FormName] = Screen.ActiveForm.Name
  34.                     ![RecordID] = Screen.ActiveForm.Controls("SO DESIGN ID").Value
  35.                     ![FieldName] = ctl.ControlSource
  36.                     ![OldValue] = ctl.OldValue
  37.                     ![NewValue] = ctl.Value
  38.                     .Update
  39.                 End With
  40.             End If
  41.         End If
  42.     Next ctl
  43. SOAuditChanges_Exit:
  44.     On Error Resume Next
  45.     rst.Close
  46.     cnn.Close
  47.     Set rst = Nothing
  48.     Set cnn = Nothing
  49.     Exit Sub
  50. SOAuditChanges_Err:
  51.     MsgBox Err.Description, vbCritical, "ERROR!"
  52.     Resume SOAuditChanges_Exit
  53. End Sub
  54.  
Jun 16 '17 #5

NeoPa
Expert Mod 15k+
P: 31,492
I'm out most of today so can't post a fully helpful reply at this stage.

OTOH, consider changing the code to take a Form object as a parameter. Use this instead of Screen.ActiveForm in your code and then call the procedure from both the main Form and the SubForm as appropriate. See how you get on with that.

Use the same concept of flagging the Controls you want audited in their .Tag properties.
Jun 17 '17 #6

P: 11
NeoPa
Since this is all new to me, could you show me on the code I have provided?
Jun 18 '17 #7

NeoPa
Expert Mod 15k+
P: 31,492
Hi Marc.

That's not so straightforward as I don't have your database to hand. However, I can illustrate some of the points where I suggested to make changes.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. ...
  5.  
  6. Public Sub SOAuditChanges(frmMe As Form, IDField As String)
  7. ...
  8.     For Each ctl In frmMe.Controls
  9. ...
  10. End Sub
That means it can be called from both the Main Form AND the Sub Form just as easily. You'll need to ensure you call it at the appropriate place, of course. that was covered earlier.
Jun 19 '17 #8

P: 11
NeoPA - I am not knowledgeable enough to follow "appropriate place" in writing code, just smart enough to be dangerous and able to copy and follow code of others. But I do thank you for the time you have given me. I was hopping that a simple line had to be entered and that would allow for the Sub form and main form to be tracked to the Audit table at the same time.
Jun 19 '17 #9

NeoPa
Expert Mod 15k+
P: 31,492
Hi Marc.

I could be more specific with my instructions if I had a fuller understanding of the story. Unfortunately that's very rare. Expressing requirements and situations clearly is a skill few master. I thus have to rely on you to infer my meanings based on what I say and what you know about your system. Sometimes the two sides don't quite meet up.

I assume that you have a Main Form / SubForm setup such that the Main Form manages a single record whereas the SubForm manages multiple records. Thus, the time to audit the Main Form record is when it has been changed. The time to audit any of the SubForm records is when they (individually) have been changed.

In both cases the forms used should have a call to the general-purpose subroutine called SOAuditChanges() from their Form_AfterUpdate() procedures.
Jun 19 '17 #10

P: 11
NeoPa, please see the attached ZIP file, it contains a stripped down version of what I am dealing with. I need to let you know, the Main table is on a different server, so it is only linked to the local table, which is what I will be doing with a total of three sections, they use the main table to get header info for items to action, then make notes. I have not built a report for each of the three sections as of yet, but will build them locally and then have a button on the Main Table (server) located where the review team can run a report from each external database, I hope this makes more sense, in the "subform" the changes are tracked, but when you get the header info loaded and make a subform where the changes are going to be made, I can not get those to be tracked.
Attached Files
File Type: zip SubFormAuditTest.zip (140.0 KB, 56 views)
Jun 19 '17 #11

NeoPa
Expert Mod 15k+
P: 31,492
Now I have the database I still find your explanations a long way short of helpful. We both know the names of the subforms now, and there are two of them. Please explain what you're having problems with clearly and concisely.

I notice that on the [Soils Tab] page, which is cleverly filled with spaces and a trailing full stop to display nicely, that you have a SubForm control called [SO_frm_Subform_Tab] which has a QueryDef object as its source, showing data in Datasheet view. I don't use either QueryDefs directly, or items in Datasheet view much myself, but I do know that using a form in Datasheet view means you don't have access to any of the events. If this is the form you're talking about then you can't expect it to work however you've tried it, and I still have very little idea from you yet as to what you've actually tried.

The common subroutine you've posted should do what you need now, but that's only part of the story of course. It'll only work properly for you when called from the right places.

If you want something like Datasheet View, but without the limitations, you can design a form to be used in Continuous Forms view. This will allow the events to fire and be captured by your code.
Jun 19 '17 #12

P: 11
A little break down of the database.

01_tbl_MAIN – This will be an external connection to the central server, what is going to happen, this will hold a standard format that will be used as header information on this SO system and other sections. There are a number of sections and each have a Different ID, the first 5 IDs in the table are all possible.

04_SO_tbl_Main and 04_SO_tbl_Main_Tab_Two are local section tables, those are to be kept on the sections server locally, 04_SO_tbl_Main_Tab_Two is really currently a really big excel spread sheet that I will convert into a table for the group once I get the Sub form thing corrected. But this is the sub form in the database you will open.

When the users open the SODatabase, the initial form will be SO_frm_Main_Input, that is where the general header information and search feature is located. The user will use one of the drop down selection boxes at the bottom to search for the project they are working on and then update data in the SO_frm_Subform_Data_Input. This is the subform I am in need of an Audit tracker, in this case the following fields in the subform need to be tracked for changed - SO DUE DATE, SO SCHEDULE DATE and SO COMPLETION DATE, these can all be passed to tblAuditTrail as a text field, the date format will hold.

When you run the SO_frm_Subform_Data_Input by itself, the audit function works, but when it is part of the SO_frm_Main_Input (Only need the Main Soils Tab to track), no data is being pushed.

I have a total of 6 other sections like this, they are all separate but all one, the whole idea behind this is to make sure a Q&A of projects and data can be done with less duplications, currently each of the sections were entering the header info “01_tbl_MAIN” and all sections had different formats, the other part, sections where using a combination of excel sheets, word files and their own access tables to track information.

Currently, if all the records were pulled in, the 01_tbl_MAIN would have a total of 732 records, but not all the section have actions with those records. So in the working database I sent, the SO Section would only have about 20-25 records where there data would matter.

End result is to get all the sections under one roof for the Header info, second, make all sections follow a defined format, move all their work into databases, away from Excel and word files, this is where the audit trail is really needed, for this case, Soils I need to be able to provide a report of changed dates, then that may have a ripple effect to other sections and those due dates, so when I make a report from the Audit Trail, I can show the changes by field and share as a report that anyone with access to the Reports page can see these changes.
Jun 20 '17 #13

NeoPa
Expert Mod 15k+
P: 31,492
Hi Marc.

This may sound churlish of me, but I'm still struggling to make sense of your explanations. I'm really not just trying to be picky. I actually find most of what you've said to be devoid of any clues that would help me to understand what you're looking for.
Marc:
01_tbl_MAIN – This will be an external connection to the central server, what is going to happen, this will hold a standard format that will be used as header information on this SO system and other sections. There are a number of sections and each have a Different ID, the first 5 IDs in the table are all possible.
What's a section? Different ID indicates it's a proper noun. I guess not as I've seen no evidence of an object with such a name. There's only one record in the table in the database you posted. How does your comment about the top five make sense?
Mark:
04_SO_tbl_Main and 04_SO_tbl_Main_Tab_Two are local section tables, those are to be kept on the sections server locally, 04_SO_tbl_Main_Tab_Two is really currently a really big excel spread sheet that I will convert into a table for the group once I get the Sub form thing corrected. But this is the sub form in the database you will open.
What is. I see you've talked about two tables and fixing some unspecified SubForm.
Marc:
When the users open the SODatabase, the initial form will be SO_frm_Main_Input, that is where the general header information and search feature is located. The user will use one of the drop down selection boxes at the bottom to search for the project they are working on and then update data in the SO_frm_Subform_Data_Input.
That actually reads well and makes good sense. Up to the point where you refer to SO_frm_Subform_Data_Input. I see a SubForm object called SO_frm_Subform_Tab. That looks fine so far. I also see an actual Form object called SO_frm_Subform_Data_Input, but it doesn't seem to be any part of the main Form SO_frm_Main_Input.

Other comments refer to sections, which leave me no wiser as I have no pointers to your vocabulary. I can't help if the stuff you say that actually makes sense doesn't match up to the reality of the database I'm looking at.

Let me be clear. I'm willing to spend time with you helping you to develop your database further. I have already spent a considerable amount. Nevertheless, the onus and responsibility for the project are yours. Your responsibility to ask for help in such a way as to make it practicable. Your responsibility to communicate your issue(s) accurately and with minimum numbers of inaccuracies. Your responsibility to understand your project well enough to be able to answer questions directly and clearly when asked.

The fact that you're asking a question at this level in the first place leads me to suspect you're no beginner. So far though, communications have been less clear than I'd expect from an experienced developer. Are you up for changing that now? Or do we both give it up as a bad job and look elsewhere? I'm not running away yet, but I'll need things to change if I'm to hang around too much longer. OTOH If you were to want to call it quits now I'd understand and sympathise.
Jun 20 '17 #14

P: 11
The audit trail works when you call the subform (SO_frm_Subform_Data_Input) by itself, when the subform is part of the default view (SO_frm_Main_Input), the field in the subform (SO_frm_Subform_Data_Input), in this case (SO DUE DATE), nothing gets passed to the Audit Table, changes are made, but because it is a subform of the default view (SO_frm_Main_Input), the data is not getting passed. This is where the code is failing me, it only looks at the (SO_frm_Main_Input) and not the subform (SO_frm_Subform_Data_Input).
Jun 21 '17 #15

P: 11
I found an answer from an early post that NeoPa had posted a while ago, the code seems to work on my test database with no errors, will test on Monday at work on a copy of the live database, below is the updated code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. 'The base code is from Martin Green
  5. 'http://www.fontstuff.com/access/acctut21.htm
  6. 'Code on the Event Procedure on the field
  7. 'Will Be Private Sub SO_SCHEDULE_DATE_BeforeUpdate(Cancel As Integer)
  8. '    If Not Me.NewRecord Then Call SOAuditChanges("SO SCHEDULE DATE")
  9. 'End Sub
  10. 'Under Tag of the field, is SO Audit
  11. 'Need to get this to run in a Subform for a total of three fields for
  12. 'tracking in tblAuditTrail
  13. 'To include Subforms in the Audit, change Screen.ActiveForm to
  14. ‘Screen.ActiveControl.Parent, thanks to NeoPa and his postings on
  15. ‘https://bytes.com/topic/access/answers/965032-audit-trail-subform
  16. ‘reply to satifali, Audit Trail – SubForm, Dec 1, 2015
  17. ‘This changed worked on my test database, will do a test on main system
  18. ‘that I have been working on, running on a network server, with three front ends
  19. ‘access the backend on a shared network drive
  20. ‘Marc 24June2017
  21.  
  22. Sub SOAuditChanges(IDField As String)
  23.     On Error GoTo SOAuditChanges_Err
  24.     Dim cnn As ADODB.Connection
  25.     Dim rst As ADODB.Recordset
  26.     Dim ctl As Control
  27.     Dim datTimeCheck As Date
  28.      Dim strUserID As String
  29.     Set cnn = CurrentProject.Connection
  30.     Set rst = New ADODB.Recordset
  31.     rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
  32.     datTimeCheck = Now()
  33.      strUserID = Environ("USERNAME")
  34.     For Each ctl In Screen.ActiveForm.Controls
  35.         If ctl.Tag = "SOAudit" Then
  36.             If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
  37.                 With rst
  38.                     .AddNew
  39.                     ![DateTime] = datTimeCheck
  40.                     ![UserName] = strUserID
  41.                     ![FormName] = Screen.ActiveForm.Name
  42.                     ![RecordID] = Screen.ActiveForm.Controls("SO DESIGN ID").Value
  43.                     ![FieldName] = ctl.ControlSource
  44.                     ![OldValue] = ctl.OldValue
  45.                     ![NewValue] = ctl.Value
  46.                     .Update
  47.                 End With
  48.             End If
  49.         End If
  50.     Next ctl
  51. SOAuditChanges_Exit:
  52.     On Error Resume Next
  53.     rst.Close
  54.     cnn.Close
  55.     Set rst = Nothing
  56.     Set cnn = Nothing
  57.     Exit Sub
  58. SOAuditChanges_Err:
  59.     MsgBox Err.Description, vbCritical, "ERROR!"
  60.     Resume SOAuditChanges_Exit
  61. End Sub
  62.  
Jun 25 '17 #16

NeoPa
Expert Mod 15k+
P: 31,492
I'm pleased to hear that Marc. I was struggling to know what to say. I'm glad you got it to work though. Good luck.
Jun 25 '17 #17

P: 11
Will test on Monday, my only worry now is the network server and making everything work over the network with a split database.
Jun 25 '17 #18

P: 11
To all that may try this, I did have to run four separate modules to make it track the 4 different subforms I have running. The call requirements are the same but I had to tag each field with the Module that was designated for that Sections table, I think this is because of different ID fields that each table is using. But with a little tweaking, the fix is working.
Jun 27 '17 #19

Post your reply

Sign in to post your reply or Sign up for a free account.