Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2015
    Posts
    23

    Answered: Track changes in AuditTrail table

    I am tracking changes perfect in tblAudiTrail but the only thing not working is the fieldName
    I want it to give me the field name that I changed in another table rather than giving me that field value..
    Please check my screenshot.. Thanks!!!
    Click image for larger version. 

Name:	AuditTrail.png 
Views:	9 
Size:	43.0 KB 
ID:	16778

  2. Best Answer
    Posted by healdem

    "if you are using the ACCDx format of file as opposed to the MDx then as Allan Browne suggests using a trigger (implemented as macros) is fine. if you are using the older MDx file then you have to insert code in the relevant event hooks. Allen Brownes article covers audit logs in good detail

    it depends waht you are trying to do.

    what i have done in the past is use the forms events to do the work
    one version Ive sued wrote native SQL. EG:-

    UPDATE mytable, set columnx = 'blah', columny 1.23 WHERE myPK = 87699786

    another recorded pairs
    eg
    user: alipezu
    computer: wks005-5
    time: hh:mm:ss
    changed........

    form A provided a means of easily transferring data as a series of sql statements that merely updated a remote duplicate, without havign to take that duplicate off line, or worry about autoneumebrs and so on. efeectively it implemented a slave DB appraoch

    form B provided a more legalistic approach recording who did what and when, used where there was a need to know precisely who had done what

    you cna of course store the computer ID & username in either form

    I prefer the SQL approach as it means its actually relatively trivial to restore the data form a precise point in time and then roll forward all SQL statements till the db is back to where it should be.

    but form b is just as good and may be more legible to non db people... i have seen it used for disciplinary and in some cases employee termination. its clearer and simpler to read which alwasy appeals to the legal profession

    BUT bear in mind your audit log MUST be write only, no changes to existing rows, no deletes allowed if its to be used in a legal context."


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    In the form, when it loads, you'd need to have an event code in every field.
    In the BEFORE update event, you'd need to see if the before value is different from the after value, then send that field to the table....
    Code:
    Sub txtZip_beforeupdate(cancel)
       If txtZip<> mvPrevZip then PostLog "zipCode",UserId()
    End sub

  4. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if its a bound control then then the name of the column should be available in the control source property. if you don't know what a bound control is then almost certainly you are using bound controls
    I'd rather be riding on the Tiger 800 or the Norton

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are using the ACCDx format of file as opposed to the MDx then as Allan Browne suggests using a trigger (implemented as macros) is fine. if you are using the older MDx file then you have to insert code in the relevant event hooks. Allen Brownes article covers audit logs in good detail

    it depends waht you are trying to do.

    what i have done in the past is use the forms events to do the work
    one version Ive sued wrote native SQL. EG:-

    UPDATE mytable, set columnx = 'blah', columny 1.23 WHERE myPK = 87699786

    another recorded pairs
    eg
    user: alipezu
    computer: wks005-5
    time: hh:mm:ss
    changed........

    form A provided a means of easily transferring data as a series of sql statements that merely updated a remote duplicate, without havign to take that duplicate off line, or worry about autoneumebrs and so on. efeectively it implemented a slave DB appraoch

    form B provided a more legalistic approach recording who did what and when, used where there was a need to know precisely who had done what

    you cna of course store the computer ID & username in either form

    I prefer the SQL approach as it means its actually relatively trivial to restore the data form a precise point in time and then roll forward all SQL statements till the db is back to where it should be.

    but form b is just as good and may be more legible to non db people... i have seen it used for disciplinary and in some cases employee termination. its clearer and simpler to read which alwasy appeals to the legal profession

    BUT bear in mind your audit log MUST be write only, no changes to existing rows, no deletes allowed if its to be used in a legal context.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    Nov 2015
    Posts
    23
    Thank you healdem for your detailed answer, and Im using ACCDx file format.. Iam glad to have answers for all my asked questions here.. thnks to all who helped me here..

Posting Permissions

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