Undo changes to a record with subtables

[yedidel]

Hello,

I have a form that edits an Order. Each order has several OrderItems which are edited by a continous-form subform inside the main form. Each OrderItem also has several OrderItemsAddit ions which are edited from a pop-up form.
3 seperate tables:Orders,O rderItems,Order ItemsAdditions.

The big problem is that i need a way to undo all changes to an order. When clicking on the "Edit" button (data is locked before that), i save all OrderItems into a temporary table, same with all relevant OrderItemsAddit ions .
When pressing cancel, I delete all relevant OrderItems, and then insert all OrderItems from the temporary table.

Now when i do the same for OrderItemsAddit ions, because the OrderItems ID has changed, the link between the additions and the orderitems goes away.

Because the orderitems are edited on a subform, and in a continuos sub-form, i found no way of undoing changes through access (since the records gets saved instantly).
ID's in all tables are autonumber.

Any ideas?

[Stewart Ross]

Hello,

I have a form that edits an Order. Each order has several OrderItems which are edited by a continous-form subform inside the main form. Each OrderItem also has several OrderItemsAddit ions which are edited from a pop-up form.
3 seperate tables:Orders,O rderItems,Order ItemsAdditions.

The big problem is that i need a way to undo all changes to an order. When clicking on the "Edit" button (data is locked before that), i save all OrderItems into a temporary table, same with all relevant OrderItemsAddit ions .
When pressing cancel, I delete all relevant OrderItems, and then insert all OrderItems from the temporary table.

Now when i do the same for OrderItemsAddit ions, because the OrderItems ID has changed, the link between the additions and the orderitems goes away.

Because the orderitems are edited on a subform, and in a continuos sub-form, i found no way of undoing changes through access (since the records gets saved instantly).
ID's in all tables are autonumber.

Any ideas?

Hi. Sorry to say but Access does not have the equivalent of the Commit and Rollback processes which Oracle, SQLServer and other transaction-processing databases use. With these systems, you have to commit your changes explicitly before they take effect. As you say, Access changes the data instantly.

It would be possible to use the on-current event of the forms to save a copy of the data for recovery later. This would slow things down, though, and would require a bit of coding to achieve.

Sorry that I can't think of an immediate solution that would help you - other than changing your tables to be stored on a transaction-based back-end system such as SQL Server if it is a real problem for you.

Regards

Stewart

[yedidel]

Found a solution at last.

I save the ID of the OrderItems in the temporary table. When cancelling I delete all OrderItems, insert the old ones from the temporary table. Then i run a dlookup(max([ID]) on orderitems table to see the new ID of the last order item, do the same on the ID's in the temporary table. Now I subtract the old one from the new one, and then when adding the additions i add the result to the OrderItem reference so it stays bounded to it's new ID.