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

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

From the Utter Angel:
Audit Trail (http://www.utterangel.com/utterangel.aspx#4)

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 (http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1576962&Zf=f48&Zw=audit%20trail&Zg=0&Zl=a&Main=1576962&Search=true&where=&Zu=&Zd=l&Zn=&Zt=5&Zs=a&Zy=#Post1576962&Zp=)

Audit Trail for Access 2000 (http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=98965&Zf=f48&Zw=audit%20trail&Zg=0&Zl=a&Main=98965&Search=true&where=&Zu=&Zd=l&Zn=&Zt=5&Zs=a&Zy=#Post98965&Zp=)

Audit Trail revised (http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=220783&Zf=f48&Zw=audit%20trail&Zg=0&Zl=a&Main=220783&Search=true&where=&Zu=&Zd=l&Zn=&Zt=5&Zs=a&Zy=#Post220783&Zp=)

Hope these help ....

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

Here's Mine that seems to work well. (http://www.access-programmers.co.uk/forums/showthread.php?t=157566) Stores in a separate table.

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

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.

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!

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


'call this sub to save an audited event
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
'some function to get ther computer name
mycomp = GetComputerName

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

sqlstrg = "insert into audit_data (audittype,auditdetails,auditdate,auditwho,auditte rminal,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:
'if you want to see the error
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

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.
25650

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!

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.

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

http://www.fileden.com/files/2006/7/13/128238/error.png

Any ideas why it'd give this error?

Thanks

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.

I imported the module straight from Irish's example.

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

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?

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.

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

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.

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

http://www.fileden.com/files/2006/7/13/128238/Differences.png

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

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

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.

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?

Does the sample I attached work for you in Access 2007?
Take a screen shot of the start of the module in VBA.
Did you rename the Audit Trail by chance?
Use the "Find" function in VBA and find out where each "AuditTrail" word is. (To find out if you have duplicate procedures).

Other than that, maybe someone with Access 2007 can help you figure it out.

1. The sample you sent does work.

2. http://www.fileden.com/files/2006/7/13/128238/StartVBA.png

Hope this is what you were looking for. =P


3. I did not rename the module.

4. I did try the Find function, actually, when it was suggested that there might be a duplicate, and there were only the references in your module and the one where I had put the code in a form's BeforeUpdate event.

1. The sample you sent does work.

Well, then the next step I would try is this:


Disable your line again.
Delete the module
Import everything from the database sample that works (tables, forms, module, etc)
Compile and see what happens.
Try to run the audit trail in the form you just imported (just like in the example I sent).

If it works then, try adding your call again.

Steps 1-3 were successful.

Step 4 note: The "Compile" option was grayed out.

So I de-apostrophized my call line and...

Same error.

Eh.

This is getting ridiculous. Thank you guys for hanging in with me on this tedious chore. :/ I somehow doubt this should be so difficult...

Steps 1-3 were successful.

Step 4 note: The "Compile" option was grayed out.

So I de-apostrophized my call line and...

Same error.

Eh.

This is getting ridiculous. Thank you guys for hanging in with me on this tedious chore. :/ I somehow doubt this should be so difficult...

Disable your call line.
Open the form you imported from my sample... Make a change. Does it work? Can you compile then?