Audit Trail (1 Viewer)

[Aquadevel]

Gh,

I had tried your 'updated' version of audittrails in my app., but had problems.
Above you are saying to make a 'hidden' text control in each of the forms that you would track?

If so, thats probably where I went wrong, cause I 'co-mingled' versions of AuditTrails and had "Updates' on the forms.

My bad!

THanks,

After I polish up the user forms in the app I'm working on I'll try and add AuditTrails <again>.

Aqua

 

[ghudson]

My forms are using the text box named tbAuditTrail which is bound to the AuditTrail field in the table. I suggested hiding [visible = no] the tbAuditTrail since the users should not [in most cases] have access to the audit trail data.

 

[Aquadevel]

Gh,

yep, that was it!....too many hurricanes (real ones, not drinks )and not enough time for sleep.

Thanks!

Aqua

 

[FNQChick]

First off thanks for this audit trail ghudson. Next, I admit, that my knowledge of VBA is limited. I worked for a while last night trying to get this bit of code to write to a seperate table instead of the one that it is. Is that possible? If it is can someone point me in the right direction?

Thanks,
Matthew

mdcory, did you get this working?

I am interested in doing the same thing.

FNQChick

 

[Aquadevel]

Lyn?

Can you post your version of AuditTrails, as I'm still having a problem with it working on 2 forms that have subforms.

GH, you did great, it works perfect on main forms!!

Thanks,

Aqua

 

[DanG]

I'm sorry to bring this subject up again...
Great code! but like others I can't seem to get it working on subforms (but have got it to work on main forms of my databases).
From Lyn Mac's solution:

<q> My solution:

1. Pass the form as an argument of the function

Function AuditTrail(frm as Form)

2. Comment out these lines in the code

'Dim frm as Form
'Set frm = Screen.ActiveForm

3. Call the function in the BeforeUpdate event of the form and or subform as
follows:

Call AuditTrail(Me) </q>

I get the general concept but, concept and implementaion are two different animals. I have the following questions:
Step1: does this mean I make a "new" public funcion (module)?
if so sub or function? (I assume function) (I'm pretty new to VBA)
Step2: The actual line I see is "Dim MyForm As Form" not "Dim frm as Form" which I would make a difference in that the code uses "Dim MyForm As Form" down the pipe (which is one of the errors I got as I tried this on my own).
If you comment out "Set frm = Screen.ActiveForm" how does it pick up on a form?
Step3:Calling the funcion from step1 on the beforeupdate confuses me aren't I already calling the main code on the beforeupdate action? so which gets called first? or do I wrap both calls in one on the beforeupdate event?

I really have to apologize for my ignorance, one day I'm gonna wake up and I'll just know it all...at least that's what I tell myself.

Thank you very much.

 

[mchlle]

Try my attached sample. It is based on the How to Create an Audit Trail of Record Changes in a Form . I recently had a reason to use it so I had to figure out how it worked and altered it to my liking. I also made some changes to the original code to report Null values. Please post back if you have any suggestions. Thanks!

I have uploaded a newer version in the post below...

Hi
I have used your sample database and it works great. Thanks. I was wondering how would I do this for only one field? I have form that has hundreds of fields but only want to track historical changes on only the graduation date for our participants. But I would like to know on who's record the graduation date has changed for along with all the info your database captures such changed by user, change date, change time etc... and view it through a report.
Any comments would be helpful.

 

[DanG]

Does anyone have an A97 sample of how to use Lyn Mac's solution to the Subform problem?

I have tried on my own to work this out...I have tried to get pointers in the right direction...I have given great detail of what it is I don't understand about the solution and yet...here I am again.

Maybe if no one has the time to work through this with me at least someone has a sample I can look at?

Thanks

 

[DanG]

I found it!!!
For those of you who have had the same problem I have, here is where I found the answer..

http://www.access-programmers.co.uk/forums/showthread.php?t=55017&highlight=audittrail

The biggest problem with solving this one was this thread got too big and branched off into new threads...it was like a needle in a hey stack.

IT WORKS GREAT!!!

 

[ghudson]

Thanks for posting the subform solution "link" where those in need will be able to find it.

 

[KevCB226]

Sorry to revive this thread again, but this is exactly what I need. Although I am receiving an error message when the script runs:

13 - Type Mismatch

I am trying to get this to run on a form with a tab control, which is basically linked to a subform. I looked on the link above and if I try either the Call Audit_Trail(Form) or (me) it returns with the same error.

Script:

On Error GoTo Form_BeforeUpdate_Err
    
    Call Audit_Trail(Me)
    
Form_BeforeUpdate_Exit:
    Exit Sub
    
Form_BeforeUpdate_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume Form_BeforeUpdate_Exit

When I debug it highlights the last line of the script.

 

[GarageFlower]

I had that problem earlier. I found out that when a form with subform is displayed and edits are being done to the subform the active form is the parent form and not the subform.

This seems to be the effect that this line in the Audit Trail had:

Set frm = Screen.ActiveForm

My solution:

1. Pass the form as an argument of the function

Function AuditTrail(frm as Form)

2. Comment out these lines in the code

'Dim frm as Form
'Set frm = Screen.ActiveForm

3. Call the function in the BeforeUpdate event of the form and or subform as
follows:

Call AuditTrail(Me)


HTH,

Lyn

Could someone explain this to me please as i cant work out how to use it in my database to incorperate subforms within a form within the audit trail

 

[DanG]

been there done that..
We wrapped this one up on page 6, is there something there you don't undersand?

 

[GarageFlower]

been there done that..
We wrapped this one up on page 6, is there something there you don't undersand?

Erm well i think i get what it is saying

Have tried to implment it but still cant get it working.

I have read through the link you suggested on page 6 and am still none the wiser

is there any examples of it working online?

 

[DanG]

Sorry it took so long.
Look at the "notes" on the subform then work from there.

 

[GarageFlower]

Sorry mate. which notes would these be?

Also i cant really see how this works

On my sub form

i add in on before update

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err

Call Audit_Trail(Me)

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_BeforeUpdate_Exit
End Sub

But then i look at the audit trail on the parent form and it just says changes made but doesnt list any of the actual changes.

Do i need Audittrail memo fields in every table?
Or does it save the sub forms changes all in the parent tables audittrail field?

 

[GarageFlower]

Could you take a look at this
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=11532

and show me how to incoporate the subforms into the audit trail

 

[kedarj]

I have tried the Audit Trail 2003 from gHudson and have bene struggling since last few hours on this one and few weeks on the one before. I would appreciate some help.
I have a form whihc has got about 5 tabs on it. when I update a field on he first tab, it updates correctly n the Audit Trail field mentioneing which field was changed from what to what. There is also a combo box which works correctly. There are few other combo boxes on the same tab, which do not reflect the changes in Audittrail field. Although it records that there was achange, what was changed is not recorded. Same is the case on all other tabs. Can someone please help...
Regards
K

 

[kedarj]

Hi Rob1234,
can you please elaborate how you used the RecordClone Property?
Thanks

 

[JohnD]

The Audit Trail has been working great in my currently messed up DB

I did like a few suggestions said, I have the Audit Trail that can be turned on by an invisible command button that then prompts a PW. After the correct PW it opens the Audit Trail for view. It then can be closed by another invisible cmd button - works like a charm.

My question to add to this thread is this...Can the Audit Trail or something of the likes, track when someone opens up the DB and what forms they have accessed (or tables / queries etc). So essentially it doesnt only track changes of the data, but it tracks the actuall user.

In all honesty, I dont need this but have been talking about this with my tech team and they seem to think its impossible - either way, its simple curiosity.

John D