How do I rollback?

[RoadR]

Using Access 2000, I have two tables, one a header and the other the line items belonging to the header. If the user is typing information into a form related to the header, and also into a subform related to the line items, how do I roll back any inserts made automatically into the tables if the user decides they don't want that data to be saved?

If the forms are based on the tables, then as the user is typing in the data it's being saved automatically?

Help

RR

 

[mp9]

The data isn't saved automatically until the user either closes the form, moves to another record or explicitly saves (e.g. clicks a save button).

There are a couple of options - train your users that pressing the Escape keys undoes all changes made to the record since the last change. Sadly this solution, although elegant, is dependant on your users! So you might alternatively want to add a command button to your form and add

Me.Undo

to its OnClick event.

http://www22.brinkster.com/accessory/

 

[cmmrfrds]

The data cannot go automatically into the tables, if you want to have a logical transaction across the main Form record and the sub Form records. Some programmers use unbound forms and do the updates manually but there is some tedious coding necessary.

Another way to do it, although I have not had a need yet, is to use ADO disconnected recordsets. The Forms are bound to disconnected recordset(s) and the updates would be done in batch through a command button on the Form. You would need to structure both queries for the Form's as recordsets and use the same connection object on both. Then, close the recordset, BUT NOT the connection. This makes disconnected recordsets that can be reconnected when you are ready to update.

To do this set up both forms initially bound to the respective queries and then delete the queries from the data source that way the fields are on the Form and bound when you set the Form's recordset to the recordset that you create in code and that will need to be set back to the query recordset before the update.

I have used disconnected recordsets but not for this type of implementation. It should work if you want to give it a try. The advantage is that once you have it working the model will be reuseable for future needs.