audit trail using timestamp

[Geordie2008]

Hi all,

I have form which I would like the end-user to be able to use to update fields, for example:

First Name
Last Name
Building
Country

etc...

trouble is I need to be able to track changes. I have a unique StaffNumber with I have used as the key. So if a staff member changes Building I need to be able to track where they are now and where they were in say Dec-07.

In order to do this I wanted to populate a form with the current values held on record. I have done this by filtering on a form and returning the employee details filtered by Staff number (the form populates with all of the building, first / last name details etc)

Next I would like the user to be able to make a change which will then re-populate a line of data within the database (and NOT overwrite the historic data). I would like a time stamp on when the change was made to allow me to run reports using the timestamp.

e.g.

Mandy | Smith | London | Liverpool Street | 01/01/2005
Mandy | Jones | London | Holborn | 24/03/2008
Mandy | Smith-Jones | London | Holborn | 25/03/2008

Can anyone please help me with this?
Thanks,
Mandy

 

[Geordie2008]

I have read several (and there are hundreds!) of posts on audit trails using the search forum on this site... none of them really seem to do what I need.... the Ghudson solution looked very good, but then it only updated for new fields and I would like to populate the entire row again with the change in place so that I can run reports off the back of the data....

reports such as a graph of which country staff were based at in Dec-07 versus Dec-08 etc... and hence I would like the field repopulated as shown in my earlier post.

Is there a thread / link that looks at an audit trail in the way that I have described?

Im very new to VBA but looking forward to learning anything I can!

Thanks for any help,
Mandy

 

[Alc]

You may be able to make something of the following.
I use one function to add a record to the audit table every time something-or-other happens. This means calling the function and passing in the name of the table affected and what happened.
The date is stored as text purely because I inherited the table when I got the job and my predecessor had it running that way.

[I]Example of a call[/I]   
 'Record the action
 '-----------------
  str_Table = "N/A"
  str_Change = "Opening form number one was successful"
    
  Call Record_Data_Change(str_Table, str_Change)

'---------------------------------------------------------

Function Record_Data_Change(str_Table As String, str_Change As String)
    Dim ld_Date As Date
    Dim str_SQL As String
    Dim str_Person As String
    
    DoCmd.SetWarnings False
    
    'Set date
    '--------
    ld_Date = Now
    
    'Insert details of change made
    '-----------------------------
    DoCmd.SetWarnings False
    
    DoCmd.RunSQL "INSERT INTO Database_Useage " & _
                 "(TableName," & _
                 "Activity," & _
                 "Activity_Date) " & _
                 "VALUES" & _
                 "('" & str_Table & "'," & _
                 "'" & str_Change & "'," & _
                 "'" & ld_Date & "');"                
    
    DoCmd.SetWarnings True
End Function

 

[gemma-the-husky]

this is enormously complex, i feel

by (effectively) duplicating/multiplicating each record, you ALSO need some other identifier to indicate the active record, or an outmoded history record - you then need every query using these tables to ONLY find the active record, or you may find yourslef with some sort of cartesian product, as every potential match gets returned.

you need to be particularly careful with dlookups and sql statements etc, as they will return a random match from candidate matches

 

[Geordie2008]

I was intending on using a max function on the date in order to get the most current field.

I only have 2000 rows of data to contend with and the changes are likely to be infrequent (I hope!)

i really cant think of any other way of getting the results I need...

"the powers that be" would like reports detailing where staff were before and where they are now, when staff have moved department, where did they go and where were they before.

e.f. they would like graphs on how many people were in advertising in Dec-06 and how many people are in advertising in Mar-08. Why did these people leave? Internal moves / external moves / maternity leave etc....

I cant think of how else to do this, I figured that at least by time stamping EVERY single change, all of the data would be available to me and I would just need to be smart about how to query the results....

any advice mucho appreciated however, I am slightly worried about the scale of this project to be honest. I voluntered myself as someone who uses access "now and again" and this is a huge learning curve for moi!

Would using the Max function as described still worry you gemma-the-husky?

Thanks,
Mandy

 

[gemma-the-husky]

no, i think if you understand the implications you wont have a problem.

from what you are saying this is more of a personnel tracking database then, rather than an accounts type database. in that case, yes your transactions are likely to be less frequent in this sort of environment, and yes its probably a good way to be able to retrieve the statistics you are looking for.

 

[Mike375]

Mandy

Something I do in my telemarketing data base might be applicable. When I bring on someone new there are always screen problems for a couple of days and they will also crop up at later dates.

The screen has many labels that do such things as open diary, type of call result etc and etc. Behind each label is a macro that does what is needed but in addition each macro has a couple of action lines which open a new record in a table and with SetValue it sticks in Now(), the name of the label that was clicked, the name of the telemarketer and the ID of the prosect.

Thus I can track when and where they were on the screen. Obviously in my case this enables me to see what they were clicking when they had a problem.

Perhaps something similar could work for you.