tracking data edits across different tables

[anewuser]

Hi everyone,

Firstly I am using access 2007. I currently have 2 tables in my database, 1 to store up to date data and the other to store out of date data (where data edits have been made, for audit purposes). I have set up a one-to-many relation ship between the 2 tables based on the record number.

I use a form to search for records which opens up a split form to show all of the matching records. I do have a code which selects and copies the old data before it is replaced, it is then replaced and the code then pastes the old record into the second table. (the macro is posted below N.B. it was written in access 2007's macro builder window).

My problem is that the record that is copied and pasted is always the first record in the database not necessarily the record being edited. Can anyone help (I feel that the solution is quite simple and I have tried various different solutions but I can't quite get the right solution (the macro posted is my best attempt so far).

On Error          Next,
OpenTable         CD Signatures, Datasheet, Edit
RunCommand        SelectRecord
RunCommand        Copy
RunCommand        Replace
RunCommand        Refresh
OpenTable         Edited Signatures, Datasheet, Edit
GoToRecord        Table, Edited Signatures, New
RunCommand        SelectRecord
RunCommand        Paste
Close             Table, CD Signatures, Yes
Close             Table, Edited Signatures, Yes
RunCommand        Refresh

[TheSmileyCoder]

I don't really work with the macros of Access. I don't feel they are flexible enough to work to my needs.

My approach is as so:
In the beforeUpdate event I copy the record using SQL, not Copy/Paste. This requires the record has a unique key.

If you are interested I can find the code for you, but it is not something you can simply copy and paste, you will need to make the required code modifications yourself. If you are not comfortable doing so, I won't confuse you with codes at this point.

Best of luck with your project
TheSmileyCoder

[anewuser]

That sounds like an intriguing idea, I am happy to give any suggestion a go. I have got a backup available if I mess things up. Your help would be really appreciated.

[NeoPa]

ANewUser:
I have got a backup available if I mess things up.

That's very sensible :-)

I would echo what Smiley's said already. I would ask though, for you to post the data layout of your tables. The general data is not too important, but the indexing for each is very important to know. Bear in mind that while the primary table must have unique records based on a specific PK, the archive table needs the primary PK (IE. of the primary table) to allow duplicates as a record may be archived more than once. I would expect the PK of the archive to be something like a compound index including the PK of the primary table along with a Date/Time field that stamps when the particular archive record was added.

[anewuser]

You are right NeoPa, the archive table does not have a primary key as it does allow for multiple data edits and does have a date/time stamp setup to record when data is added to it. The primary table does use a primary key. I believe what you are asking for is a list of the fields of each table (I am sorry if I am wrong).

Primary table:
Record Number (PK) AutoNumber
Designation Text
First Name Text
Surname Text
Ward Text
Signature OLE Object
Renewal Date Date/Time
Authorised By Text
Date of Last Amendment Date/Time
Added By Text
Changed By Text
Deleted By Text
Restored By Text

Please note that the fields in the archive table are exactly the same (excluding the record number field which is just a number field and does not have a primary key).

[NeoPa]

That's nearly what I was asking for, but it doesn't include the important parts from the Archive table. Please list how the Record Number and Time Stamp fields are named. When I have that info I will certainly be suggesting, as a first step, that the two fields, together, be defined as a (composite) PK for this table.

Presumably, the task you are trying to achieve is to make a timestamped copy of the currently selected record from the Primary table into the Archive table. What is not clear is how this is (should be) triggered. Is it automatic when an update is applied? Does it happen after a button is clicked?

[anewuser]

The date/time stamp is applied at the time the record is created in the primary table by setting a default value of Now() in the "Date of last amendment" field. When the data is archived this value remains the same for the archived record and a new date/time stamp is applied to the updated record. There is a button which is clicked to apply the change.

However, the problem that I am having is that my macro is only selecting the first record in the primary table and moving it to the archive table regardless of the data being editted.

The record number field is just called record number, it is a number field so that records can be archived more than once.

I hope this helps.

[NeoPa]

ANewUser:
I hope this helps.

A little, but not a lot.

You need to read my last post again with more attention. You haven't dealt with two important issues raised. It seems sort of pointless for me to raise issues if you don't deal with them. I certainly can't help further, at this stage, as anything further depends on those two points, as well as being a little pointless for me to proceed further if you skip over much of what I say.

1. Handle the design of the Archive such that it has a PK as indicated (and post to say as much).
2. Answer the question about triggering of the process.

When we're back up-to-date we can proceed sensibly.

[anewuser]

Hi I am sorry it has been a while since my last reply. I have now created a composite PK on my archive table. It took me a little while to find out how to do this which the reason for the delayed reponse.

The event is triggered by clicking on a button called "Update Record" on a split form which shows a list of matching records found by a search form. Edits to the data are made on the form and then the user clicks the button to apply the change and archive the old record.

[NeoPa]

Excellent. I may need some help understanding all you've said, but this is certainly a response I can proceed from and with.

ANewUser:
Hi I am sorry it has been a while since my last reply. I have now created a composite PK on my archive table. It took me a little while to find out how to do this which the reason for the delayed reponse.

No worries. I wasn't getting bored ;-) And well done. If that wasn't something you knew then you've learned something to take you forward.

ANewUser:
The event is triggered by clicking on a button called "Update Record" on a split form which shows a list of matching records found by a search form. Edits to the data are made on the form and then the user clicks the button to apply the change and archive the old record.

Well done for explaining all this. Unfortunately, although you refer to applying the change, it seems there is the potential for many changes. It also seems, from your wording, that these changes may pertain to multiple records at the same time. If so, this would tend to complicate matters a lot. I don't say it would make it impossible, but it would certainly make it extra difficult for one of your experience to deal with, even with help. Can you clarify the position here do you think.

[anewuser]

To clarify the data edits should only be made to to the record currently being viewed in the main body of the form. The reason for the split form is so that the user can select the appropriate record.

I do, however, realise the weakness here the macro (I believe) will only work for the record that is currently being viewed. If the data is editted in the part of the form which shows all of the matching records then the old record(s) will not be archived. This is something I am looking into but I have not yet found a solution).

[NeoPa]

ANewUser:
To clarify, the data edits should only be made to to the record currently being viewed in the main body of the form. The reason for the split form is so that the user can select the appropriate record.

This comment makes sense. What I find worrying is the other paragraph which talks about edits being made to multiple records in that section of the form which you have just identified as being there only to select the record to work on. In my mind this would make more sense if this section of the form were set as Read Only. Alternatively, a more standard approach would be to use Cascaded Form Filtering with ComboBoxes that contain data from all the matching records and allow you to select any one of them. I really believe this would suit your requirements a lot better and be very easy for the operators to get familiar with.

If you can confirm how you plan to proceed on this point, I think we'll be ready to start looking at the code to handle the logging of your changes.

[anewuser]

Hi NeoPa, I have decided to go with the read only route on my existing form and I have formatted it as such.

Thanks for your help so far it is much appreciated

[NeoPa]

Sorry for the long absence. I haven't forgotten you, but I'm afraid I only have time, at the moment, for more basic, in and out quickly, questions for now. I will look at this again in ernest when I can. It's still stiing in my browser until I can do so.

OK. Let's get the ball rolling anyway.

I don't see where you have ended up with an archive table which has a composite PK. Have you got that sorted out yet?