Rollback changes in subform

[JenSGT]

I am building a Amend Screen such that Client can change/edit data when they receive new information after the Order record is created.

I am using a Edit button to allow changes to the record. If user plans to save the changes, they must use the save button to update the changes.
I am using a subform (recordsource = query) to display the details of the particular Order. User can use the subform to edit the data. If they plan to save, they can use the Save button.

My problem is when user do not want to save, and they change the combo box to view another Order record, the dirty subform would already save the data. How can I prevent the original data from been change when user do not click on Save button.

Also, I have a 'close form' button which also exhibits similar behavior. If user edits the subform, do not save the data using the Save button, and click on the 'close form' button, the subform data will also be saved. How can I prevent this from happening?

I read of a possible solution that is to create a temp table and store the original data to it. Whenever the subform is dirty, copy the original data back. However, this soln does not work if referential integrity is enforce.
My design is using referential integrity for the relationships. What can I do to overcome this?

TIA......

 

[missinglinq]

The concept of a "Save" button is really not a part of an Access database because, by default, Access saves records anytime you leave a record, whether moving to another record, closing the form, or in the case of a subform, merely moving off it back to the main form. They user can simply close the form with the big X and the record will be saved.

The way saving/not saving a new record or changes to an existing record is usually handled in Access is to ask the user, just before the record/changes are saved, whether they want to save them, with code like this, in the form used as the subform:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not (Me.NewRecord) Then
 If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
  Me.Undo
 End If
Else
 If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This Record ???") = vbNo Then
  Me.Undo
 End If
End If
End Sub

The point is, that by placing code in the Form_BeforeUpdate event, you will always be asking the user to save/not save before the record is ever saved. You can keep your "Close" button and Access will still go to the Form_BeforeUpdate event before closing, but the "Save" button is really unnecessary.

You could go the unbound form/temp table route but it's really a lot of work that's not needed

 

[Banana]

In case of a subform, there is no simple way to edit both the parent record and all associated child records as a single unit of transactions.

For most simple & straightforward implementation of the functionality, I would probably build a temporary table replicating both parent & child tables and write code to load the tables with data for the "current" record and you can use your button to write the data in temporary back to the permanent table.

I don't think referential integrity will interfere with using temp tables if we handle it correctly. One way to do this would be to make the key column in child's temp table a nullable column non key so it's always left up to the permanent table to provide the new key at the insertion time, and in case of creating a new parent record, have a function to retrieve the new ID as assigned by the permanent table and fill in all child records' foreign key column before saving them to the other permanent table.

Another possible implementation is to use recordsets wrapped in the transaction, though my experience has been that they're quite hard to work and you have to handle for several issues.

Of course, unbound form is one way to get more control, but TBQH, I'd say temp table solution is much more easier than doing everything in the unbound forms.