Cannot Update Subdatasheets

[ccutayar]

I have two tables that contain the same fields except for an additional primary key field within the second table. Table 1 is my main table and Table 2 is to be a history table for each record contained within Table 1.

If I change any data within a record in Table 1, I want it to show up within Table 2. So if in a day I make 4 changes to record 1, then I should view 4 changes for that day and for that record in Table 2.

I am working with Forms and Subforms to achieve this, but I cannot get them working. I have gone so far as to see the history of a record in Table 1, but any changes I make, just get updated, Table 2 does not create a new record showing the changes.

I have also tried append and update query, but no luck.

I really hope you can help me.

Many Thanks

Cathy

 

[dcx693]

Please do not post the same question to different sections of the forum: Updating SubDatasheets.

 

[theprez]

I'll take a shot at this, It sounds like Table 2 is really a transaction log of changes to Table 1. This could be difficult because if the primary key is allowed to change, you may not be able to link the two tables.

You may want to try recording what changed for the record and not keeping a duplicate in Table 2. You may want to also add a updateduser (if you have a user login process) and a updatetime fields to table 2.

Your instincts are right about the append query.

On the form that has table 1, you will have to use the AfterUpdate event to append the data to table 2. Again, instead of keeping a before and after picture in table 2, you may want to record the field that changed and its new value. This is just some thoughts, I'm sure other will jump in on this one.