Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2001
    Location
    Ohio
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tracking changes

    Will access track all changes in a table? We are going to run our backlog in Access 2000. Now we have 15 fields for each record. What we want is any time any of the fields is changed individually or as a group or the whole record is deleted, we would like a copy of the entire record before and after to be appended to another table with the current date added.

    Currently we are running our backlog in Excel using macros, but it has gradually gotten complicated. I am the only person to understand the Excel macros and I think Access is a much better platform for what I am doing. I have it pretty well done except for this area.

  2. #2
    Lounger
    Join Date
    Feb 2001
    Location
    TX, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking changes

    What you will need to look into is: .OldValue

    HTH

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tracking changes

    No, Access will not track changes in and of itself, and it won't do it at all from tables. If you want to do this, you will have to use a form with controls bound to the underlying table's fields.

    In the BeforeUpdate event of the form, you would have a routine to loop through the controls and assign the OldValue property of each control to a separate module level variable (you would use variables rather than writing directly to the log table because they might cancel the update).

    Then in the AfterUpdate event of the form, you would compare the variable values with the control values to make sure there was actually a change. If there was, you would either open a recordset and use AddNew to add a new record, populating it with the values in the variables and then adding another and populating it with the new values, or you could create the SQL for an append query for each set of values and execute it for each to add the records that way.
    Charlotte

  4. #4
    New Lounger
    Join Date
    Feb 2001
    Location
    Ohio
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking changes

    Thanks
    I really appreciate it. You will probley hear from me later as I try to write it. It sounds just what I need.

    John

Posting Permissions

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