Commits and Rollbacks

[Guest]

I am trying to set up a form and a subform where I can make changes to both
forms and allow the user to rollback all changes at the bottom of the main
form. I used an example in a manual where you remove the
linkmasterfields/linkchildfield properties and you manage the linking
yourself. The sub-form is based on a query where the parameter is based on a
field from the main form. There is a command button on the main form and I
set the enabled property to allow me to undo all the changes to the subform.
This part is working perfectly. I can make changes to all the records on the
subform and when I say cancel on this command button, they are rolled back
properly. The issue is that I want to roll back both the main form and sub
form all with one click. What is happening is when I move from the main form
to the sub-form, the main form is updated. I can undo the changes on the
main form if I don't move to the sub form. What do I do to make this one
command button roll back both changes?

 

[Arvin Meyer [MVP]]

The default behavior of Access is to use bound forms, i.e. forms which are
bound to the underlying query or table. The data is committed as soon as you
move from one form to the other. To do what you want, you need to set the
recordsource of each form to be blank (unbound) and use code to write a
transaction based recordset to the tables. You still should be using the
linkmasterfields/linkchildfield properties, or you will need to figure out a
way to add the foreign key to the subform records. The method I'd use if I
were not using linkmasterfields/linkchildfield properties is to set the
default value of the foreign key field to be equal to the primary key field
in the main form.

 

[Guest]

-- Do I have two transactions - one for the main form and one for the
subform? I tried creating that but got an error that it could only open one
recordset. Is there somewhere I can look for how to do this?

Peggy

 

[Arvin Meyer [MVP]]

I've used as many as 4 recordsets so your problem is problably trying to use
the same recordset multiple times. While you can do that like:

CurrentDB.Execute "sql statement"

you cannot use recordsets that way within a transaction. Instead Dim
multiple recordsets:

Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset

and handle them separately with the transaction. Personally, I'd use
db.Execute because it's easier and faster to write.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

I know I'm a beginner at access so I'm very confused with transactions and
what you are telling me to do. Below is the code that I used to handle the
transactions of the subform. Can you put it in perspective for me? I'm
doing this reset which sets the transaction of the subform in the main form.
So how do I set up another transaction for the main form in the same place?

Option Compare Database
Option Explicit

' From Access 2000 Developer's Handbook, Volume I
' by Getz, Litwin, and Gilbert (Sybex)
' Copyright 1999. All rights reserved.

Private mwks As DAO.Workspace
Private mfInTrans As Boolean

Private Const adhcSource _
As String = "qrySubformTransactions"

Private Sub Form_Unload(Cancel As Integer)
'Assume user wants to commit changes in subform,
' if they haven't canceled them. You could, of course,
' ask if the user wants to save or discard changes
' at this point, if mfInTrans is True.
If mfInTrans Then
mwks.CommitTrans
End If
Set mwks = Nothing
End Sub

Private Sub ResetData()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim prm As DAO.Parameter
Dim strSource As String
Dim FirstName As String

' Note the intentional lack of error handling.
' This one's just an example.

' Assume user wants to commit changes in subform,
' if they haven't canceled them.
If mfInTrans Then
mwks.CommitTrans
End If

' Create and assign filtered recordset for subform.
Set mwks = DBEngine.CreateWorkspace("mwks", "Admin", "")
Set db = mwks.OpenDatabase(CurrentDb.Name)
Set qdf = db.QueryDefs(adhcSource)

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Me.Painting = False
Set rst = qdf.OpenRecordset
rst.LockEdits = False
Set subOrders.Form.Recordset = rst
Me.Painting = True

'Start a new subform transaction
mwks.BeginTrans
mfInTrans = True
cmdRollbackSubform.Enabled = False

End Sub

Private Sub Form_Dirty(Cancel As Integer)
cmdRollbackSubform.Enabled = True
End Sub

Private Sub cmdRollbackSubform_Click()
!Me.Undo
If mfInTrans Then
mwks.Rollback
mfInTrans = False

'Shift focus, so button can be disabled
txtFirstName.SetFocus
Call ResetData
End If
Me.Dirty = True

End Sub

Private Sub Form_Current()
Call ResetData
End Sub

 

[Arvin Meyer [MVP]]

What you are showing in the code below is not a series of recordsets, but
only 1, based upon:

qrySubformTransactions

Are you even sure you need a transaction? Perhaps it might be a good idea to
explain what you are trying to accomplish. Try and be as explicit as
possible.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

This is a actually a sample that I playing with. What I have is a main form
with a subform. The main form has the customer data and the po number and
the subform has the invoice data connected to that customer. What I want to
do is allow the user to make changes to both forms and be able to save with a
command button or at least rollback all changes to both forms at once. I
found this example in a access developers 2000 handbook by Ken Getz, Paul
Litwin and Mike Gilbert which was exactly what I wanted to do. It said that
to rollback back changes on the subform on the main form, to set up this
transaction which I copied below. This works great as far as rolling back
the subform transactions. What I found in the book example as well as my
form was that as soon as I put focus on the subform, the main form was
updated and once I was finished with the subform, I was not allowed to
rollback the changes made to the main form. My rollback button works only on
the subform because the Me.Undo on the main form is lost once focus is
changed to another form because the update is already done. I really want a
button to either confirm the changes on both forms at once or a button to
rollback the changes on both forms at once and I can't seem to find the
answer to this.

 

[Arvin Meyer [MVP]]

I am unfamiliar with the code example, but I do have the Handbook (version
2002) if you can tell me where it is.

Bound forms always save changes once you are done and move to a different
form (the subform) or a different record. You need to use unbound forms or
write the data to temporary tables, then write it back. Of the 2 methods,
the unbound form works the best.

With an unbound form/subform you will need to call up 1 record at a time for
editing, then save the record back to the table. It is much more involved
than using bound forms.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

In my manual, it is in Volume 1, Desktop Edition, Chapter 8 Topics in Form
Design and Usage. and the section is called Subforms and Transactions. Can
you recommend a book that I can get to explain the unbound forms?

 

[Arvin Meyer [MVP]]

OK, I read the chapter section and looked at the database form and it only
handles rolling back the subform, not the data in the main form. If you want
to control both the form and subform, I suggest that you write the data to
temporary tables, because it's easier.

Just use the temporary tables like any other table. Bind the form and
subform to them. When you are ready to commit the records, just use an
append query to write them to their respective permanent tables. Then either
delete the data in the temp table, or start the next operation with a delete
query.

If you want to also edit existing records before "committing" them, you need
to write the data back to the temp table, edit it, then use an Update query
to write the changes back.

All this is a lot of work to over-ride the default behavior of Access.
There are very few instances where there is a justifiable reason not to
simply write the data and delete or change it back as necessary.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com