Using instances and transactions

[ryetee]

In the past few months I've asked a lot of question.
Two in particular were in essence
1. How do I back out updates
2. How do I load more than 1 instance of a form
I got some great feed back and implemented both with help from this community
In isolation of each other they work a treat.
The problem I'm encountering is if I 'mix' the 2.
If for example I open instance 1 of form 1 and then make a change
I then try an open instance 2 of of form 1 I get error "Run Time Error '2074'. This operation is not supported within transaction". The line of code it stops on is .FilterOn = True

Set frm = New Form_ListSalesItems
With frm
.Filter = "salesid = " & Split(strOpenArgs, "|", 3)(0) & " and customerid = " & Split(strOpenArgs, "|", 3)(1)
.FilterOn = True
.OrderBy = "ReferenceNumber"
.OrderByOn = True
End With


If I open instance 1 of form 1 and before making any changes
I open instance 2 of of form 1 then all appears OK. However if I make changes to both forms and then when committing the changes not all are made.

I suspect the problem each transaction is 'related' to the original form and not the instance. Can I relate it to the instance.
The code to start transaction is
DBEngine.BeginTrans

 

[June7]

You open multiple instances of form to the same record?

If you haven't seen, review http://allenbrowne.com/ser-35.html

 

[MajP]

June is very late to the game and you are well beyond that. I think you are well beyond managing multiple form instances. I would look at this
https://codekabinett.com/rdumps.php?Lang=2&targetDoc=how-to-access-transaction
I do wonder why you are still in a transaction when opening a next instance. I would suggest you add DoEvents prior to opening an instance, but I unlikely that would help. My thought is timing. You are executing procedures on the recordset that is still in a transaction. See that link to ensure you are not trapped in a transaction.

 

[ryetee]

You open multiple instances of form to the same record?

If you haven't seen, review http://allenbrowne.com/ser-35.html

No. The user wants to open multiple instances of the form compare invoices/orders etc. That said now you've pointed it out there's nothing to stop him doing it. Better put a stop to that.
I used Allen's code to create multiple instances

 

[ryetee]

June is very late to the game and you are well beyond that. I think you are well beyond managing multiple form instances. I would look at this
https://codekabinett.com/rdumps.php?Lang=2&targetDoc=how-to-access-transaction
I do wonder why you are still in a transaction when opening a next instance. I would suggest you add DoEvents prior to opening an instance, but I unlikely that would help. My thought is timing. You are executing procedures on the recordset that is still in a transaction. See that link to ensure you are not trapped in a transaction.

OK I'll take a look. Slowly getting there! The user wants to be able to look at multiple invoices for a a client - hence multiple instances. He also wants the ability to discard any updates hence being in a transaction. I think i may have to put an end to that.

 

[MajP]

OK I'll take a look. Slowly getting there! The user wants to be able to look at multiple invoices for a a client - hence multiple instances.

I think you figured that out AFAIK.

The question is when you open an instance why does it think you are still within a transaction. That is the part that needs to be figured out.

 

[ryetee]

I think you figured that out AFAIK.

The question is when you open an instance why does it think you are still within a transaction. That is the part that needs to be figured out.

Because I am!

If I open a form and start to make changes there are a couple of events that start the transaction On Dirty and On Delete. At that time the user may wish to open anther instance of a form to make changes to a different set of records.
At the point I mentioned it then throws out an error message.

If the 2 forms are opened without any changes and then changes are made to both then the commits don't work as expected.

The transaction must be linked to the form and not the instance if you see what I mean. Each form I have uses transaction processing and if I open other forms and make updates it doesn't screw up any changes, well at least not with the testing I've done thus far.

 

[MajP]

If I open a form and start to make changes there are a couple of events that start the transaction On Dirty and On Delete. At that time the user may wish to open anther instance of a form to make changes to a different set of records.

Can you show some code? I am trying to wrap my head around opening a second instance in between a transaction. Are you binding a recordset to the Form or using a recordsource?

 

[ryetee]

Can you show some code? I am trying to wrap my head around opening a second instance in between a transaction. Are you binding a recordset to the Form or using a recordsource?

Hmm I think I may for this one be using a bit of both!
OK I copy this standard code to where I want to back out data. Not sure where I got this from without looking at all of my threads but I can find this out if necessary. The code is in the following events
After Update
On Delete
After Del Confirm
On dirty
On open
On unload
The code is

Option Compare Database
Option Explicit

'http://support.microsoft.com/kb/248011

Private boolFrmDirty As Boolean
Private boolFrmSaved As Boolean

Public Property Get Dirtied() As Boolean

Dirtied = boolFrmDirty

End Property

Public Property Let Dirtied(boolFrmDirtyIn As Boolean)

boolFrmDirty = boolFrmDirtyIn

End Property

Public Property Get Saved() As Boolean

Saved = boolFrmSaved

End Property

Public Property Let Saved(boolFrmSavedIn As Boolean)

