×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

undo/rollback

undo/rollback

undo/rollback

(OP)
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.  

RE: undo/rollback

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.

RE: undo/rollback

(OP)
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??

RE: undo/rollback

(OP)
Also note that I'm using Access XP

RE: undo/rollback

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:

CODE

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

RE: undo/rollback

(OP)
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?

RE: undo/rollback

(OP)
Also -the rollback is not working

RE: undo/rollback

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

RE: undo/rollback

(OP)
or will the me.undo not work for subforms?

RE: undo/rollback

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

RE: undo/rollback

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

RE: undo/rollback

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

RE: undo/rollback

Actually you might want to update the undo function:

CODE

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

RE: undo/rollback

(OP)
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?

RE: undo/rollback

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:

CODE

public function GetConnection()
 set getConnection = mcnn
end function
Thus, in the subforms

CODE

public Function LoadData()
  Set mrs = New ADODB.Recordset
  mrs.CursorLocation = adUseClient
  mrs.Open "SELECT * FROM tblMain", me.parent.GetConnection, 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

CODE

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

RE: undo/rollback

(OP)
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?    
 

RE: undo/rollback

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

RE: undo/rollback

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 www.tek-tips.com for the answer.

RE: undo/rollback

(OP)
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!

RE: undo/rollback

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

RE: undo/rollback

(OP)
I figured out how to shorten the loading time of the form from other posts - (tools,options,general and disable autocorrect)

I have another problem when I'm trying to create a new record on my main form.
In my main form:
mcnn = nothing
set mcnn = currentproject.connection
DoCmd.GoToRecord , , acNewRec
Call Me.Custs.Form.LoadData
Call Me.CustSupp.Form.LoadData
mcnn.BeginTrans

I get an error when calling the subform's loaddata function.  These functions work fine when a main form record exists even if no related records exist.
My error is "Method 'Recordset' of object '_form_custs' failed"

RE: undo/rollback

(OP)
I figured out why I was getting error= "method recordset of object _form_custs failed"  I first needed to do an acsaverec on my main form.
I'm still stuck on the problem of adding a new record to my unbound subform...

RE: undo/rollback

Quote (aw23):


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.  
Have you tried using the before update event of the subform to copy the value of the primary key of the main form to the foreign key of the subform?

e.g. in subforms before update event:

CODE

if me.newrecord then
  me.field_name = me.parent.field_name
end if

you could bind the main form - then you only have to deal with transaction processing on the subform instead of both.

RE: undo/rollback

(OP)
Thank You! I never user the beforeupdate event before.  It works great!

Here's the new problem.
I have a listbox on my main form that queries my customer table for all relating records.  This same form has a subform called customers which allows the user to update and add new records to the customers table.  When a user adds a new record to the subform (or edits) I want the listbox to reflect the changes. I tried myLB.requery but it didn't show the changes.  I think it's because the changes are being made in the transaction block and hasn't been "commited" yet.  How do I show the changes without commiting?

RE: undo/rollback

why would you want the listbox to update - because any new and non-committed customer records will not be present in the underlying table. Thus you could potentially be displaying 'ghost' customers.

you could try using a call back function. type "listbox user defined function" into MS Help for more information or try http://www.mvps.org/access/forms/frm0049.htm .

cheers,
dan

RE: undo/rollback

(OP)
Thanks but I solved it- I had to use the same connection string when requerying the listbox.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close