Audit Trail Idea (2 Viewers)

[Uvuriel03]

I'm aware that there is already a looooooong thread about creating an audit trail. Here's why I'm posting this new thread.

First of all, the audit trail I have set up (yes, from the thread mentioned above) is only saving the first couple of lines of data--244 characters worth, I think, in the memo field. I found this link about it: http://allenbrowne.com/ser-63.html, but as far as I can see, I don't have any of the issues he listed.

So I did some more browsing around at Audit Trail solutions and found this: http://www.indywebshop.com/bestpractices/2006/07/28/leaving-an-audit-trail-in-your-database/. I REEEEALLY like the idea this guy has, but I got very little sleep last night and unless my brain is just totally shot, I don't think he has an actual working example of said idea.

So my point is, I would like to make an audittrail like the second one, but I have no idea how, nor can I create modules. I would LIKE for the audittrail to be stored in a separate table, since the database is going to be relatively huge with a lot of changes. However, I'm not that advanced with coding yet, and I have no clue where to start.

Does anybody have any suggestions? Or would anybody be willing to give this a go?

Eternally grateful,
Rachel

 

[HiTechCoach]

Here are some working examples that you can use in your database:

From the Utter Angel:
Audit Trail

This db demonstrates how to set up an audit trail of changes to the data including ID, Field Name, the field value before it was changed, the field value after it was changed, the user who did the change, and the date/time the change was made.

and also

Audit Trail Database - Made SUPER Simple

Audit Trail for Access 2000

Audit Trail revised

Hope these help ....

 

[Uvuriel03]

Awesome, thank you HiTech. I'll check these out and see what I can do with them!!

 

[irish634]

Here's Mine that seems to work well. Stores in a separate table.

 

[Uvuriel03]

Irish, I was taking a look at your code, but where exactly do I put it? Do I store it as a module? If so, what should I save it as?

And also, I have a split database. Is that going to make a difference?

Thanks!
Rachel

 

[irish634]

Irish, I was taking a look at your code, but where exactly do I put it? Do I store it as a module? If so, what should I save it as?

And also, I have a split database. Is that going to make a difference?

Thanks!
Rachel

In vba, Insert a module then paste it there.
In the BEFORE_UPDATE event on the FORM paste this:

Call AuditTrail(Me.Form, [RecordID])

The "recordID" is your form's auto number or primary key.

Having a split database should not make a difference. Just create the table in the backend then link to it in your front end.

You'll have to create your own query/report, but this should work ok for you.

 

[Uvuriel03]

Hey Irish,

I'm wondering if you can help me out. I'm kind of lost when I'm looking through your modules.

Could you help me add a column to the audit trail for Company Name? Some of the companies have overlapping IDs (bad form, I know, but else it would require MASSIVE amounts of restructuring which is simple not feasible), so I don't have a unique key outside of the combination of [company]&[id].

So, if I could have the Company column in there along with whatever tweaks to the module/s that are needed, I'd be all set! And I'd be grateful forever!!

Thank you!

 

[gemma-the-husky]

dont know if this is any good for you - i have a table called auditdata consisting of

a text string (what the audited event is)
a date (when it happened)
a username (who fired the event)
an auditclass (to distiguish different types of audit event)
and some other thnigs that are not so important for this purpose

then in your code, if you want to save/audit an event just call this sub

[COLOR="red"]'call this sub to save an audited event[/COLOR]
Sub auditme(ItemText As String, Optional auditclass As Long, Optional showerror As Boolean)
Dim mycomp As String
Dim mylogin As String
Dim sqlstrg As String

mylogin = CurrentUser
[COLOR="red"]'some function to get ther computer name[/COLOR]
mycomp = GetComputerName

If IsMissing(auditclass) Then auditclass = 0
If IsMissing(showerror) Then showerror = False

sqlstrg = "insert into audit_data (audittype,auditdetails,auditdate,auditwho,auditterminal,auditcleared) " & _
    "select " & _
    auditclass & ", " & _
    Chr(34) & ItemText & Chr(34) & " , " & _
    "#" & format(Date,"long date") & "# ," & _
    Chr(34) & mylogin & Chr(34) & " , " & _
    Chr(34) & mycomp & Chr(34) & " , " & _
    False