boolFrmSaved = boolFrmSavedIn

End Property

Private Sub Form_AfterDelConfirm(Status As Integer)

If Me.Saved = False Then Me.Saved = (Status = acDeleteOK)

End Sub

Private Sub Form_AfterUpdate()

Me.Saved = True

End Sub

Private Sub Form_Delete(Cancel As Integer)

If Me.Dirtied = False Then DBEngine.BeginTrans
Me.Dirtied = True

End Sub

Private Sub Form_Dirty(Cancel As Integer)

If Me.Dirtied = False Then DBEngine.BeginTrans
Me.Dirtied = True

End Sub

Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset)
Set Me.Recordset = rs

End Sub

Private Sub Form_Unload(Cancel As Integer)

Dim msg As Integer

If Me.Saved Then
msg = MsgBox("Do you want to commit all changes?", vbYesNoCancel)

Select Case msg
Case vbYes
DBEngine.CommitTrans
Case vbNo
DBEngine.Rollback
Case vbCancel
Cancel = True
End Select
Else
If Me.Dirtied Then DBEngine.Rollback
End If

End Sub

Obviously Table1 needs to be changed to appropriate table/query name.

My instances started out as life from http://allenbrowne.com/ser-35.html but I've tweaked it after responses on here.
I now have for the one that is giving me the grief the following

Set frm = New Form_ListSalesItems
With frm
strNew = [Forms]![Customer]![txtCustomer]
.Filter = "salesid = " & 999999999 & " and customerid = " & strNew
.FilterOn = True
.OrderBy = "ReferenceNumber"
End With

frm.Caption = strOpenArgs & " List Items " & frm.Hwnd & ", opened " & Now()
strOpenArgs = "|" & [Forms]![Customer]![txtCustomer] & "|" & strOpenArgs
frm.MyProperty = strOpenArgs
frm.Visible = True

'Append it to our collection.
clnListSalesItems.Add Item:=frm, Key:=CStr(frm.Hwnd)
Set frm = Nothing

Hope this is clear and what you're after

 

[MajP]

I was able to recreate, and I think there is no simple workaround. It makes sense to me that you can not do any recordset manipulation while in a transaction.

When you use transactions, all databases and Recordset objects in the specified Workspace object are affected; transactions are global to the workspace, not to a specific database or Recordset. If you perform operations on more than one database or within a workspace transaction, the Commit and Rollback methods affect all the objects changed within that workspace during the transaction

I see 2 options.
1) Do not use transactions. For what you are doing I really am not seeing the need for transactions. If you simply want to be able to "roll back" the updates then you can accomplish that in the before update event by canceling the event and undoing.
2) Use unbound forms. If you really want to manage the save/delete of a record and still use multi instances then I would make the form unbound.

 

[ryetee]

I was able to recreate, and I think there is no simple workaround. It makes sense to me that you can not do any recordset manipulation while in a transaction.

I see 2 options.
1) Do not use transactions. For what you are doing I really am not seeing the need for transactions. If you simply want to be able to "roll back" the updates then you can accomplish that in the before update event by canceling the event and undoing.
2) Use unbound forms. If you really want to manage the save/delete of a record and still use multi instances then I would make the form unbound.

OK I'm not sure I can do 1). I have a continuous form so rows are presented like a datasheet. So, and tell me if I'm wrong here, if changes are made to say rows 1 and 2, the before update can give the user the option to cancel or not. If he doesn't and the user then changes row 3 he may want to back everything including changes to the 1st 2 rows. Amy I right in saying only row 3 will get cancelled?
2. The processing can be a little bit more than just amending/adding/deleting a row. By adding a row a number of other tables can get changed including the adding of other rows to other tables.

 

[MajP]

I assumed this would be a single form view. I guess the form instances are details of records in the single form view. I am assuming you need to have multiple instances so the user can compare data side by side.
You are correct you cannot replicate "rolling back" multiple records. This would be done in the before update event of the form which in datasheet view would happen at every record change. So you would get the confirmation message to "undo" if you switch rows in your datasheet and could never get to the case of having multiple rows with changes that you did not already agree to.
Making a continuous unbound form is possible, but would be a lot of work. You would put N rows on a form and have buttons to move to back and forth to the next N records. If the Pop ups form instances are single view and not overly complex you could make them unbound.

However, I think this may be a very bad idea in general. This is not how transactions should be utilized. It is not meant for locking down the db and rolling back multiple records. Normally this is for multiple actions that need to take place simultaneously. Like withdraw from one account and deposit in another. In your design you could be locking multi users out of a lot of the database waiting on a user interaction to confirm a transaction. Hopefully that person does not leave for the day with an open transaction. I would simply make them confirm after each record in the before update or do this with a temp table or unbound form.

 

[MajP]

I would relook at this article. It definitely cautions against what you are doing.
https://codekabinett.com/rdumps.php?Lang=2&targetDoc=how-to-access-transaction