473,324 Members | 2,254 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Undo changes to a record with subtables

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
2 2531
Stewart Ross
2,545 Expert Mod 2GB
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
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

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

Similar topics

18
by: Darryl Kerkeslager | last post by:
I don't do much with subforms - in fact I've deliberately avoided them - but .... I have a tab control that will contain several subforms, each bound to a separate table, and each table/subform...
2
by: Leslie | last post by:
On a form, I create a record and save, and am ready to enter another. I decide to Quit the form which executes an "Undo" in case there is data entered. This then tries to "delete" my just saved...
6
by: Raji16 | last post by:
Hi, I am a new member. i am designing a simple judicial database system. however after creating tables i am a bit confused on setting the relationships between tables :confused: here is the link...
3
by: GoogleEyeJoe | last post by:
Dear ladies and gents, I'm trying to determine whether the .NET Framework implements a means of transactional processing without the need for a database. Essentially, I'd like to enlist...
2
by: Bernard Dhooghe | last post by:
The (limited to propagatable since version 9) log records are described in the API reference manual. What is meant by: normal/undo/redo/compensation records, what are the possible subtypes for...
7
by: call_me_anything | last post by:
Hi, I am looking for a good algorithm (in C/C++) for implementing undo/ redo on a data structure. The data structure is basically a n-children tree somewhat of the form : class /* or...
5
by: jmarcrum | last post by:
Hey everybody! I've created 2 buttons that I've placed on all my forms: a Save button & a Cancel button. The cancel button I have a question about. I used the wizard to create the button...
2
by: maxx429 | last post by:
Hi all, I am a VBA newb. I know probably just enough to be dangerous. :) Anyway, I have a Form that I am using <a href="http://allenbrowne.com/AppAuditCode.html">Allen Brown's Audit Trail</a> ...
0
by: wizard of oz | last post by:
Hi all, I'm extending an Abstract Styled Document associated with a JTextPanel to implement a syntax highlighting editor. This is all working just fine - except for undo / redo. The problem...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.