On Error GoTo fail
currentdb.Execute sqlstrg, dbFailOnError

exithere:
    Exit Sub
    
fail:
[COLOR="Red"]'if you want to see the error[/COLOR]
     If showerror Then MsgBox ("Error: System was unable to record this action in the audit trail. " & vbCrLf & vbCrLf & _
        "Error: " & Err & "   Desc: " & Err.Description)
    Resume exithere
End Sub

 

[irish634]

Hey Irish,

I'm wondering if you can help me out. I'm kind of lost when I'm looking through your modules.

Could you help me add a column to the audit trail for Company Name? Some of the companies have overlapping IDs (bad form, I know, but else it would require MASSIVE amounts of restructuring which is simple not feasible), so I don't have a unique key outside of the combination of [company]&[id].

So, if I could have the Company column in there along with whatever tweaks to the module/s that are needed, I'd be all set! And I'd be grateful forever!!

Thank you!

Here's an example of how you can do that.
Good luck.
View attachment Audit_Tracking.zip

 

[Uvuriel03]

Thanks so much for helping me out this far. Unfortunately, I'm not very familiar with modules, and I'm having difficulty figuring out exactly what I need to change to adjust it to my own database. What information do you need to help me out with this one?

Thanks so much!

 

[irish634]

Thanks so much for helping me out this far. Unfortunately, I'm not very familiar with modules, and I'm having difficulty figuring out exactly what I need to change to adjust it to my own database. What information do you need to help me out with this one?

Thanks so much!

1. Import the "mod_AuditTrail" into your database.
2. In the before update event of the form you want to track put this:

On Error GoTo Error_Handler

    Call AuditTrail(Me.Form, [txt_Company])
    
Error_Handler_Exit:
   Exit Sub

Error_Handler:
    MsgBox ("Error No: " & Err.Number & vbCrLf & vbCrLf & "Error Description: " & Err.Description)
    Err.Clear
    Resume Error_Handler_Exit

Keep in mind that you need to change [txt_Company] to whatever the Company field is on your form.

Then type some changes in the form and it should create the table and log the change.

 

[Uvuriel03]

I'm getting this error when I try to save the changed record:

Any ideas why it'd give this error?

Thanks

 

[HiTechCoach]

I'm back ....

Do you have have a module with the AuditTrail() code in it?

If you do, then you also meed to make sure the the module name is not the same name as any sub/function withint the module so Access does not get confused about what are referencing.

 

[Uvuriel03]

I imported the module straight from Irish's example.

Here's an example of how you can do that.
Good luck.
View attachment 25650

 

[HiTechCoach]

I imported the module straight from Irish's example.

Do you maybe have two procedures with the same name, but in different modules?

Have you tried to compile the VBA code to see if you get any other error messages?

 

[Uvuriel03]

Nope, the one I imported is the only module I even have. I have compiled the VBA, and that's the only error I get. Or at least, the only one I can get TO, since I can't cancel that error and continue or anything.

 

[irish634]

If you have imported the module as I have described make sure all of your references are selected:


You can also try these:

AuditTrail (Me.Form, [Company])
AuditTrail Me.Form, [Company]
Call AuditTrail Me.Form, [Company]

I agree with HiTech though, it sounds like the module is missing or there are two of them.

I don't have Access 2007, but I did check the sample in Access 2007 runtime and it works as I have sent it to you.

 

[Uvuriel03]

This was as close as I could get for the References...

I used the next highest number I had for the ones that didn't match exactly.

I'm still getting the same error, though...

 

[irish634]

Did you try the 3 other ways to call the function?

Put an apostrophe ' in front of the Call AuditTrail and change it to a comment. Now try to compile and see what happens.

Does the sample I gave you work? Just open the sample and type some changes. Do you get the same error?

It still sounds like something is missing or duplicate.

 

[Uvuriel03]

I did try all three calls, sorry. And when I comment it out, nothing happens, no errors, no nothing.

Would it be possible that I'm getting this error because data field types and/or names aren't matching up or something?