Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2009
    Posts
    9

    Unanswered: Date audit trail

    When changing a date in a subform I need to save the old date every time its changed to form an audit trail. Help plz.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is a solution:
    Code:
    Private Sub AnyDate_BeforeUpdate(Cancel As Integer)
    
        Dim strSQL As String
        
        strSQL = "INSERT INTO Tbl_AuditTrail (AnyDate) VALUES (#" & Me.AnyDate.Value & "#)"
        CurrentDb.Execute strSQL, dbSeeChanges
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Aug 2009
    Posts
    9
    Thanks Sinndho, I'm a novice with code I know I have to substitute (anydate) with the field name but I also need these dates with the corresponding Prop_code primary key. Also I have code already in the beforeupdate can I run two beforeupdate codes?

    Jim

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Indeed - just storing a series of dates is not going to be much use to you;

    Here is a link to a downloadable example of a 'History Log' from Dev Ashish' 'The Access Web'.

    This will enable you to store the corresponding Prop_code primary key and also machine name / network name / username etc.

    Modules: Maintain a history of changes

    If you have any difficulty implementing this, post a reply to this thread.
    Last edited by garethdart; 09-14-09 at 07:06.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Aug 2009
    Posts
    9
    Garethdart, Sorry I should have provided more specific info, its not the date that something is changed I want to keep it is an actual date input in a form that relates to when a gas boiler is serviced. I need an audit trail of previous service dates to demonstrate it has never been greater than one year between gas services. As you rightly say a list of dates is no good, they must be saved in a table against the individual property code field (each house's primary key code).

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    That module will enable you to keep track of all database changes.

    However, you can use a similar method if you are only interested in basic record update information, I'm guessing you want:

    *DateTimeOfInput and RecordID (*Unless this is a date 'Input' by the user; 'datLastService'???)

    Firstly
    You need a table in your database to store this information.

    Add a new table called say tblAuditLog
    Add an Autonumber field say intAuditID and set as a Primary key
    Add a field of the same datatype (Number?) of your RecordID say intRecordID
    Add another field of type Date/Time say datDateEntered or datDateServiced

    During the event you wish to capture - (Not sure if you want to do this every time the user edits a record, a specific field or whatever??), append this table with the required information.

    You can then have a query to find the last date that a house was serviced and identify any say >1 Year, coming up to >1 Year etc.

    I can post a .zip example of this later (prob early eve.) if this is not enough information.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  7. #7
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Hmm - I've just had a thought;

    With the correct database design, you shouldn't have to do this anyway - can you post your table structure or even a blank .zip version of your database?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  8. #8
    Join Date
    Aug 2009
    Posts
    9
    It is a date input by the user 'Lastservicedate'
    Please find attached table structure as requested.
    Attached Files Attached Files

  9. #9
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    "to demonstrate it has never been greater than one year between gas services"

    Do you have a table of addresses which has a relationship (even if intermediate) with the 'Last Service Dates' table? - Maybe RO1? Difficult to tell from the doc.

    If so, you should be able to write a query to do this from your existing table structure - you don't need to 'log' a user entering this information.

    Can you provide an example of how you would like this report / form to appear - It should be a simple query.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  10. #10
    Join Date
    Aug 2009
    Posts
    9
    I think the problem could do with more definition. We are audited to ensure all our properties have a current gas certificate and they also go back to the previous certificates to ensure that the dates were always within 1 year of each other. When we recieve a new gas certificate the user inputs the date in a form field named Lastservicedate and this updates the table by replacing the previous date of gas cert. Therein lies my problem, this overwrites the previous dates which no longer exist in the db.

    Address table is a mixture of R01 and R02.
    I don't need to log the user doing the updating
    The report would show the address and say last 5 previous cert dates and would highlight where any were more than 1 year between them.

  11. #11
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    "Therein lies my problem, this overwrites the previous dates which no longer exist in the db."

    This is a 'normalization' issue (google 'database normalization');
    Your underlying database structure is flawed.

    You should probably have a 1 to many relationship between *Properties and certificates. (This may not be the only issue).

    (*If a property can have >1 appliance 1 to many between applicances and certificates.)

    By logging each user entry you could 'get around' this, but it is NOT the way to go - You need to sort out your database normalization; once this is done then the query you require is very simple.

    This is going to require a little effort and may require some input from other dbForums users.

    I think that you should maybe seek assistance in the database concepts and design section:

    Database Concepts & Design - dBforums

    Perhaps use 'Normalization assistance for existing database' or similar as your title and link back to this thread.

    If an admin can look at this thread, maybe they could offer further suggestions or even re-locate this thread.

    I hope this is of assistance - once you have the structure sorted, I will gladly help you with any coding and query assistance, although I think the design issues would be better addressed by a.n. Other member.

    Good luck and let me know how you get on.

    GD
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  12. #12
    Join Date
    Aug 2009
    Posts
    9
    Tracking data changes in Access

    Got excellent audit code from above. Take your point about normalisation but I link some of the tables from an oracle database thro 3rd party software so its messy. But thanks again for your time.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you need an audit trail then each separate event needs capturing
    so overwriting isn't an option
    that measn that youneed to push some data into a subtable

    eg
    customersite
    |-- customerVisit

    you need toenforce a write only characteristic on the db, so that users cannot modify data per se, you cna intercept all chanegs to the row in a form and write a new record, however the big stumbling block in Access when using JET is that you cannot make JET itself secure enough for this purpose. JET doesn't have tools and sophistication of server products, nor does it have the security. so if you applciation requires soem form of legal paper trail then you shouldn't use JET as your backing data store as its virtually impossible to make JET secure enough to pass a detailed legally compliant security check.

    however in most cases there is a perfectly acceptable workaround which is to print off reports and file 'em. you can also print off reports as Acrobat documents and providign you also record the file creation date and file size its possible to demonstrate a legal compliance trial and not have to file paper docuemnts
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Aug 2009
    Posts
    9
    The only user input is the date of the new gas safety certificate. I have a frontend form that allows them to input this date and print letters reports etc. So this single field audit trail is perfect. It has also allowed me to query all the past date changes to ensure none have gone outwith the 1 year max between any 2 date changes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •