Audit trail not working on form with subform

[RuralGuy]

As I mentioned in post #6, the code is designed for a MainForm only. I just did a test and this line: Set MyForm = Screen.ActiveForm points to the MainForm regardless of you calling it from the SubForm or the MainForm.

[jpkeller55]

Thanks RG...I did make a modification to the code in #12 to allow the code to work on the subform as well.

Function AuditTrail(MyForm As Form)
On Error GoTo Err_Handler
    
    Dim C As Control, xName As String
    'Set MyForm = Screen.ActiveForm
    'Set date and current user if form has been updated.
    MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
    "Changes made on " & Now() & " by " & Environ("USERNAME") & ";"

In searching other posts on this subject, the particular line of code you identified was the problem getting the code to work with subforms so it was REM'd out.

[RuralGuy]

Sorry, I lost track of the changes. You are correct about post #12. Darn, I hate it when that happens!

[jpkeller55]

No problem RG...this post has gotten too big and difficult to follow. I would like to close it out but it really isn't completely solved. Thanks for taking the time to look at it.

[RuralGuy]

Keep the thread open until we pin down what is going on. We'll get there.

[RuralGuy]

I would like to see a test that enumerates the controls of a SubForm. I'm not so sure you can access the Control collection of the SubForm this way in your For...Next loop. The SubForm is *not* in the FORMS collection and may therefore need to be accessed through the MainForm.SubForm type of syntax. I'll try and put together that testbed and see what happens.

[RuralGuy]

OK...that test proved me wrong. It does enumerate the correct control collection. Back to the books on this one.

[jpkeller55]

Right...I am having no problem with the controls being enumerated on both the sub and the main. Somehow, I need the code to be able to identify when it is finished with the subform controls and starting on the main form controls so it can print the Date/Time info to where the main form audit trail is stored.

[RuralGuy]

What event in both the MainForm and the SubForm do you use to invoke this procedure?

[jpkeller55]

Before Update

[RuralGuy]

That is the Form_BeforeUpdate event of the MainForm and the SubForm right? Do you know how Access fires those events? If you have made changes to the MainForm that Dirties the MainForm RecordSource then Access will save that record when you move the focus from the MainForm to the SubForm or if you try and leave the MainForm record for any other reason as well (Exit, Close the form, NextRecord...). The same thing happens when you move the focus in the SubForm. If your SubForm is in Continuous Form view then moving to the next record will save if the record is Dirty. Also moving the focus back to the MainForm will save if the Current record is Dirty. Is that your understanding of the process?

[jpkeller55]

Yes, it is the Form_BeforeUpdate for both main and subform. And yes, that is my understanding. I follow.

[RuralGuy]

So I re-read your post #29 for the 50th time and a light is starting to glow. Are you saying that you want the AuditTrail to record the fact that the user made a change to the SubForm in the MainForm AuditTrail control, even though no change was made in the mainForm record?

[RuralGuy]

Did I lose you there? Do I have it all wrong?

[jpkeller55]

Sorry, got sidetracked with work. If a user makes an edit to the subform (responses), I only need the audit trail to record in the tblQuestResponseSet. Do not need to make an audit trail in the Main form audit trail. That is working correctly. Conversely, if i make an edit on the main form (question) but no change on the subform, the audit trail should go to tblQuestions. This is also working correctly - I will see the change in the audit log on the tblQuestions and nothing on the tblQuestResponseSets. But, in this last scenario, the Date/Time part is missing on the main audit log. In the first scenario, the Date/Time prints on the subfrom audit trail (tblQuestResponseSets). In fact, in every test case I have run, each time the audit trail on the tblQuestResponseSets (subform) is correctly stamping the date/time.