Solved Error 3034: You tried to commit or rollback a transaction without first beginning a transaction. (1 Viewer)

[Mike Krailo]

Having a bit of trouble locating the source of this error. It always goes right to the DAO.DBEngine.Rollback line complaining about not starting a transaction when after stepping through the code, it sure does start the transaction. If I skip over that line in the debugger, everything appears to have updated correctly. Anyone know what could be causing the issue? All the code complies fine.

Private Sub MovePortBtn2_Click()
   Dim StrSQL As String
 
   TempVars!DestPortNum = ""
   If IsNull(Me.AvailablePortsCbo) Then
      MsgBox "No destination port was selected, please select a port from the dropdown."
      Exit Sub
   End If
 
   TempVars!DestPortNum = Me.AvailablePortsCbo.Column(1)
   TempVars!DestPortID = Me.AvailablePortsCbo.Column(0)
   If Not MyYesNoBox("About to move port " & TempVars!OrigPortNum & " to port " & TempVars!DestPortNum) Then
      GoTo CleanUp
   End If
      DAO.DBEngine.BeginTrans
      On Error GoTo TransErr
     
      StrSQL = "Update Port SET [PhyStatus]='BAD', [SwStatus]='DISABLED', [VLan]=666, [VName]='DUMMY', [ConnTo]='Moved to Port " &               TempVars!DestPortNum _
                     & "' WHERE PortID= " & TempVars!OrigPortID
'      Debug.Print StrSQL
     
      ' Update the selected port to BAD and put it on the DUMMY vlan of 666
      CurrentDb.Execute StrSQL, dbFailOnError
     
      StrSQL = "Update Port SET [PhyStatus]='USED', [SwStatus]='ENABLED', [VLan]=" & TempVars!OrigVLan & ", [VName]='" & TempVars!OrigVName _
                     & "', [ConnTo]='" & TempVars!OrigConnTo & "' WHERE PortID= " & TempVars!DestPortID
'      Debug.Print StrSQL
     
      ' Update the destination port to the orginal values that the BAD port had
      CurrentDb.Execute StrSQL, dbFailOnError
     
     
      DAO.DBEngine.CommitTrans
      MsgBox "Successfull! Port number " & Me!PNum & " moved to " _
                     & TempVars!DestPortNum & vbCrLf & "Notes also updated. "
     
      DoCmd.Close acForm, "MovePort"
      Exit Sub
     
CleanUp:
   ' Remove TempVars
   TempVars.Remove "DestPortNum"
   TempVars.Remove "OrigConnTo"
   TempVars.Remove "OrigPortID"
   TempVars.Remove "OrigVLan"
   TempVars.Remove "OrigVName"
   TempVars.Remove "DestPortNum"
 
   Exit Sub
   
TransErr:
   DAO.DBEngine.Rollback
   MsgBox "Transaction failed. Error: " & Err.Description
   DoCmd.Close acForm, "MovePort"
End Sub

 

[arnelgp]

you need to create a Workspace variable and instantiate the Transaction there:

Dim ws As DAO.WorkSpace

Private Sub MovePortBtn2_Click()

Set ws = DbEngine.Workspace(0) 'the default workspace

ws.BeginTrans
ws.CommitTrans
ws.Rollback

 

[Mike Krailo]

Ok I'll try that in the morning. Gotta get up early tomorrow.

 

[arnelgp]

ok, don't forget the pajamas!

 

[Mike Krailo]

Maybe you meant Set ws = DbEngine.Workspaces(0)
The singular version of Workspace didn't compile. In any case, I'm still getting the exact same error. This is so frustrating.

 

[MajP]

Any chance you throw the error after the commit. Your error trap would try a rollback. Need to move your message box before the rollback

 

[arnelgp]

yes Workspaces(0)

 

[Mike Krailo]

Thanks MajP, that helped me find the error. Moving the msgbox up over the rollback gave me an error pointing back to that msgbox line itself. The reference to Me!PNum was incorrect, changed to TempVars!OrigPortNum and everything works properly now.