Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

Workspace transactions - Access 2000

I have read heaps of webpages and help pages on how to declare a workspace, start a transaction using BeginTrans and cancelling it using Rollback. I can't get it to work.

I am using it as part of a form (containing a subform)

Dim wksp As Workspace

Private Sub cmd_Cancel_Click()
    wksp.Rollback
    DoCmd.Close acForm, "frm_DebtorInv"
End Sub


Private Sub Form_Open(Cancel As Integer)
    Set wksp = DBEngine.Workspaces(0)
    wksp.BeginTrans
End Sub

I've also tried
   DBEngine.Workspaces(0).BeginTrans
instead of using the wksp object variable.

I get no errors, but I can't get it to rollback the values
 - The values of 3 bound controls in the form
    and of many records in a subform (items on an invoice)

Thanks all
Brad
0
brad103
Asked:
brad103
1 Solution
 
nico5038Commented:
Hi Brad,

I'm afraid you'll have to change your approach.
As far as I know the COMMIT/ROLLBACK will only work on transactions executed in a "batch".
The form however will "break" the sequence.

I would probably switch to using temp table(s). In the FormOpen event I create the tables and when the [Save] is given I delete the original rows and insert the temp table row(s) into the original table. When the form is Cancelled I have nothing to do......

Nic;o)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now