Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: Can you capture how many times a record has been updated?

    In a particular column.

    Example:

    Record 1 has been updated from Access 3 times total, in this particular column it would show 3. It gets updated again it rolls to 4?

    I tried Googling but so far no dice.

    Thanks for any hyperlinks or resources to accomplish this.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes you can, but not automatic ally, you have to do the work yourself

    Assuming your version of Access (well JET as thats the underlying db storage) supports a trigger, then I'd suggest using a trigger to do 'something' every time an attempt is made to insert or modify a row in whatever table(s) as required. the advantage f using a trigger is that its virtually impossible to sidestep the process. Access/
    JET is very hard to truly tie down securely, Access connected to a server is capable of tieng down securely quickly, but plain vanilla Access / JET is all to easy to circumvent. if you use a trigger then the process happens every time sopemo9ne actually makes a change tot hje underlying data in that table. the seemingly attractive option of dong this programataiczally yourself is that you have to insert the audit code everywhere changes are made. you woudl then have no knowledge of data changed by a query through a form, or directly throiugh a table edit

    as to what that 'somethign' is depends on you.
    it could be just a passive counter row x modified n times
    it could be a full blown audit trail recording who made what changes from what computer and when
    The excellent Allen Browne site has a good section on audit logs/audit trails
    you can find who and what computer is being used via the API calls (google 'dev ashish api')
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Lot's of awesome information here, thanks a lot!

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Obviously you need a Numeric Field, in your Table, to store the data; let's call it UpdateTally:

    To include the original creation of the Record
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
     Me.UpdateTally = Nz(Me.UpdateTally, 0) + 1
    End Sub


    To only include actual updates, i.e. not the original creation of the Record
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
     If Not Me.NewRecord Then
      Me.UpdateTally = Nz(Me.UpdateTally, 0) + 1
     End If
    End Sub

    Linq ;0)>
    Last edited by Missinglinq; 12-09-14 at 13:46.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    WOW Fantastic! Thanks so much!

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad healdem and I could help!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I was ~~~~~~~~~~^~~^~^~~~~\0/~~~~~~~~~~~~ until you all came along.

Posting Permissions

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