Transactions or Delete queries

[NauticalGent]

I am looking for ideas and best practice methods. I feel this is a fairly common issue but I have never really given it much thought. I have a Data Entry/Bound form with a subform. The users want to be able to move freely from on to the other and back, but this renders the Undo command practically useless. I do NOT want to convert the form from Bound to Unbound.

I could simply capture the PK for both tables and run delete queries if the user clicks the Cancel Button, but I am wondering if Transactions would be a better way to go?

 

[CJ_London]

I did a presentation recently about ado recordsets which included an example of transactions using disconnected recordsets

Access Europe User Group - Wed 4 Mar: Using ADO Recordsets in Access (Chris Arnold)

The third Access Europe User Group meeting for 2026 will be on Wednesday 4 Mar starting at 18:00 UK time (6PM UTC) and finishing by 19:30 (7.30PM). Please note that the UK is currently on winter time. For local times, please check World Time Buddy In this session, we are very pleased to welcome...

www.access-programmers.co.uk

 

[NauticalGent]

Thanks Chris, I was trying to make time to watch that, now I have a little motivation. So, from your response I am guessing you are leaning towards Transactions for this function?

 

[RonPaii]

I am looking for ideas and best practice methods. I feel this is a fairly common issue but I have never really given it much thought. I have a Data Entry/Bound form with a subform. The users want to be able to move freely from on to the other and back, but this renders the Undo command practically useless. I do NOT want to convert the form from Bound to Unbound.

I could simply capture the PK for both tables and run delete queries if the user clicks the Cancel Button, but I am wondering if Transactions would be a better way to go?

I don't think you want to use transactions during user input because you don't want to keep them active longer then the time to do the update. Temp tables may be a solution, another would be 2 forms using code to link them. Two forms because edits on the 1st form are not automatically saved when you click on another form.

 

[Ken Sheridan]

I could simply capture the PK for both tables and run delete queries if the user clicks the Cancel Button, but I am wondering if Transactions would be a better way to go?

Is the relationship between the parent form's table and the subform's table one-to-many, as is generally the case? If so you can simply enforce cascade deletes in the relationship, so that deleting the parent form's current record will automatically delete the referencing records in the subform. The parent form's Undo method could still be called to either to undo the parent form if a new record is being added, at which stage no records will yet exist in the referencing table, or to undo edits if at an existing record. Individual records in the subform can be deleted or undone in the usual way of course.

 

[Imb]

I have a Data Entry/Bound form with a subform. The users want to be able to move freely from on to the other and back, but this renders the Undo command practically useless.

What is the function of the subform? What kind of information?

 

[CJ_London]

I am guessing you are leaning towards Transactions for this function?

Yes - in the example it uses transactions and creates dummy pk & fk values to maintain the main form/ sub form relationship - typical example would be invoice or order header/rows

Temp tables is another option but you would still need to manage the temp tables. A benefit of ado is it is all in memory

 

[CJ_London]

because you don't want to keep them active longer then the time to do the update

You start the transaction when the user clicks the save button