Audit Trail into Subform

[AdamMVRRS]

Hi Everyone,




Sorry for posting a thread that pops up a lot on the internet, but I haven't been able to find a solution in the past couple hours of searching. I'll try to explain my situation the best that I can.


I have a form with a few tabs and 3 subforms. I used the following Audit Trail http://www.fontstuff.com/Access/acctut21.htm to complete wha I have so far and it's been really great. Simple, easy to use and works well. The only issue that I have is it doesn't read the edits from the sub forms. It tells me when a new record is created or deleted but I can't see if anything was changed or what was input into the new record.


I believe it's something to do with Screen.ActiveForm but I can't be certain.


Below is the code that I use in a module and the code on the event section of the forms and the module that I use. Also all the controls I want audited have Audit in the tag of the Other menu.


BadAudit Module


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 tblAuditTrail", 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.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Controls(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.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
.Update
End With
End Select
AuditChanges_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
AuditChanges_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume AuditChanges_Exit


End Sub




Learner Details - Main Form


Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = acDeleteOK Then Call AuditChanges("MVRRS Learner ID", "DELETE")


End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Call AuditChanges("MVRRS Learner ID", "NEW")
Else
Call AuditChanges("MVRRS Learner ID", "EDIT")
End If
End Sub


The After Del and Before Up are also on the forms SuspensionsSubFrm, ReviewsSubFrm and Framework which are all subforms.




Thanks to anyone who can actually help me in this little mess!

[NTC]

if I understand your post; auditing to the degree where one is able see everything changed or input new in a separate audit file - is very unusual. that would be very duplicative/write intensive & tending toward duplicating all db activity.....

most auditing is writing the time/date stamp and the user's id to a record when written or changed. that's it. one can then, via query on the date/time stamp - review the content of altered/added records from within their regular tables. writing those queries depends of course on your data structure and not unique per say to the auditing task.

[ItsMe]

...It tells me when a new record is created or deleted but I can't see if anything was changed or what was input into the new record....

Have you tried using the .Requery method on your form or subform?

[AdamMVRRS]

if I understand your post; auditing to the degree where one is able see everything changed or input new in a separate audit file - is very unusual. that would be very duplicative/write intensive & tending toward duplicating all db activity.....

most auditing is writing the time/date stamp and the user's id to a record when written or changed. that's it. one can then, via query on the date/time stamp - review the content of altered/added records from within their regular tables. writing those queries depends of course on your data structure and not unique per say to the auditing task.

Thanks for your response. I wasn't aware that it was unusual as it was one of the more popular auditing methods that I've seen. And in light of recent events within our admin team we really need to see things that were changed and what they were changed too. Will the method you describe remove the ability to see the changes? I know it is an intensive method but our database is very small with about 30 updates a day.

Have you tried using the .Requery method on your form or subform?

I haven't, no. Could you give me some more advice on that? Thank you!

[ItsMe]

.Requery is a rather basic and popular tool. Because you are not familiar with it, I recommend you analyze your workflow and concentrate on post #2 before you get too concerned with how to requery a form.

[AdamMVRRS]

.Requery is a rather basic and popular tool. Because you are not familiar with it, I recommend you analyze your workflow and concentrate on post #2 before you get too concerned with how to requery a form.

I believe I've requeried a combo box before. Will requerying help me identify what was changed in the sub forms?

[ItsMe]

loosely stated...

Changes that happen in a table may not be reflected in a form/subform until after a Requery of said form/subform

[orange]

Can you describe in simple, plain terms What you want to audit for what purpose?
As NTC mentioned it seems a little excessive, but you know your circumstances and we don't.

How many users?

[AdamMVRRS]

loosely stated...

Changes that happen in a table may not be reflected in a form/subform until after a Requery of said form/subform

Okay thanks that puts me on a starting point

Can you describe in simple, plain terms What you want to audit for what purpose?
As NTC mentioned it seems a little excessive, but you know your circumstances and we don't.

How many users?

This is passed down from management - there have been a lot of issues with admin inputting into the system and blaiming the system on tech faults. When in fact if I didn't have the audit log we wouldn't have been able to pick out individual issues and say no this was changed to this at this time etc. It has helped identify a lot of problems and allowed us to bring it up with the team. 4 read/write users and about 8-10 read only.

Is that any help?

[orange]

OK, so management wants some answers/comfort in responding to questions related to data/system flow/users...

Have you identified the types of problems (the source of the blaming)?
Do have edit validation routines on the data being input before saving the values?
Can you tell us a little about the "typical blaming" issue? Is it real? What is the source of the issues?
Have users been trained in the system/database?
Is the system well designed and tested?

[Xipooo]

I have also used a function to pass all commands which acts as a batch to perform the various actions. Within that batching routing you can add logging to record every call to it and it's success or failure. This is also very handy for recording debug errors.

This also negates all of the necessity of adding and working with tags for everything and simplifies your troubleshooting.

It doesn't require much refactoring either because you'll be calling the subs and functions you've already created. You just assign the new function to each one of your events available to your user.

public sub RunCommand(CommandName as String)
Application.run CommandName

// Enter logging code here

end sub

Then just use =RunCommand("CommandName") in all of your event handlers of your form.