cancel update - multiple records on error

[rhinomac]

hi
im trying to be able to cancel multiple record updates on error.
I have a series of record updates to diffrent tables in one subform. I want to be able to say if on the third table update if theres an error to roll back the previous two updates as well.
I ve been trying to use the .cancelupdate method
-Im not getting an error it just keeps going to the error msg

Here is a sample of my code:

On Error GoTo cancelupdate
.Open "User_data", CurConn, , , adCmdTableDirect
.AddNew
![contact_name] = (Me!contact_name)
![First_Name] = (Me!First_Name)
![Last_Name] = (Me!Last_Name)
![contact_name] = (Me!contact_name)
![lm_office] = (Me!lm_office)
![dept] = (Me!dept)
![contact_phone] = (Me!contact_phone)
![lm_contact_extension] = (Me!lm_contact)
![exchange] = (Me!exchange)
![Address] = (Me!Address)
![City] = (Me!City)
![State] = (Me!State)
![Zip] = (Me!Zip)
.Close

.Open "ServiceCenterProblem", CurConn, , , adCmdTableDirect
.AddNew
![number] = (Me!number)
![contact_name] = (Me!contact_name)
![action] = (Me!action)
![update_time] = (Me!MinOfupdate_time)
![lm_updated_by_lastname] = (Me!lm_updated_by)

.Close


With rst
.Open "Inventory", CurConn, , , adCmdTableDirect
.AddNew
![serial_no_] = (Me!serial_no_)
![mfg_id] = DLookup

.Open "InventoryStatusForm", CurConn, , , adCmdTableDirect
.AddNew
![serial_no_] = (Me!serial_no_)
![number] = (Me!number)
![status_id] = (1)
![close_time] = (Me!MaxOfclose_time)
![tech_id] = DLookup
.Close
.update

cancelupdate:
MsgBox Err.Description
With rst
.cancelupdate
End With

!!!!Any help would be greatly appreciated,
Any suggestions on how to do this an alternative way would be cool to.
Thanks

 

[goodfela26]

You're going to have to add an Exit Sub right before the cancelupdate portion of the code, which I assume is at the end of the procedure. Otherwise, once it executes the .update, it just continues onto the cancelupdate portion of the code.

I hope this helps.

 

[rhinomac]

Thanks for the reply - I just shortend the actual code and left that out. So thats not the problem. Im starting to think that you need a cancel batch or rollbacktrans method.
Any suggestions..

 

[goodfela26]

Sorry about that. I thought that was the full snippet of code. What is the error message that you are getting?

 

[rhinomac]

Hey, Im not getting an error message now. I.ve actually added a rollback transaction to the form.
now the error trapper works fine but the commit transaction isnt working right.
I get my message that the table gets updated with out errors but the tables do not get updated.
So again there is no error message
am i using the commit transaction correctly.
Thanks,,,,

On Error GoTo cancelupdate
curconn.BeginTrans
.Open "User_data", CurConn, , , adCmdTableDirect
.AddNew
![contact_name] = (Me!contact_name)
![First_Name] = (Me!First_Name)
![Last_Name] = (Me!Last_Name)
![contact_name] = (Me!contact_name)
![lm_office] = (Me!lm_office)
![dept] = (Me!dept)
![contact_phone] = (Me!contact_phone)
![lm_contact_extension] = (Me!lm_contact)
![exchange] = (Me!exchange)
![Address] = (Me!Address)
![City] = (Me!City)
![State] = (Me!State)
![Zip] = (Me!Zip)
.update
.Close

.Open "ServiceCenterProblem", CurConn, , , adCmdTableDirect
.AddNew
![number] = (Me!number)
![contact_name] = (Me!contact_name)
![action] = (Me!action)
![update_time] = (Me!MinOfupdate_time)
![lm_updated_by_lastname] = (Me!lm_updated_by)

.update
.Close


With rst
.Open "Inventory", CurConn, , , adCmdTableDirect
.AddNew
![serial_no_] = (Me!serial_no_)
![mfg_id] = DLookup


.update
.Close
CurConn.CommitTrans
MsgBox Me!number & " has been added to the Center Table."
exit sub

cancelupdate:
MsgBox Err.Description
With rst
CurConn.RollbackTrans
End With

 

[goodfela26]

It looks like CommitTrans is being used correctly. I'm not sure why it's not updating the info to the table. If I think of a possible solution, I'll post again. If you figure it out, please do the same as I'm curious to what's wrong.

Thanks.

 

[rhinomac]

Dum me!
I set the record set to ad lock batch optmistic. It needs to read as follows
Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic

I must of changed it when i was tinkering around..
Thanks anyway for the help
-Rhino

 

[goodfela26]

Ahh. Couldn't see that part of the code, so I never would've thought of it. Good to know what the result was for my future reference. Sorry I couldn't help more!