Mike Krailo
Well-known member
- Local time
- Yesterday, 20:59
- Joined
- Mar 28, 2020
- Messages
- 1,044
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.
Code:
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