undo/rollback 1

[aw23]

I have a form where the user can update live data. The form contains subforms too. There is a save button and cancel button. The save button will actually o nothing since the user is working on live data. I want the cancel button to undo whatever changes the user made. How do I do this? Remember it has to undo the changes on the whole form which includes related records on the subforms.

 

[lupins46]

This is an extremely difficult thing to do.

On approach would be to copy data into temporary tables for your form/subform and make changes to those. You then need a 'commit' routine which updates the real tables with the contents of the temporary tables, and your 'undo' button would simply discard all temporary records.

But even this is just looking at the Undo in isolation and when you combine it with all the other stuff you typically do in forms it gets very complex.

 

[aw23]

I understand that I'm able to do this through creating a transaction and then only committing it when the user hits save and doing a rollback when the user hits cancel. I've always worked with unbound fields. Can someone please be specific and tell me how to create a transaction, commit it and rollback? Also how do I deal with the subforms?? Doesn't the transaction end when the user goes into a subform??

 

[aw23]

Also note that I'm using Access XP

 

[DanJR]

With XP you can bind an ado recordset to a form as read/write.

Here's a simple example with a form that has an undo and save button. This will at least get you going:

Option Compare Database

Private mrs As ADODB.Recordset
Private mcnn As ADODB.Connection

Private Sub cmdSave_Click()
  'Set mrs = Me.Recordset
  DoCmd.RunCommand acCmdSaveRecord
  mcnn.CommitTrans
  
  Call LoadData
End Sub

Private Sub cmdUndo_Enter()
  mcnn.RollbackTrans
  Call LoadData
End Sub

Private Sub Form_Load()
  Call LoadData
End Sub

Private Function LoadData()
  Set mcnn = CurrentProject.Connection
  Set mrs = New ADODB.Recordset
 
  mrs.CursorLocation = adUseClient
  mrs.Open "SELECT * FROM tblMain", mcnn, adOpenKeyset, adLockOptimistic
  
  Set Me.Recordset = mrs
  mcnn.BeginTrans
End Function

 

[aw23]

Why do I have to use adlockoptimistic?When using this code I sometimes get a write conflict warning when trying to "undo". How do I deal with subforms?

 

[aw23]

Also -the rollback is not working

 

[aw23]

I was searching the posts and saw the me.undo command. Can't i just use me.undo when the user hits cancel?

 

[aw23]

or will the me.undo not work for subforms?

 

[aw23]

ok - my rollback is working.
I get the write conflict sometimes after hitting the save or undo

 

[lupins46]

me.undo will only undo data that has not been saved.

 

[DanJR]

Sorry, I should have provided more information.

When you create the form, make sure you do not bind the form to a table or query, ie in the properties window the 'Record Source' property is blank - this may be causing for write conflicts. However, each field should have the 'control source' property set.

lupins46 has given you the reason for not using me.undo. It undo's only what is in the forms buffer. Note that I used DoCmd.RunCommand acCmdSaveRecord in the save button method - because we need to get any content that is stored in the forms buffer and update the underlying recordset. Then, using commit will save the content of the recordset to the table.

The purpose of the undo button is to rollback any changes and start a new transaction. The new tracsaction is handled by the LoadData() function.

One strategy would be to have the main form bound and use the me.undo to undo its changes and use this code/approach for the subform. Another approach would be to have both forms unbound....

I hope this helps.
Cheers,
Dan

 

[DanJR]

Actually you might want to update the undo function:

Private Sub cmdUndo_Enter()
  DoCmd.RunCommand acCmdSaveRecord
  mcnn.RollbackTrans
  Call LoadData
End Sub

 

[aw23]

How do I deal with the subforms on my main form? If the user hits cancel on the main form I want the subfrom to rollback also. How?

 

[DanJR]

It depends on what approach you take. Let say you are using this transaction approach for both the main form and all the subforms. Lets say you have two subforms named chdForm1 an chdForm2. You will need to add the above code to each of the (sub)forms (..or something similar - see below).

I would have the subforms use the parents ado connection, so make the connection object accessible to the subforms e.g.

In Main form:

public function GetConnection()
 set getConnection = mcnn
end function

Thus, in the subforms

public Function LoadData()
  Set mrs = New ADODB.Recordset
  mrs.CursorLocation = adUseClient
  mrs.Open "SELECT * FROM tblMain", [COLOR=blue]me.parent.GetConnection[/color], adOpenKeyset, adLockOptimistic 
  Set Me.Recordset = mrs
End Function

I would also use the main form to handle all the commit and rollback, and loading the data e.g.

In parent form

private function LoadData()
  ...code to load parent data
  call chdForm1.LoadData
  call chdForm2.LoadData
  mcnn.begintrans
end function

Private Sub cmdUndo_Click()
  DoCmd.RunCommand acCmdSaveRecord
  call chdForm1.UndoForm 'custom function in child form
  call chdForm2.UndoForm 'custom function in child form
  mcnn.RollbackTrans
  Call LoadData
End Sub

Something like that....

Does this help?
Cheers,
Dan

 

[aw23]

I put the function loaddata in my subform but when I tried calling it from my parent form i get "method or data member not found". I tried as a function and a sub and neither worked. My calling line is
call me.customers1.loaddata
customers1 is my subform.

The function in customers1 is public.

Also do I need to make a seperate transaction in my subform or will it be part of the main form's transaction?

 

[DanJR]

call me.customers1.loaddata won't work

you need me.<name of subform control>.Form.LoadData

ie if your subform control is called chdSubForm then:
me.chdSubForm.Form.LoadData

Yes, I would try using the parents connection object to handle the transactions for the subforms (see the blue bits of code above). Are you going to bind the main form or not?

Cheers,
Dan

 

[GummowN]

Here is my 2 cents worth on this - I will make assumptions based on my experience so bare with me.

Force the user to select a main record from a list. When the form loads, copy the record into a temp table - including temp tables for all subform data. The forms and subform will now reflect the live data but actually be looking at the temp tables. Let them make changes and when they hit save - it updates the live data, when they hit undo you reload from the live data into the temp tables and refresh the form.

I have found this works in the past. To really impress users - you could time stamp the change in the main table with a lastchangedate. Then if 2 people have made changes you can compare records and get them to accept/reject those conflict changes.

Let me know if you want more information on this.

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[aw23]

This is my code in my subform

Option Compare Database
Private mrs As ADODB.Recordset

Public Function LoadData()
Set mrs = Nothing
Set mrs = New ADODB.Recordset
mrs.CursorLocation = adUseClient
mrs.Open "SELECT * FROM customers where customers.company_id = " & Parent!Company_ID, Me.Parent.getConnection, adOpenKeyset, adLockOptimistic
Set Me.Recordset = mrs
End Function

Private Sub Form_Load()
Call LoadData
End Sub

In my main form the subform's loaddata is called each time a new main form record is selected.
The transaction process of editing a record on the subform works great. The problem is - how do i add a record on my subform if it's not bound? when i add a new record now to my subform the data is entered but it is not linked to my main form.
to be more specific: my subform is customers and my main form is comapany. customers has company_id which holds the id of company that it is linked to. when i add a record in customers the company_id is left blank.
Is my subform sopposed to be bound unlike the main form?
Thank You!

 

[aw23]

Also why does it take so long to load an unbound form?