By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,472 Members | 1,168 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,472 IT Pros & Developers. It's quick & easy.

Undo changes to a record with subtables

P: 2
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 OrderItemsAdditions which are edited from a pop-up form.
3 seperate tables:Orders,OrderItems,OrderItemsAdditions.

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 OrderItemsAdditions .
When pressing cancel, I delete all relevant OrderItems, and then insert all OrderItems from the temporary table.

Now when i do the same for OrderItemsAdditions, 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?
Feb 14 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
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 OrderItemsAdditions which are edited from a pop-up form.
3 seperate tables:Orders,OrderItems,OrderItemsAdditions.

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 OrderItemsAdditions .
When pressing cancel, I delete all relevant OrderItems, and then insert all OrderItems from the temporary table.

Now when i do the same for OrderItemsAdditions, 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
Feb 15 '08 #2

P: 2
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.
Feb 18 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.