Hi,

I have a class module. When I call it from my form module, I get the error
3034 "You tried to commit or rollback a transaction without first beginning a
transaction".

What am I missing

Thanks,

J Gold

Option Compare Database
Option Explicit

Private wrkSpace As DAO.Workspace
Private dbUnit As DAO.Database

Private Sub Class_Initialize()

Set wrkSpace = DBEngine(0)
Set dbUnit = CurrentDb

End Sub

Public Function add_unit(newUnitID As Integer, newUnitType As String,
newUnitName As String, _
newBuildingName As String, newBuildingAddress As String,
newRoomNumber As String) As Boolean

Dim sqlQuery As String
Dim intDAOErrNo As Integer, intCtr As Integer


On Error GoTo ErrorHandler

sqlQuery = "INSERT INTO Unit (unitID, unitName, buildingName ,
buildingAddress, roomNumber, unitType) VALUES (" _
+ newUnitID + ",'" + newUnitName + " ','" + newBuildingName +
"','" + newBuildingAddress _
+ "','" + newRoomNumber + "','" + newUnitType + "')"

wrkSpace.BeginTrans


dbUnit.Execute sqlQuery

wrkSpace.CommitTrans dbForceOSFlush

Done:
Exit Function

ErrorHandler:

wrkSpace.Rollback

Resume ErrorHandler_Exit

ErrorHandler_Exit:

wrkSpace.Close

Exit Function

End Function

Private Sub Class_Terminate()

Set wrkSpace = Nothing
Set dbUnit = Nothing
End Sub

I'd bet that this is because you had a unrelated error that occurred
prior to beginning a transaction and the control went to error handler
which then tried to rollback the transaction. You may want to debug your
VBA to see what actually caused the error.

j_gold wrote:
> Hi,
>
> I have a class module. When I call it from my form module, I get the error
> 3034 "You tried to commit or rollback a transaction without first beginning a
> transaction".
>
> What am I missing
>
> Thanks,
>
> J Gold
>
> Option Compare Database
> Option Explicit
>
> Private wrkSpace As DAO.Workspace
> Private dbUnit As DAO.Database
>
> Private Sub Class_Initialize()
>
> Set wrkSpace = DBEngine(0)
> Set dbUnit = CurrentDb
>
> End Sub
>
> Public Function add_unit(newUnitID As Integer, newUnitType As String,
> newUnitName As String, _
> newBuildingName As String, newBuildingAddress As String,
> newRoomNumber As String) As Boolean
>
> Dim sqlQuery As String
> Dim intDAOErrNo As Integer, intCtr As Integer
>
>
> On Error GoTo ErrorHandler
>
> sqlQuery = "INSERT INTO Unit (unitID, unitName, buildingName ,
> buildingAddress, roomNumber, unitType) VALUES (" _
> + newUnitID + ",'" + newUnitName + " ','" + newBuildingName +
> "','" + newBuildingAddress _
> + "','" + newRoomNumber + "','" + newUnitType + "')"
>
> wrkSpace.BeginTrans
>
>
> dbUnit.Execute sqlQuery
>
> wrkSpace.CommitTrans dbForceOSFlush
>
> Done:
> Exit Function
>
> ErrorHandler:
>
> wrkSpace.Rollback
>
> Resume ErrorHandler_Exit
>
> ErrorHandler_Exit:
>
> wrkSpace.Close
>
> Exit Function
>
> End Function
>
> Private Sub Class_Terminate()
>
> Set wrkSpace = Nothing
> Set dbUnit = Nothing
> End Sub
>

Technically, the error handling you specified should be placed AFTER the
beginTrans, since any error occuring before that would not have a started
transaction to rollback. Why is there code which close the workspace? have
you removed some code? are you sure your variable wrkSpace is NOT Nothing
when you call (again) add_unit?



I would also use a database defined from my workspace, rather than CurrentDb
(to be more explicit about what I do)


----------------------------------
Public Sub testTrans()
Dim wks As Workspace: Set wks = DBEngine(0)
Dim db As Database: Set db = wks(0)

Debug.Assert Not (wks Is Nothing)

wks.BeginTrans
On Error GoTo RollbackMe

' the following is an error, invalid syntax ...
db.Execute "INSERT this INTO that ", dbFailOnError
wks.CommitTrans
Exit Sub

RollbackMe:
wks.Rollback
Exit Sub

End Sub
-----------------------------------------------


Vanderghast, Access MVP


"j_gold" <(E-Mail Removed)> wrote in message
news:00C6412C-77C7-4090-B8E0-(E-Mail Removed)...
> Hi,
>
> I have a class module. When I call it from my form module, I get the error
> 3034 "You tried to commit or rollback a transaction without first
> beginning a
> transaction".
>
> What am I missing
>
> Thanks,
>
> J Gold
>
> Option Compare Database
> Option Explicit
>
> Private wrkSpace As DAO.Workspace
> Private dbUnit As DAO.Database
>
> Private Sub Class_Initialize()
>
> Set wrkSpace = DBEngine(0)
> Set dbUnit = CurrentDb
>
> End Sub
>
> Public Function add_unit(newUnitID As Integer, newUnitType As String,
> newUnitName As String, _
> newBuildingName As String, newBuildingAddress As
> String,
> newRoomNumber As String) As Boolean
>
> Dim sqlQuery As String
> Dim intDAOErrNo As Integer, intCtr As Integer
>
>
> On Error GoTo ErrorHandler
>
> sqlQuery = "INSERT INTO Unit (unitID, unitName, buildingName ,
> buildingAddress, roomNumber, unitType) VALUES (" _
> + newUnitID + ",'" + newUnitName + " ','" + newBuildingName +
> "','" + newBuildingAddress _
> + "','" + newRoomNumber + "','" + newUnitType + "')"
>
> wrkSpace.BeginTrans
>
>
> dbUnit.Execute sqlQuery
>
> wrkSpace.CommitTrans dbForceOSFlush
>
> Done:
> Exit Function
>
> ErrorHandler:
>
> wrkSpace.Rollback
>
> Resume ErrorHandler_Exit
>
> ErrorHandler_Exit:
>
> wrkSpace.Close
>
> Exit Function
>
> End Function
>
> Private Sub Class_Terminate()
>
> Set wrkSpace = Nothing
> Set dbUnit = Nothing
> End Sub
>

"j_gold" <(E-Mail Removed)> wrote in message
news:00C6412C-77C7-4090-B8E0-(E-Mail Removed)...
> Hi,
>
> I have a class module. When I call it from my form module, I get the error
> 3034 "You tried to commit or rollback a transaction without first
> beginning a
> transaction".
>
> What am I missing
>
> Thanks,
>
> J Gold
>
> Option Compare Database
> Option Explicit
>
> Private wrkSpace As DAO.Workspace
> Private dbUnit As DAO.Database
>
> Private Sub Class_Initialize()
>
> Set wrkSpace = DBEngine(0)
> Set dbUnit = CurrentDb
>
> End Sub
>
> Public Function add_unit(newUnitID As Integer, newUnitType As String,
> newUnitName As String, _
> newBuildingName As String, newBuildingAddress As
> String,
> newRoomNumber As String) As Boolean
>
> Dim sqlQuery As String
> Dim intDAOErrNo As Integer, intCtr As Integer
>
>
> On Error GoTo ErrorHandler
>
> sqlQuery = "INSERT INTO Unit (unitID, unitName, buildingName ,
> buildingAddress, roomNumber, unitType) VALUES (" _
> + newUnitID + ",'" + newUnitName + " ','" + newBuildingName +
> "','" + newBuildingAddress _
> + "','" + newRoomNumber + "','" + newUnitType + "')"
>
> wrkSpace.BeginTrans
>
>
> dbUnit.Execute sqlQuery
>
> wrkSpace.CommitTrans dbForceOSFlush
>
> Done:
> Exit Function
>
> ErrorHandler:
>
> wrkSpace.Rollback
>
> Resume ErrorHandler_Exit
>
> ErrorHandler_Exit:
>
> wrkSpace.Close
>
> Exit Function
>
> End Function
>
> Private Sub Class_Terminate()
>
> Set wrkSpace = Nothing
> Set dbUnit = Nothing
> End Sub
>


You're getting a type mismatch error before you ever begin your transaction,
because this line:

> sqlQuery = "INSERT INTO Unit (unitID, unitName, buildingName ,
> buildingAddress, roomNumber, unitType) VALUES (" _
> + newUnitID + ",'" + newUnitName + " ','" + newBuildingName +
> "','" + newBuildingAddress _
> + "','" + newRoomNumber + "','" + newUnitType + "')"


.... uses the + operator in an attempt to concatenate an integer and a
string. But with an integer as one of the operands, the + operator will be
construed as signifying addition, not concatenation. It's safer to use the
& operator for concatenation:

sqlQuery = _
"INSERT INTO Unit (" & _
"unitID, unitName, buildingName, " &
"buildingAddress, roomNumber, unitType" & _
") VALUES (" & _
newUnitID & ",'" & newUnitName & " ','" & _
newBuildingName & "','" & newBuildingAddress _
& "','" & newRoomNumber & "','" & newUnitType & "')"

As a side note, do not close the default workspace. I don't think Jet will
let you, anyway, but you shouldn't even try to execute this statement:

> wrkSpace.Close


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Thanks Banana, sorry for the late reply, this message got lost.

"Banana" wrote:

> I'd bet that this is because you had a unrelated error that occurred
> prior to beginning a transaction and the control went to error handler
> which then tried to rollback the transaction. You may want to debug your
> VBA to see what actually caused the error.
>
> j_gold wrote:
> > Hi,
> >
> > I have a class module. When I call it from my form module, I get the error
> > 3034 "You tried to commit or rollback a transaction without first beginning a
> > transaction".
> >
> > What am I missing
> >
> > Thanks,
> >
> > J Gold
> >
> > Option Compare Database
> > Option Explicit
> >
> > Private wrkSpace As DAO.Workspace
> > Private dbUnit As DAO.Database
> >
> > Private Sub Class_Initialize()
> >
> > Set wrkSpace = DBEngine(0)
> > Set dbUnit = CurrentDb
> >
> > End Sub
> >
> > Public Function add_unit(newUnitID As Integer, newUnitType As String,
> > newUnitName As String, _
> > newBuildingName As String, newBuildingAddress As String,
> > newRoomNumber As String) As Boolean
> >
> > Dim sqlQuery As String
> > Dim intDAOErrNo As Integer, intCtr As Integer
> >
> >
> > On Error GoTo ErrorHandler
> >
> > sqlQuery = "INSERT INTO Unit (unitID, unitName, buildingName ,
> > buildingAddress, roomNumber, unitType) VALUES (" _
> > + newUnitID + ",'" + newUnitName + " ','" + newBuildingName +
> > "','" + newBuildingAddress _
> > + "','" + newRoomNumber + "','" + newUnitType + "')"
> >
> > wrkSpace.BeginTrans
> >
> >
> > dbUnit.Execute sqlQuery
> >
> > wrkSpace.CommitTrans dbForceOSFlush
> >
> > Done:
> > Exit Function
> >
> > ErrorHandler:
> >
> > wrkSpace.Rollback
> >
> > Resume ErrorHandler_Exit
> >
> > ErrorHandler_Exit:
> >
> > wrkSpace.Close
> >
> > Exit Function
> >
> > End Function
> >
> > Private Sub Class_Terminate()
> >
> > Set wrkSpace = Nothing
> > Set dbUnit = Nothing
> > End Sub
> >
>

Thanks vanderghast, sorry for the delay in responding, I lost these replies
in my inbox.

I'm still new to VBA and I obviously missed an important step - I was
placing all my On Errors before not after.

Cheers,

J Gold

"vanderghast" wrote:

> Technically, the error handling you specified should be placed AFTER the
> beginTrans, since any error occuring before that would not have a started
> transaction to rollback. Why is there code which close the workspace? have
> you removed some code? are you sure your variable wrkSpace is NOT Nothing
> when you call (again) add_unit?
>
>
>
> I would also use a database defined from my workspace, rather than CurrentDb
> (to be more explicit about what I do)
>
>
> ----------------------------------
> Public Sub testTrans()
> Dim wks As Workspace: Set wks = DBEngine(0)
> Dim db As Database: Set db = wks(0)
>
> Debug.Assert Not (wks Is Nothing)
>
> wks.BeginTrans
> On Error GoTo RollbackMe
>
> ' the following is an error, invalid syntax ...
> db.Execute "INSERT this INTO that ", dbFailOnError
> wks.CommitTrans
> Exit Sub
>
> RollbackMe:
> wks.Rollback
> Exit Sub
>
> End Sub
> -----------------------------------------------
>
>
> Vanderghast, Access MVP
>
>
> "j_gold" <(E-Mail Removed)> wrote in message
> news:00C6412C-77C7-4090-B8E0-(E-Mail Removed)...
> > Hi,
> >
> > I have a class module. When I call it from my form module, I get the error
> > 3034 "You tried to commit or rollback a transaction without first
> > beginning a
> > transaction".
> >
> > What am I missing
> >
> > Thanks,
> >
> > J Gold
> >
> > Option Compare Database
> > Option Explicit
> >
> > Private wrkSpace As DAO.Workspace
> > Private dbUnit As DAO.Database
> >
> > Private Sub Class_Initialize()
> >
> > Set wrkSpace = DBEngine(0)
> > Set dbUnit = CurrentDb
> >
> > End Sub
> >
> > Public Function add_unit(newUnitID As Integer, newUnitType As String,
> > newUnitName As String, _
> > newBuildingName As String, newBuildingAddress As
> > String,
> > newRoomNumber As String) As Boolean
> >
> > Dim sqlQuery As String
> > Dim intDAOErrNo As Integer, intCtr As Integer
> >
> >
> > On Error GoTo ErrorHandler
> >
> > sqlQuery = "INSERT INTO Unit (unitID, unitName, buildingName ,
> > buildingAddress, roomNumber, unitType) VALUES (" _
> > + newUnitID + ",'" + newUnitName + " ','" + newBuildingName +
> > "','" + newBuildingAddress _
> > + "','" + newRoomNumber + "','" + newUnitType + "')"
> >
> > wrkSpace.BeginTrans
> >
> >
> > dbUnit.Execute sqlQuery
> >
> > wrkSpace.CommitTrans dbForceOSFlush
> >
> > Done:
> > Exit Function
> >
> > ErrorHandler:
> >
> > wrkSpace.Rollback
> >
> > Resume ErrorHandler_Exit
> >
> > ErrorHandler_Exit:
> >
> > wrkSpace.Close
> >
> > Exit Function
> >
> > End Function
> >
> > Private Sub Class_Terminate()
> >
> > Set wrkSpace = Nothing
> > Set dbUnit = Nothing
> > End Sub
> >
>

Thanks Dirk, my apologies for not responding sooner, the replies got lost in
my inbox.

Appreciate the tip on the & vs + operators. Will save me a few headaches I'm
sure.

Cheers,

J Gold

"Dirk Goldgar" wrote:

> "j_gold" <(E-Mail Removed)> wrote in message
> news:00C6412C-77C7-4090-B8E0-(E-Mail Removed)...
> > Hi,
> >
> > I have a class module. When I call it from my form module, I get the error
> > 3034 "You tried to commit or rollback a transaction without first
> > beginning a
> > transaction".
> >
> > What am I missing
> >
> > Thanks,
> >
> > J Gold
> >
> > Option Compare Database
> > Option Explicit
> >
> > Private wrkSpace As DAO.Workspace
> > Private dbUnit As DAO.Database
> >
> > Private Sub Class_Initialize()
> >
> > Set wrkSpace = DBEngine(0)
> > Set dbUnit = CurrentDb
> >
> > End Sub
> >
> > Public Function add_unit(newUnitID As Integer, newUnitType As String,
> > newUnitName As String, _
> > newBuildingName As String, newBuildingAddress As
> > String,
> > newRoomNumber As String) As Boolean
> >
> > Dim sqlQuery As String
> > Dim intDAOErrNo As Integer, intCtr As Integer
> >
> >
> > On Error GoTo ErrorHandler
> >
> > sqlQuery = "INSERT INTO Unit (unitID, unitName, buildingName ,
> > buildingAddress, roomNumber, unitType) VALUES (" _
> > + newUnitID + ",'" + newUnitName + " ','" + newBuildingName +
> > "','" + newBuildingAddress _
> > + "','" + newRoomNumber + "','" + newUnitType + "')"
> >
> > wrkSpace.BeginTrans
> >
> >
> > dbUnit.Execute sqlQuery
> >
> > wrkSpace.CommitTrans dbForceOSFlush
> >
> > Done:
> > Exit Function
> >
> > ErrorHandler:
> >
> > wrkSpace.Rollback
> >
> > Resume ErrorHandler_Exit
> >
> > ErrorHandler_Exit:
> >
> > wrkSpace.Close
> >
> > Exit Function
> >
> > End Function
> >
> > Private Sub Class_Terminate()
> >
> > Set wrkSpace = Nothing
> > Set dbUnit = Nothing
> > End Sub
> >
>
>
> You're getting a type mismatch error before you ever begin your transaction,
> because this line:
>
> > sqlQuery = "INSERT INTO Unit (unitID, unitName, buildingName ,
> > buildingAddress, roomNumber, unitType) VALUES (" _
> > + newUnitID + ",'" + newUnitName + " ','" + newBuildingName +
> > "','" + newBuildingAddress _
> > + "','" + newRoomNumber + "','" + newUnitType + "')"
>
>
> ... uses the + operator in an attempt to concatenate an integer and a
> string. But with an integer as one of the operands, the + operator will be
> construed as signifying addition, not concatenation. It's safer to use the
> & operator for concatenation:
>
> sqlQuery = _
> "INSERT INTO Unit (" & _
> "unitID, unitName, buildingName, " &
> "buildingAddress, roomNumber, unitType" & _
> ") VALUES (" & _
> newUnitID & ",'" & newUnitName & " ','" & _
> newBuildingName & "','" & newBuildingAddress _
> & "','" & newRoomNumber & "','" & newUnitType & "')"
>
> As a side note, do not close the default workspace. I don't think Jet will
> let you, anyway, but you shouldn't even try to execute this statement:
>
> > wrkSpace.Close
>
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>