By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,395 Members | 2,537 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,395 IT Pros & Developers. It's quick & easy.

Having trouble setting up transactions

P: n/a
TD
I am trying to add transactions to my code. The original code worked
fine until I followed an example to setup transactions, now the code
does strange things, but no error messages.

Could someone please review my before and after code and tell me the
proper way to add transactions to my code?

Thanks,
TD

------------------------------------------------------
Before transactions added:----------------------------
------------------------------------------------------
Private Sub Sold_AfterUpdate()
On Error GoTo Err_Sold_AfterUpdate

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim recid As Long
Dim invnum As String
Dim qnty As Long
Dim child As Long

'-----------------False-------------------------
If Me.Sold = False Then

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold]
child = Forms![frmInventory]![frmSubInventory].Form![ChildID]

'-----Find QntyOnHand in parent record----------

sql = ""
sql = sql & "SELECT tblInventory.QntyOnHand FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"

Parent![txtInvNum].SetFocus
Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'--------Add qnty sold back to parent record----------

qnty = qnty + rst(0)

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = " & qnty & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"

db.Execute sql

'----------------Delete child record------------------

sql = ""
sql = sql & "DELETE tblInventory.* FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

db.Execute sql

rst.Close
db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True Then
Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True
ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Then
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

GoTo Exit_Sold_AfterUpdate

'-----------------True----------------------

ElseIf Me.Sold = True Then

'------Create recordset--------------------

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
invnum = Forms![frmInventory].txtInvNum
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold]

sql = ""
sql = sql & "SELECT * FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

Parent![txtInvNum].SetFocus
Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'-------Check if recordset empty--------

If rst.BOF = True And rst.EOF = True Then
rst.Close
db.Close
GoTo Exit_Sold_AfterUpdate
End If

'------Append new record, stuff just sold------------

sql = ""
sql = sql & "INSERT INTO tblInventory ( VendorID, ChildID, "
sql = sql & "DatePurchased, Custom1, Custom2, Custom3, Custom4,

QntyOnHand, "
sql = sql & "Description, SellingPrice, QntySold, SalesInvNumber,

Sold ) "
sql = sql & "VALUES('" & rst(1) & "'," & recid & ",'" & rst(3) &

"',"
sql = sql & "'" & rst(4) & "','" & rst(5) & "','" & rst(6) & "','"

& rst(7) & "',"
sql = sql & "0,'" & rst(9) & "','" & rst(10) & "','" & qnty & "',"
sql = sql & "'" & invnum & "'," & True & ");"

db.Execute sql

'----Update old record, items left after last sell--------------

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = (('" & rst(8) & "') - (" &

qnty & ")), "
sql = sql & "tblInventory.QntySold = 0, "
sql = sql & "tblInventory.SalesInvNumber = 0, "
sql = sql & "tblInventory.Sold = " & False & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

db.Execute sql
rst.Close
db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True Then
Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True
ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Then
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

GoTo Exit_Sold_AfterUpdate

End If

Exit_Sold_AfterUpdate:
Exit Sub

Err_Sold_AfterUpdate:
MsgBox Err.Description
Resume Exit_Sold_AfterUpdate

End Sub

---------------------------------------------------------------
After I followed the example to add transactions:--------------
---------------------------------------------------------------
Private Sub Sold_AfterUpdate()
On Error GoTo Err_Sold_AfterUpdate

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim bIntrans As Boolean
Dim sql As String
Dim recid As Long
Dim invnum As String
Dim qnty As Long
Dim child As Long

Set ws = DBEngine(0)
ws.BeginTrans
bIntrans = True
Set db = OpenDatabase("C:\BMS\adsi.mdb")

'-----------------False-------------------------
If Me.Sold = False Then

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold]
child = Forms![frmInventory]![frmSubInventory].Form![ChildID]

'-----Find QntyOnHand in parent record----------

sql = ""
sql = sql & "SELECT tblInventory.QntyOnHand FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"

Parent![txtInvNum].SetFocus
'Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'--------Add qnty sold back to parent record----------

qnty = qnty + rst(0)

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = " & qnty & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"

db.Execute sql

'----------------Delete child record------------------

sql = ""
sql = sql & "DELETE tblInventory.* FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

db.Execute sql

rst.Close
'db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True Then
Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True
ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Then
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

ws.CommitTrans
bIntrans = False
GoTo Exit_Sold_AfterUpdate

'-----------------True----------------------

ElseIf Me.Sold = True Then

'------Create recordset--------------------

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
invnum = Forms![frmInventory].txtInvNum
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold]

sql = ""
sql = sql & "SELECT * FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

Parent![txtInvNum].SetFocus
'Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'-------Check if recordset empty--------

If rst.BOF = True And rst.EOF = True Then
rst.Close
'db.Close
GoTo Exit_Sold_AfterUpdate
End If

'------Append new record, stuff just sold------------

sql = ""
sql = sql & "INSERT INTO tblInventory ( VendorID, ChildID, "
sql = sql & "DatePurchased, Custom1, Custom2, Custom3, Custom4,
QntyOnHand, "
sql = sql & "Description, SellingPrice, QntySold, SalesInvNumber,
Sold ) "
sql = sql & "VALUES('" & rst(1) & "'," & recid & ",'" & rst(3) &
"',"
sql = sql & "'" & rst(4) & "','" & rst(5) & "','" & rst(6) & "','"
& rst(7) & "',"
sql = sql & "0,'" & rst(9) & "','" & rst(10) & "','" & qnty & "',"
sql = sql & "'" & invnum & "'," & True & ");"
'
db.Execute sql

'----Update old record, items left after last sell--------------

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = (('" & rst(8) & "') - (" &
qnty & ")), "
sql = sql & "tblInventory.QntySold = 0, "
sql = sql & "tblInventory.SalesInvNumber = 0, "
sql = sql & "tblInventory.Sold = " & False & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

db.Execute sql
rst.Close
'db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True Then
Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True
ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Then
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

ws.CommitTrans
bIntrans = False
GoTo Exit_Sold_AfterUpdate

End If

Exit_Sold_AfterUpdate:

On Error Resume Next

If bIntrans = True Then
ws.Rollback
End If

Set db = Nothing
Set ws = Nothing

Exit Sub

Err_Sold_AfterUpdate:
MsgBox Err.Description, vbExclamation, "Archiving Failed: Error " &
Err.number
Resume Exit_Sold_AfterUpdate

End Sub

Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
First, let me compliment you on your very legible code (I could nit pick
structure, of course because I'm picky, but...)

Now, I don't know if this is your problem, but I would -never- try to use a
transaction in code using the default workspace because of potential side
effects - both side effects of other code on your transaction and of your
transaction on other code. Simply changing "Set ws = DBEngine(0)" to "Set ws
= DefaultWorkspaceClone" will fix that.

Another issue is that you're not using the ws reference to open the database.
That should be OK if you are using the default worskpace (which I'm saying to
stop doing), but it looks suspicious to me - better to be explicit, and change
Set db = OpenDatabase("C:\BMS\adsi.mdb") to Set db =
ws.OpenDatabase("C:\BMS\adsi.mdb").

On 7 Feb 2005 09:51:44 -0800, "TD" <dl**@adsi-sc.com> wrote:
I am trying to add transactions to my code. The original code worked
fine until I followed an example to setup transactions, now the code
does strange things, but no error messages.

Could someone please review my before and after code and tell me the
proper way to add transactions to my code?

Thanks,
TD

------------------------------------------------------
Before transactions added:----------------------------
------------------------------------------------------
Private Sub Sold_AfterUpdate()
On Error GoTo Err_Sold_AfterUpdate

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim recid As Long
Dim invnum As String
Dim qnty As Long
Dim child As Long

'-----------------False-------------------------
If Me.Sold = False Then

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold]
child = Forms![frmInventory]![frmSubInventory].Form![ChildID]

'-----Find QntyOnHand in parent record----------

sql = ""
sql = sql & "SELECT tblInventory.QntyOnHand FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"

Parent![txtInvNum].SetFocus
Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'--------Add qnty sold back to parent record----------

qnty = qnty + rst(0)

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = " & qnty & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"

db.Execute sql

'----------------Delete child record------------------

sql = ""
sql = sql & "DELETE tblInventory.* FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

db.Execute sql

rst.Close
db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True Then
Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True
ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Then
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

GoTo Exit_Sold_AfterUpdate

'-----------------True----------------------

ElseIf Me.Sold = True Then

'------Create recordset--------------------

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
invnum = Forms![frmInventory].txtInvNum
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold]

sql = ""
sql = sql & "SELECT * FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

Parent![txtInvNum].SetFocus
Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'-------Check if recordset empty--------

If rst.BOF = True And rst.EOF = True Then
rst.Close
db.Close
GoTo Exit_Sold_AfterUpdate
End If

'------Append new record, stuff just sold------------

sql = ""
sql = sql & "INSERT INTO tblInventory ( VendorID, ChildID, "
sql = sql & "DatePurchased, Custom1, Custom2, Custom3, Custom4,

QntyOnHand, "
sql = sql & "Description, SellingPrice, QntySold, SalesInvNumber,

Sold ) "
sql = sql & "VALUES('" & rst(1) & "'," & recid & ",'" & rst(3) &

"',"
sql = sql & "'" & rst(4) & "','" & rst(5) & "','" & rst(6) & "','"

& rst(7) & "',"
sql = sql & "0,'" & rst(9) & "','" & rst(10) & "','" & qnty & "',"
sql = sql & "'" & invnum & "'," & True & ");"

db.Execute sql

'----Update old record, items left after last sell--------------

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = (('" & rst(8) & "') - (" &

qnty & ")), "
sql = sql & "tblInventory.QntySold = 0, "
sql = sql & "tblInventory.SalesInvNumber = 0, "
sql = sql & "tblInventory.Sold = " & False & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

db.Execute sql
rst.Close
db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True Then
Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True
ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Then
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

GoTo Exit_Sold_AfterUpdate

End If

Exit_Sold_AfterUpdate:
Exit Sub

Err_Sold_AfterUpdate:
MsgBox Err.Description
Resume Exit_Sold_AfterUpdate

End Sub

---------------------------------------------------------------
After I followed the example to add transactions:--------------
---------------------------------------------------------------
Private Sub Sold_AfterUpdate()
On Error GoTo Err_Sold_AfterUpdate

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim bIntrans As Boolean
Dim sql As String
Dim recid As Long
Dim invnum As String
Dim qnty As Long
Dim child As Long

Set ws = DBEngine(0)
ws.BeginTrans
bIntrans = True
Set db = OpenDatabase("C:\BMS\adsi.mdb")

'-----------------False-------------------------
If Me.Sold = False Then

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold]
child = Forms![frmInventory]![frmSubInventory].Form![ChildID]

'-----Find QntyOnHand in parent record----------

sql = ""
sql = sql & "SELECT tblInventory.QntyOnHand FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"

Parent![txtInvNum].SetFocus
'Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'--------Add qnty sold back to parent record----------

qnty = qnty + rst(0)

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = " & qnty & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"

db.Execute sql

'----------------Delete child record------------------

sql = ""
sql = sql & "DELETE tblInventory.* FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

db.Execute sql

rst.Close
'db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True Then
Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True
ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Then
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

ws.CommitTrans
bIntrans = False
GoTo Exit_Sold_AfterUpdate

'-----------------True----------------------

ElseIf Me.Sold = True Then

'------Create recordset--------------------

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
invnum = Forms![frmInventory].txtInvNum
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold]

sql = ""
sql = sql & "SELECT * FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

Parent![txtInvNum].SetFocus
'Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'-------Check if recordset empty--------

If rst.BOF = True And rst.EOF = True Then
rst.Close
'db.Close
GoTo Exit_Sold_AfterUpdate
End If

'------Append new record, stuff just sold------------

sql = ""
sql = sql & "INSERT INTO tblInventory ( VendorID, ChildID, "
sql = sql & "DatePurchased, Custom1, Custom2, Custom3, Custom4,
QntyOnHand, "
sql = sql & "Description, SellingPrice, QntySold, SalesInvNumber,
Sold ) "
sql = sql & "VALUES('" & rst(1) & "'," & recid & ",'" & rst(3) &
"',"
sql = sql & "'" & rst(4) & "','" & rst(5) & "','" & rst(6) & "','"
& rst(7) & "',"
sql = sql & "0,'" & rst(9) & "','" & rst(10) & "','" & qnty & "',"
sql = sql & "'" & invnum & "'," & True & ");"
'
db.Execute sql

'----Update old record, items left after last sell--------------

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = (('" & rst(8) & "') - (" &
qnty & ")), "
sql = sql & "tblInventory.QntySold = 0, "
sql = sql & "tblInventory.SalesInvNumber = 0, "
sql = sql & "tblInventory.Sold = " & False & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

db.Execute sql
rst.Close
'db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True Then
Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True
ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Then
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

ws.CommitTrans
bIntrans = False
GoTo Exit_Sold_AfterUpdate

End If

Exit_Sold_AfterUpdate:

On Error Resume Next

If bIntrans = True Then
ws.Rollback
End If

Set db = Nothing
Set ws = Nothing

Exit Sub

Err_Sold_AfterUpdate:
MsgBox Err.Description, vbExclamation, "Archiving Failed: Error " &
Err.number
Resume Exit_Sold_AfterUpdate

End Sub


Nov 13 '05 #2

P: n/a
TD
I made the changes you suggested and code still not working correctly.
If the Me.Sold is true then it adds the new record but does not update
the origianl record. This worked before transactions but not now.
Also when Me.Sold is false it runs the code for the true part instead
of the section for false.
I am always willing to learn to program better so please tell what
bothers you about the structure.
Steve Jorgensen wrote:
First, let me compliment you on your very legible code (I could nit pick structure, of course because I'm picky, but...)

Now, I don't know if this is your problem, but I would -never- try to use a transaction in code using the default workspace because of potential side effects - both side effects of other code on your transaction and of your transaction on other code. Simply changing "Set ws = DBEngine(0)" to "Set ws = DefaultWorkspaceClone" will fix that.

Another issue is that you're not using the ws reference to open the database. That should be OK if you are using the default worskpace (which I'm saying to stop doing), but it looks suspicious to me - better to be explicit, and change Set db = OpenDatabase("C:\BMS\adsi.mdb") to Set db =
ws.OpenDatabase("C:\BMS\adsi.mdb").

On 7 Feb 2005 09:51:44 -0800, "TD" <dl**@adsi-sc.com> wrote:
I am trying to add transactions to my code. The original code workedfine until I followed an example to setup transactions, now the code
does strange things, but no error messages.

Could someone please review my before and after code and tell me the
proper way to add transactions to my code?

Thanks,
TD

------------------------------------------------------
Before transactions added:----------------------------
------------------------------------------------------
Private Sub Sold_AfterUpdate()
On Error GoTo Err_Sold_AfterUpdate

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim recid As Long
Dim invnum As String
Dim qnty As Long
Dim child As Long

'-----------------False-------------------------
If Me.Sold = False Then

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold]
child = Forms![frmInventory]![frmSubInventory].Form![ChildID]

'-----Find QntyOnHand in parent record----------

sql = ""
sql = sql & "SELECT tblInventory.QntyOnHand FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"

Parent![txtInvNum].SetFocus
Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'--------Add qnty sold back to parent record----------

qnty = qnty + rst(0)

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = " & qnty & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"

db.Execute sql

'----------------Delete child record------------------

sql = ""
sql = sql & "DELETE tblInventory.* FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

db.Execute sql

rst.Close
db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True Then Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True
ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False

Then
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

GoTo Exit_Sold_AfterUpdate

'-----------------True----------------------

ElseIf Me.Sold = True Then

'------Create recordset--------------------

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
invnum = Forms![frmInventory].txtInvNum
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold]

sql = ""
sql = sql & "SELECT * FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

Parent![txtInvNum].SetFocus
Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'-------Check if recordset empty--------

If rst.BOF = True And rst.EOF = True Then
rst.Close
db.Close
GoTo Exit_Sold_AfterUpdate
End If

'------Append new record, stuff just sold------------

sql = ""
sql = sql & "INSERT INTO tblInventory ( VendorID, ChildID, "
sql = sql & "DatePurchased, Custom1, Custom2, Custom3, Custom4,

QntyOnHand, "
sql = sql & "Description, SellingPrice, QntySold, SalesInvNumber,
Sold ) "
sql = sql & "VALUES('" & rst(1) & "'," & recid & ",'" & rst(3) &

"',"
sql = sql & "'" & rst(4) & "','" & rst(5) & "','" & rst(6) & "','"
& rst(7) & "',"
sql = sql & "0,'" & rst(9) & "','" & rst(10) & "','" & qnty & "'," sql = sql & "'" & invnum & "'," & True & ");"

db.Execute sql

'----Update old record, items left after last sell--------------

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = (('" & rst(8) & "') - (" &
qnty & ")), "
sql = sql & "tblInventory.QntySold = 0, "
sql = sql & "tblInventory.SalesInvNumber = 0, "
sql = sql & "tblInventory.Sold = " & False & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

db.Execute sql
rst.Close
db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True Then Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True
ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False

Then
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

GoTo Exit_Sold_AfterUpdate

End If

Exit_Sold_AfterUpdate:
Exit Sub

Err_Sold_AfterUpdate:
MsgBox Err.Description
Resume Exit_Sold_AfterUpdate

End Sub

---------------------------------------------------------------
After I followed the example to add transactions:--------------
---------------------------------------------------------------
Private Sub Sold_AfterUpdate()
On Error GoTo Err_Sold_AfterUpdate

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim bIntrans As Boolean
Dim sql As String
Dim recid As Long
Dim invnum As String
Dim qnty As Long
Dim child As Long

Set ws = DBEngine(0)
ws.BeginTrans
bIntrans = True
Set db = OpenDatabase("C:\BMS\adsi.mdb")

'-----------------False-------------------------
If Me.Sold = False Then

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold]
child = Forms![frmInventory]![frmSubInventory].Form![ChildID]

'-----Find QntyOnHand in parent record----------

sql = ""
sql = sql & "SELECT tblInventory.QntyOnHand FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"

Parent![txtInvNum].SetFocus
'Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'--------Add qnty sold back to parent record----------

qnty = qnty + rst(0)

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = " & qnty & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"

db.Execute sql

'----------------Delete child record------------------

sql = ""
sql = sql & "DELETE tblInventory.* FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

db.Execute sql

rst.Close
'db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True Then Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True
ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = FalseThen
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

ws.CommitTrans
bIntrans = False
GoTo Exit_Sold_AfterUpdate

'-----------------True----------------------

ElseIf Me.Sold = True Then

'------Create recordset--------------------

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
invnum = Forms![frmInventory].txtInvNum
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold]

sql = ""
sql = sql & "SELECT * FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

Parent![txtInvNum].SetFocus
'Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'-------Check if recordset empty--------

If rst.BOF = True And rst.EOF = True Then
rst.Close
'db.Close
GoTo Exit_Sold_AfterUpdate
End If

'------Append new record, stuff just sold------------

sql = ""
sql = sql & "INSERT INTO tblInventory ( VendorID, ChildID, "
sql = sql & "DatePurchased, Custom1, Custom2, Custom3, Custom4,
QntyOnHand, "
sql = sql & "Description, SellingPrice, QntySold, SalesInvNumber,Sold ) "
sql = sql & "VALUES('" & rst(1) & "'," & recid & ",'" & rst(3) &
"',"
sql = sql & "'" & rst(4) & "','" & rst(5) & "','" & rst(6) & "','"& rst(7) & "',"
sql = sql & "0,'" & rst(9) & "','" & rst(10) & "','" & qnty & "'," sql = sql & "'" & invnum & "'," & True & ");"
'
db.Execute sql

'----Update old record, items left after last sell--------------

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = (('" & rst(8) & "') - (" &qnty & ")), "
sql = sql & "tblInventory.QntySold = 0, "
sql = sql & "tblInventory.SalesInvNumber = 0, "
sql = sql & "tblInventory.Sold = " & False & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"

db.Execute sql
rst.Close
'db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True Then Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True
ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = FalseThen
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

ws.CommitTrans
bIntrans = False
GoTo Exit_Sold_AfterUpdate

End If

Exit_Sold_AfterUpdate:

On Error Resume Next

If bIntrans = True Then
ws.Rollback
End If

Set db = Nothing
Set ws = Nothing

Exit Sub

Err_Sold_AfterUpdate:
MsgBox Err.Description, vbExclamation, "Archiving Failed: Error " &Err.number
Resume Exit_Sold_AfterUpdate

End Sub


Nov 13 '05 #3

P: n/a
TD
OK I found something I left out of the code per the example I was
using.
It is db.Excute sql, dbFailOnError, I left out the dbFailOnError
before. Now I get the error 3218 cannot update, locked record. Any
ideas? I have the database options set to record level locking.

TD wrote:
I made the changes you suggested and code still not working correctly. If the Me.Sold is true then it adds the new record but does not update the origianl record. This worked before transactions but not now.
Also when Me.Sold is false it runs the code for the true part instead
of the section for false.
I am always willing to learn to program better so please tell what
bothers you about the structure.
Steve Jorgensen wrote:
First, let me compliment you on your very legible code (I could nit pick
structure, of course because I'm picky, but...)

Now, I don't know if this is your problem, but I would -never- try to use a
transaction in code using the default workspace because of
potential side
effects - both side effects of other code on your transaction and
of your
transaction on other code. Simply changing "Set ws = DBEngine(0)"
to "Set ws
= DefaultWorkspaceClone" will fix that.

Another issue is that you're not using the ws reference to open the database.
That should be OK if you are using the default worskpace (which I'm

saying to
stop doing), but it looks suspicious to me - better to be explicit,

and change
Set db = OpenDatabase("C:\BMS\adsi.mdb") to Set db =
ws.OpenDatabase("C:\BMS\adsi.mdb").

On 7 Feb 2005 09:51:44 -0800, "TD" <dl**@adsi-sc.com> wrote:
I am trying to add transactions to my code. The original code

workedfine until I followed an example to setup transactions, now the codedoes strange things, but no error messages.

Could someone please review my before and after code and tell me theproper way to add transactions to my code?

Thanks,
TD

------------------------------------------------------
Before transactions added:----------------------------
------------------------------------------------------
Private Sub Sold_AfterUpdate()
On Error GoTo Err_Sold_AfterUpdate

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim recid As Long
Dim invnum As String
Dim qnty As Long
Dim child As Long

'-----------------False-------------------------
If Me.Sold = False Then

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold] child = Forms![frmInventory]![frmSubInventory].Form![ChildID]

'-----Find QntyOnHand in parent record----------

sql = ""
sql = sql & "SELECT tblInventory.QntyOnHand FROM tblInventory " sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"
Parent![txtInvNum].SetFocus
Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'--------Add qnty sold back to parent record----------

qnty = qnty + rst(0)

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = " & qnty & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"
db.Execute sql

'----------------Delete child record------------------

sql = ""
sql = sql & "DELETE tblInventory.* FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"
db.Execute sql

rst.Close
db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True Then Forms![frmInventory]![frmSubInventory].Form.FilterOn = False Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False

Then
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

GoTo Exit_Sold_AfterUpdate

'-----------------True----------------------

ElseIf Me.Sold = True Then

'------Create recordset--------------------

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
invnum = Forms![frmInventory].txtInvNum
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold]
sql = ""
sql = sql & "SELECT * FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"
Parent![txtInvNum].SetFocus
Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'-------Check if recordset empty--------

If rst.BOF = True And rst.EOF = True Then
rst.Close
db.Close
GoTo Exit_Sold_AfterUpdate
End If

'------Append new record, stuff just sold------------

sql = ""
sql = sql & "INSERT INTO tblInventory ( VendorID, ChildID, "
sql = sql & "DatePurchased, Custom1, Custom2, Custom3, Custom4,
QntyOnHand, "
sql = sql & "Description, SellingPrice, QntySold, SalesInvNumber,
Sold ) "
sql = sql & "VALUES('" & rst(1) & "'," & recid & ",'" & rst(3) &
"',"
sql = sql & "'" & rst(4) & "','" & rst(5) & "','" & rst(6) & "','"
& rst(7) & "',"
sql = sql & "0,'" & rst(9) & "','" & rst(10) & "','" & qnty & "'," sql = sql & "'" & invnum & "'," & True & ");"

db.Execute sql

'----Update old record, items left after last sell--------------

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = (('" & rst(8) & "') - (" &

qnty & ")), "
sql = sql & "tblInventory.QntySold = 0, "
sql = sql & "tblInventory.SalesInvNumber = 0, "
sql = sql & "tblInventory.Sold = " & False & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid &
"));"
db.Execute sql
rst.Close
db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True
Then Forms![frmInventory]![frmSubInventory].Form.FilterOn = False Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False

Then
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

GoTo Exit_Sold_AfterUpdate

End If

Exit_Sold_AfterUpdate:
Exit Sub

Err_Sold_AfterUpdate:
MsgBox Err.Description
Resume Exit_Sold_AfterUpdate

End Sub

---------------------------------------------------------------
After I followed the example to add transactions:--------------
---------------------------------------------------------------
Private Sub Sold_AfterUpdate()
On Error GoTo Err_Sold_AfterUpdate

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim bIntrans As Boolean
Dim sql As String
Dim recid As Long
Dim invnum As String
Dim qnty As Long
Dim child As Long

Set ws = DBEngine(0)
ws.BeginTrans
bIntrans = True
Set db = OpenDatabase("C:\BMS\adsi.mdb")

'-----------------False-------------------------
If Me.Sold = False Then

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold] child = Forms![frmInventory]![frmSubInventory].Form![ChildID]

'-----Find QntyOnHand in parent record----------

sql = ""
sql = sql & "SELECT tblInventory.QntyOnHand FROM tblInventory " sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"
Parent![txtInvNum].SetFocus
'Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'--------Add qnty sold back to parent record----------

qnty = qnty + rst(0)

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = " & qnty & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & child & "));"
db.Execute sql

'----------------Delete child record------------------

sql = ""
sql = sql & "DELETE tblInventory.* FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"
db.Execute sql

rst.Close
'db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True Then Forms![frmInventory]![frmSubInventory].Form.FilterOn = False Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = FalseThen
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

ws.CommitTrans
bIntrans = False
GoTo Exit_Sold_AfterUpdate

'-----------------True----------------------

ElseIf Me.Sold = True Then

'------Create recordset--------------------

recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
invnum = Forms![frmInventory].txtInvNum
qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty. Sold]
sql = ""
sql = sql & "SELECT * FROM tblInventory "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid & "));"
Parent![txtInvNum].SetFocus
'Set db = OpenDatabase("C:\BMS\adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenDynaset)

'-------Check if recordset empty--------

If rst.BOF = True And rst.EOF = True Then
rst.Close
'db.Close
GoTo Exit_Sold_AfterUpdate
End If

'------Append new record, stuff just sold------------

sql = ""
sql = sql & "INSERT INTO tblInventory ( VendorID, ChildID, "
sql = sql & "DatePurchased, Custom1, Custom2, Custom3, Custom4,QntyOnHand, "
sql = sql & "Description, SellingPrice, QntySold, SalesInvNumber,Sold ) "
sql = sql & "VALUES('" & rst(1) & "'," & recid & ",'" & rst(3) &"',"
sql = sql & "'" & rst(4) & "','" & rst(5) & "','" & rst(6) & "','"& rst(7) & "',"
sql = sql & "0,'" & rst(9) & "','" & rst(10) & "','" & qnty & "'," sql = sql & "'" & invnum & "'," & True & ");"
'
db.Execute sql

'----Update old record, items left after last sell--------------

sql = ""
sql = sql & "UPDATE tblInventory SET "
sql = sql & "tblInventory.QntyOnHand = (('" & rst(8) & "') - (" &
qnty & ")), "
sql = sql & "tblInventory.QntySold = 0, "
sql = sql & "tblInventory.SalesInvNumber = 0, "
sql = sql & "tblInventory.Sold = " & False & " "
sql = sql & "WHERE (((tblInventory.RecordID)= " & recid &
"));"
db.Execute sql
rst.Close
'db.Close

If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True
Then Forms![frmInventory]![frmSubInventory].Form.FilterOn = False Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Form.FilterOn = True ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = FalseThen
Forms![frmInventory]![frmSubInventory].Requery
Forms![frmInventory]![frmSubInventory].Requery
End If

ws.CommitTrans
bIntrans = False
GoTo Exit_Sold_AfterUpdate

End If

Exit_Sold_AfterUpdate:

On Error Resume Next

If bIntrans = True Then
ws.Rollback
End If

Set db = Nothing
Set ws = Nothing

Exit Sub

Err_Sold_AfterUpdate:
MsgBox Err.Description, vbExclamation, "Archiving Failed:
Error " &Err.number
Resume Exit_Sold_AfterUpdate

End Sub


Nov 13 '05 #4

P: n/a
Well, I just realized you are trying to work with the form while the
transaction is in effect. Form activity can never be managed by a code-based
transaction, and locks in the transaction can conflict with locking by the
form. Also, the most appropriate locking is usually "No Locks", not
record-level.

When you set locking to none, Access only holds physical locks on records for
a brief moment while actually writing data, and uses optimistic locking to
handle contention. With optimistic locking, if 2 users edit the same record
at the same time, the first save wins, and the second user is informed that
the data was changed by another user. In most apps, this happens pretty
rarely.

Anyway, if you want to use form data in a transaction, you pretty much have to
use an unbound form, and do everything from code including loading data into
the form, and writing data back to the table from the form.

On 7 Feb 2005 16:21:34 -0800, "TD" <dl**@adsi-sc.com> wrote:
OK I found something I left out of the code per the example I was
using.
It is db.Excute sql, dbFailOnError, I left out the dbFailOnError
before. Now I get the error 3218 cannot update, locked record. Any
ideas? I have the database options set to record level locking.

TD wrote:
I made the changes you suggested and code still not working

correctly.
If the Me.Sold is true then it adds the new record but does not

update
the origianl record. This worked before transactions but not now.
Also when Me.Sold is false it runs the code for the true part instead
of the section for false.
I am always willing to learn to program better so please tell what
bothers you about the structure.
Steve Jorgensen wrote:
> First, let me compliment you on your very legible code (I could nit

pick
> structure, of course because I'm picky, but...)
>
> Now, I don't know if this is your problem, but I would -never- tryto
use a
> transaction in code using the default workspace because of

potential
side
> effects - both side effects of other code on your transaction and

of
your
> transaction on other code. Simply changing "Set ws = DBEngine(0)"

to
"Set ws
> = DefaultWorkspaceClone" will fix that.
>
> Another issue is that you're not using the ws reference to open the

database.
> That should be OK if you are using the default worskpace (which I'm

saying to
> stop doing), but it looks suspicious to me - better to be explicit,

and change
> Set db = OpenDatabase("C:\BMS\adsi.mdb") to Set db =
> ws.OpenDatabase("C:\BMS\adsi.mdb").
>
> On 7 Feb 2005 09:51:44 -0800, "TD" <dl**@adsi-sc.com> wrote:
>
> >I am trying to add transactions to my code. The original code

worked
> >fine until I followed an example to setup transactions, now the

code > >does strange things, but no error messages.
> >
> >Could someone please review my before and after code and tell methe > >proper way to add transactions to my code?
> >
> >Thanks,
> >TD
> >
> >------------------------------------------------------
> >Before transactions added:----------------------------
> >------------------------------------------------------
> >Private Sub Sold_AfterUpdate()
> >On Error GoTo Err_Sold_AfterUpdate
> >
> >Dim db As DAO.Database
> >Dim rst As DAO.Recordset
> >Dim sql As String
> >Dim recid As Long
> >Dim invnum As String
> >Dim qnty As Long
> >Dim child As Long
> >
> >'-----------------False-------------------------
> >If Me.Sold = False Then
> >
> > recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
> > qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty.Sold] > > child = Forms![frmInventory]![frmSubInventory].Form![ChildID]
> >
> >'-----Find QntyOnHand in parent record----------
> >
> > sql = ""
> > sql = sql & "SELECT tblInventory.QntyOnHand FROM tblInventory" > > sql = sql & "WHERE (((tblInventory.RecordID)= " & child &"));" > >
> > Parent![txtInvNum].SetFocus
> > Set db = OpenDatabase("C:\BMS\adsi.mdb")
> > Set rst = db.OpenRecordset(sql, dbOpenDynaset)
> >
> >'--------Add qnty sold back to parent record----------
> >
> > qnty = qnty + rst(0)
> >
> > sql = ""
> > sql = sql & "UPDATE tblInventory SET "
> > sql = sql & "tblInventory.QntyOnHand = " & qnty & " "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & child &"));" > >
> > db.Execute sql
> >
> >'----------------Delete child record------------------
> >
> > sql = ""
> > sql = sql & "DELETE tblInventory.* FROM tblInventory "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & recid &"));" > >
> > db.Execute sql
> >
> > rst.Close
> > db.Close
> >
> > If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True

Then
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =False > > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =True > > ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn =

False
> >
> >
> >Then
> > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Requery
> > End If
> >
> > GoTo Exit_Sold_AfterUpdate
> >
> >'-----------------True----------------------
> >
> >ElseIf Me.Sold = True Then
> >
> >'------Create recordset--------------------
> >
> > recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
> > invnum = Forms![frmInventory].txtInvNum
> > qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty.Sold] > >
> > sql = ""
> > sql = sql & "SELECT * FROM tblInventory "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & recid &"));" > >
> > Parent![txtInvNum].SetFocus
> > Set db = OpenDatabase("C:\BMS\adsi.mdb")
> > Set rst = db.OpenRecordset(sql, dbOpenDynaset)
> >
> > '-------Check if recordset empty--------
> >
> > If rst.BOF = True And rst.EOF = True Then
> > rst.Close
> > db.Close
> > GoTo Exit_Sold_AfterUpdate
> > End If
> >
> >'------Append new record, stuff just sold------------
> >
> > sql = ""
> > sql = sql & "INSERT INTO tblInventory ( VendorID, ChildID, "
> > sql = sql & "DatePurchased, Custom1, Custom2, Custom3,Custom4, > >
> >QntyOnHand, "
> > sql = sql & "Description, SellingPrice, QntySold,

SalesInvNumber,
> >
> >Sold ) "
> > sql = sql & "VALUES('" & rst(1) & "'," & recid & ",'" & rst(3)& > >
> >"',"
> > sql = sql & "'" & rst(4) & "','" & rst(5) & "','" & rst(6) &

"','"
> >
> >& rst(7) & "',"
> > sql = sql & "0,'" & rst(9) & "','" & rst(10) & "','" & qnty &

"',"
> > sql = sql & "'" & invnum & "'," & True & ");"
> >
> > db.Execute sql
> >
> >'----Update old record, items left after last sell--------------
> >
> > sql = ""
> > sql = sql & "UPDATE tblInventory SET "
> > sql = sql & "tblInventory.QntyOnHand = (('" & rst(8) & "') -("
&
> >
> >qnty & ")), "
> > sql = sql & "tblInventory.QntySold = 0, "
> > sql = sql & "tblInventory.SalesInvNumber = 0, "
> > sql = sql & "tblInventory.Sold = " & False & " "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & recid &

"));" > >
> > db.Execute sql
> > rst.Close
> > db.Close
> >
> > If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True

Then
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =False > > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =True > > ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn =

False
> >
> >
> >Then
> > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Requery
> > End If
> >
> > GoTo Exit_Sold_AfterUpdate
> >
> >End If
> >
> >Exit_Sold_AfterUpdate:
> > Exit Sub
> >
> >Err_Sold_AfterUpdate:
> > MsgBox Err.Description
> > Resume Exit_Sold_AfterUpdate
> >
> >End Sub
> >
> >---------------------------------------------------------------
> >After I followed the example to add transactions:--------------
> >---------------------------------------------------------------
> >Private Sub Sold_AfterUpdate()
> >On Error GoTo Err_Sold_AfterUpdate
> >
> >Dim ws As DAO.Workspace
> >Dim db As DAO.Database
> >Dim rst As DAO.Recordset
> >Dim bIntrans As Boolean
> >Dim sql As String
> >Dim recid As Long
> >Dim invnum As String
> >Dim qnty As Long
> >Dim child As Long
> >
> >Set ws = DBEngine(0)
> >ws.BeginTrans
> >bIntrans = True
> >Set db = OpenDatabase("C:\BMS\adsi.mdb")
> >
> >'-----------------False-------------------------
> >If Me.Sold = False Then
> >
> > recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
> > qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty.Sold] > > child = Forms![frmInventory]![frmSubInventory].Form![ChildID]
> >
> >'-----Find QntyOnHand in parent record----------
> >
> > sql = ""
> > sql = sql & "SELECT tblInventory.QntyOnHand FROM tblInventory" > > sql = sql & "WHERE (((tblInventory.RecordID)= " & child &"));" > >
> > Parent![txtInvNum].SetFocus
> > 'Set db = OpenDatabase("C:\BMS\adsi.mdb")
> > Set rst = db.OpenRecordset(sql, dbOpenDynaset)
> >
> >'--------Add qnty sold back to parent record----------
> >
> > qnty = qnty + rst(0)
> >
> > sql = ""
> > sql = sql & "UPDATE tblInventory SET "
> > sql = sql & "tblInventory.QntyOnHand = " & qnty & " "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & child &"));" > >
> > db.Execute sql
> >
> >'----------------Delete child record------------------
> >
> > sql = ""
> > sql = sql & "DELETE tblInventory.* FROM tblInventory "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & recid &"));" > >
> > db.Execute sql
> >
> > rst.Close
> > 'db.Close
> >
> > If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True

Then
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =False > > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =True > > ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn =

False
> >Then
> > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Requery
> > End If
> >
> > ws.CommitTrans
> > bIntrans = False
> > GoTo Exit_Sold_AfterUpdate
> >
> >'-----------------True----------------------
> >
> >ElseIf Me.Sold = True Then
> >
> >'------Create recordset--------------------
> >
> > recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID]
> > invnum = Forms![frmInventory].txtInvNum
> > qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty.Sold] > >
> > sql = ""
> > sql = sql & "SELECT * FROM tblInventory "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & recid &"));" > >
> > Parent![txtInvNum].SetFocus
> > 'Set db = OpenDatabase("C:\BMS\adsi.mdb")
> > Set rst = db.OpenRecordset(sql, dbOpenDynaset)
> >
> > '-------Check if recordset empty--------
> >
> > If rst.BOF = True And rst.EOF = True Then
> > rst.Close
> > 'db.Close
> > GoTo Exit_Sold_AfterUpdate
> > End If
> >
> >'------Append new record, stuff just sold------------
> >
> > sql = ""
> > sql = sql & "INSERT INTO tblInventory ( VendorID, ChildID, "
> > sql = sql & "DatePurchased, Custom1, Custom2, Custom3,Custom4, > >QntyOnHand, "
> > sql = sql & "Description, SellingPrice, QntySold,

SalesInvNumber,
> >Sold ) "
> > sql = sql & "VALUES('" & rst(1) & "'," & recid & ",'" & rst(3)& > >"',"
> > sql = sql & "'" & rst(4) & "','" & rst(5) & "','" & rst(6) &

"','"
> >& rst(7) & "',"
> > sql = sql & "0,'" & rst(9) & "','" & rst(10) & "','" & qnty &

"',"
> > sql = sql & "'" & invnum & "'," & True & ");"
> > '
> > db.Execute sql
> >
> >'----Update old record, items left after last sell--------------
> >
> > sql = ""
> > sql = sql & "UPDATE tblInventory SET "
> > sql = sql & "tblInventory.QntyOnHand = (('" & rst(8) & "') -("
&
> >qnty & ")), "
> > sql = sql & "tblInventory.QntySold = 0, "
> > sql = sql & "tblInventory.SalesInvNumber = 0, "
> > sql = sql & "tblInventory.Sold = " & False & " "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & recid &

"));" > >
> > db.Execute sql
> > rst.Close
> > 'db.Close
> >
> > If Forms![frmInventory]![frmSubInventory].Form.FilterOn = True

Then
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =False > > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =True > > ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn =

False
> >Then
> > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Requery
> > End If
> >
> > ws.CommitTrans
> > bIntrans = False
> > GoTo Exit_Sold_AfterUpdate
> >
> >End If
> >
> >Exit_Sold_AfterUpdate:
> >
> > On Error Resume Next
> >
> > If bIntrans = True Then
> > ws.Rollback
> > End If
> >
> > Set db = Nothing
> > Set ws = Nothing
> >
> > Exit Sub
> >
> >Err_Sold_AfterUpdate:
> > MsgBox Err.Description, vbExclamation, "Archiving Failed:

Error
" &
> >Err.number
> > Resume Exit_Sold_AfterUpdate
> >
> >End Sub


Nov 13 '05 #5

P: n/a
TD
Thanks steve for helping me out on this. I don't have time to re-code
to use an unbound form at this time so I'll abandon transactions for
this project but your help is much appreciated!

Thanks,
TD
Steve Jorgensen wrote:
Well, I just realized you are trying to work with the form while the
transaction is in effect. Form activity can never be managed by a code-based transaction, and locks in the transaction can conflict with locking by the form. Also, the most appropriate locking is usually "No Locks", not
record-level.

When you set locking to none, Access only holds physical locks on records for a brief moment while actually writing data, and uses optimistic locking to handle contention. With optimistic locking, if 2 users edit the same record at the same time, the first save wins, and the second user is informed that the data was changed by another user. In most apps, this happens pretty rarely.

Anyway, if you want to use form data in a transaction, you pretty much have to use an unbound form, and do everything from code including loading data into the form, and writing data back to the table from the form.

On 7 Feb 2005 16:21:34 -0800, "TD" <dl**@adsi-sc.com> wrote:
OK I found something I left out of the code per the example I was
using.
It is db.Excute sql, dbFailOnError, I left out the dbFailOnError
before. Now I get the error 3218 cannot update, locked record. Any
ideas? I have the database options set to record level locking.

TD wrote:
I made the changes you suggested and code still not working

correctly.
If the Me.Sold is true then it adds the new record but does not

update
the origianl record. This worked before transactions but not now.
Also when Me.Sold is false it runs the code for the true part instead of the section for false.
I am always willing to learn to program better so please tell what bothers you about the structure.
Steve Jorgensen wrote:
> First, let me compliment you on your very legible code (I could nit pick
> structure, of course because I'm picky, but...)
>
> Now, I don't know if this is your problem, but I would -never- try
to
use a
> transaction in code using the default workspace because of

potential
side
> effects - both side effects of other code on your transaction
andof
your
> transaction on other code. Simply changing "Set ws =
DBEngine(0)"to
"Set ws
> = DefaultWorkspaceClone" will fix that.
>
> Another issue is that you're not using the ws reference to open
the database.
> That should be OK if you are using the default worskpace (which I'm saying to
> stop doing), but it looks suspicious to me - better to be explicit, and change
> Set db = OpenDatabase("C:\BMS\adsi.mdb") to Set db =
> ws.OpenDatabase("C:\BMS\adsi.mdb").
>
> On 7 Feb 2005 09:51:44 -0800, "TD" <dl**@adsi-sc.com> wrote:
>
> >I am trying to add transactions to my code. The original code
worked
> >fine until I followed an example to setup transactions, now the

code
> >does strange things, but no error messages.
> >
> >Could someone please review my before and after code and tell methe
> >proper way to add transactions to my code?
> >
> >Thanks,
> >TD
> >
> >------------------------------------------------------
> >Before transactions added:----------------------------
> >------------------------------------------------------
> >Private Sub Sold_AfterUpdate()
> >On Error GoTo Err_Sold_AfterUpdate
> >
> >Dim db As DAO.Database
> >Dim rst As DAO.Recordset
> >Dim sql As String
> >Dim recid As Long
> >Dim invnum As String
> >Dim qnty As Long
> >Dim child As Long
> >
> >'-----------------False-------------------------
> >If Me.Sold = False Then
> >
> > recid =
Forms![frmInventory]![frmSubInventory].Form![InvRecID] > > qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty.

Sold]
> > child = Forms![frmInventory]![frmSubInventory].Form![ChildID] > >
> >'-----Find QntyOnHand in parent record----------
> >
> > sql = ""
> > sql = sql & "SELECT tblInventory.QntyOnHand FROM tblInventory"
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & child &

"));"
> >
> > Parent![txtInvNum].SetFocus
> > Set db = OpenDatabase("C:\BMS\adsi.mdb")
> > Set rst = db.OpenRecordset(sql, dbOpenDynaset)
> >
> >'--------Add qnty sold back to parent record----------
> >
> > qnty = qnty + rst(0)
> >
> > sql = ""
> > sql = sql & "UPDATE tblInventory SET "
> > sql = sql & "tblInventory.QntyOnHand = " & qnty & " "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & child &

"));"
> >
> > db.Execute sql
> >
> >'----------------Delete child record------------------
> >
> > sql = ""
> > sql = sql & "DELETE tblInventory.* FROM tblInventory "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & recid &

"));"
> >
> > db.Execute sql
> >
> > rst.Close
> > db.Close
> >
> > If Forms![frmInventory]![frmSubInventory].Form.FilterOn =
True Then
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =

False
> > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =

True
> > ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
> >
> >
> >Then
> > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Requery
> > End If
> >
> > GoTo Exit_Sold_AfterUpdate
> >
> >'-----------------True----------------------
> >
> >ElseIf Me.Sold = True Then
> >
> >'------Create recordset--------------------
> >
> > recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID] > > invnum = Forms![frmInventory].txtInvNum
> > qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty.

Sold]
> >
> > sql = ""
> > sql = sql & "SELECT * FROM tblInventory "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & recid &

"));"
> >
> > Parent![txtInvNum].SetFocus
> > Set db = OpenDatabase("C:\BMS\adsi.mdb")
> > Set rst = db.OpenRecordset(sql, dbOpenDynaset)
> >
> > '-------Check if recordset empty--------
> >
> > If rst.BOF = True And rst.EOF = True Then
> > rst.Close
> > db.Close
> > GoTo Exit_Sold_AfterUpdate
> > End If
> >
> >'------Append new record, stuff just sold------------
> >
> > sql = ""
> > sql = sql & "INSERT INTO tblInventory ( VendorID, ChildID, " > > sql = sql & "DatePurchased, Custom1, Custom2, Custom3,

Custom4,
> >
> >QntyOnHand, "
> > sql = sql & "Description, SellingPrice, QntySold,
SalesInvNumber,
> >
> >Sold ) "
> > sql = sql & "VALUES('" & rst(1) & "'," & recid & ",'" & rst(3)&
> >
> >"',"
> > sql = sql & "'" & rst(4) & "','" & rst(5) & "','" & rst(6)
& "','"
> >
> >& rst(7) & "',"
> > sql = sql & "0,'" & rst(9) & "','" & rst(10) & "','" & qnty & "',"
> > sql = sql & "'" & invnum & "'," & True & ");"
> >
> > db.Execute sql
> >
> >'----Update old record, items left after last sell-------------- > >
> > sql = ""
> > sql = sql & "UPDATE tblInventory SET "
> > sql = sql & "tblInventory.QntyOnHand = (('" & rst(8) & "') -("
&
> >
> >qnty & ")), "
> > sql = sql & "tblInventory.QntySold = 0, "
> > sql = sql & "tblInventory.SalesInvNumber = 0, "
> > sql = sql & "tblInventory.Sold = " & False & " "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & recid &

"));"
> >
> > db.Execute sql
> > rst.Close
> > db.Close
> >
> > If Forms![frmInventory]![frmSubInventory].Form.FilterOn =
True Then
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =

False
> > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =

True
> > ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
> >
> >
> >Then
> > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Requery
> > End If
> >
> > GoTo Exit_Sold_AfterUpdate
> >
> >End If
> >
> >Exit_Sold_AfterUpdate:
> > Exit Sub
> >
> >Err_Sold_AfterUpdate:
> > MsgBox Err.Description
> > Resume Exit_Sold_AfterUpdate
> >
> >End Sub
> >
> >---------------------------------------------------------------
> >After I followed the example to add transactions:--------------
> >---------------------------------------------------------------
> >Private Sub Sold_AfterUpdate()
> >On Error GoTo Err_Sold_AfterUpdate
> >
> >Dim ws As DAO.Workspace
> >Dim db As DAO.Database
> >Dim rst As DAO.Recordset
> >Dim bIntrans As Boolean
> >Dim sql As String
> >Dim recid As Long
> >Dim invnum As String
> >Dim qnty As Long
> >Dim child As Long
> >
> >Set ws = DBEngine(0)
> >ws.BeginTrans
> >bIntrans = True
> >Set db = OpenDatabase("C:\BMS\adsi.mdb")
> >
> >'-----------------False-------------------------
> >If Me.Sold = False Then
> >
> > recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID] > > qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty.

Sold]
> > child = Forms![frmInventory]![frmSubInventory].Form![ChildID] > >
> >'-----Find QntyOnHand in parent record----------
> >
> > sql = ""
> > sql = sql & "SELECT tblInventory.QntyOnHand FROM tblInventory"
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & child &

"));"
> >
> > Parent![txtInvNum].SetFocus
> > 'Set db = OpenDatabase("C:\BMS\adsi.mdb")
> > Set rst = db.OpenRecordset(sql, dbOpenDynaset)
> >
> >'--------Add qnty sold back to parent record----------
> >
> > qnty = qnty + rst(0)
> >
> > sql = ""
> > sql = sql & "UPDATE tblInventory SET "
> > sql = sql & "tblInventory.QntyOnHand = " & qnty & " "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & child &

"));"
> >
> > db.Execute sql
> >
> >'----------------Delete child record------------------
> >
> > sql = ""
> > sql = sql & "DELETE tblInventory.* FROM tblInventory "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & recid &

"));"
> >
> > db.Execute sql
> >
> > rst.Close
> > 'db.Close
> >
> > If Forms![frmInventory]![frmSubInventory].Form.FilterOn =
True Then
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =

False
> > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =

True
> > ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
> >Then
> > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Requery
> > End If
> >
> > ws.CommitTrans
> > bIntrans = False
> > GoTo Exit_Sold_AfterUpdate
> >
> >'-----------------True----------------------
> >
> >ElseIf Me.Sold = True Then
> >
> >'------Create recordset--------------------
> >
> > recid = Forms![frmInventory]![frmSubInventory].Form![InvRecID] > > invnum = Forms![frmInventory].txtInvNum
> > qnty = Forms![frmInventory]![frmSubInventory].Form![Qnty.

Sold]
> >
> > sql = ""
> > sql = sql & "SELECT * FROM tblInventory "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & recid &

"));"
> >
> > Parent![txtInvNum].SetFocus
> > 'Set db = OpenDatabase("C:\BMS\adsi.mdb")
> > Set rst = db.OpenRecordset(sql, dbOpenDynaset)
> >
> > '-------Check if recordset empty--------
> >
> > If rst.BOF = True And rst.EOF = True Then
> > rst.Close
> > 'db.Close
> > GoTo Exit_Sold_AfterUpdate
> > End If
> >
> >'------Append new record, stuff just sold------------
> >
> > sql = ""
> > sql = sql & "INSERT INTO tblInventory ( VendorID, ChildID, " > > sql = sql & "DatePurchased, Custom1, Custom2, Custom3,

Custom4,
> >QntyOnHand, "
> > sql = sql & "Description, SellingPrice, QntySold,
SalesInvNumber,
> >Sold ) "
> > sql = sql & "VALUES('" & rst(1) & "'," & recid & ",'" & rst(3)&
> >"',"
> > sql = sql & "'" & rst(4) & "','" & rst(5) & "','" & rst(6)
& "','"
> >& rst(7) & "',"
> > sql = sql & "0,'" & rst(9) & "','" & rst(10) & "','" & qnty & "',"
> > sql = sql & "'" & invnum & "'," & True & ");"
> > '
> > db.Execute sql
> >
> >'----Update old record, items left after last sell-------------- > >
> > sql = ""
> > sql = sql & "UPDATE tblInventory SET "
> > sql = sql & "tblInventory.QntyOnHand = (('" & rst(8) & "') -("
&
> >qnty & ")), "
> > sql = sql & "tblInventory.QntySold = 0, "
> > sql = sql & "tblInventory.SalesInvNumber = 0, "
> > sql = sql & "tblInventory.Sold = " & False & " "
> > sql = sql & "WHERE (((tblInventory.RecordID)= " & recid &

"));"
> >
> > db.Execute sql
> > rst.Close
> > 'db.Close
> >
> > If Forms![frmInventory]![frmSubInventory].Form.FilterOn =

True Then
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =

False
> > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Form.FilterOn =

True
> > ElseIf Forms![frmInventory]![frmSubInventory].Form.FilterOn = False
> >Then
> > Forms![frmInventory]![frmSubInventory].Requery
> > Forms![frmInventory]![frmSubInventory].Requery
> > End If
> >
> > ws.CommitTrans
> > bIntrans = False
> > GoTo Exit_Sold_AfterUpdate
> >
> >End If
> >
> >Exit_Sold_AfterUpdate:
> >
> > On Error Resume Next
> >
> > If bIntrans = True Then
> > ws.Rollback
> > End If
> >
> > Set db = Nothing
> > Set ws = Nothing
> >
> > Exit Sub
> >
> >Err_Sold_AfterUpdate:
> > MsgBox Err.Description, vbExclamation, "Archiving Failed:

Error
" &
> >Err.number
> > Resume Exit_Sold_AfterUpdate
> >
> >End Sub


Nov 13 '05 #6

P: n/a
Steve Jorgensen wrote:
Well, I just realized you are trying to work with the form while the
transaction is in effect. Form activity can never be managed by a code-based
transaction, ....


I can't seem to find the first of this thread so I'm not sure if this is
pertinent, but in a simple situation, it seems to work. Is it form
activity managed by a code-based transaction?

Option Explicit

Dim c As ADODB.Connection

Private Sub Form_Close()
With c
If MsgBox("Save Changes?", vbYesNo Or vbQuestion) = vbYes Then
.CommitTrans
Else
.RollbackTrans
End If
End With
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim r As ADODB.Recordset

Set c = CurrentProject.Connection
c.BeginTrans

Set r = New ADODB.Recordset

With r
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.CursorType = adOpenKeyset
.ActiveConnection = c
.Open "SELECT * FROM Table1"
End With

Set Me.Recordset = r
End Sub
Nov 13 '05 #7

P: n/a
My statement was incomplete - bound form activity can never be managed by a
code-based transaction.

On Tue, 08 Feb 2005 01:29:51 -0500, Lyle Fairfield <ly******@yahoo.ca> wrote:
Steve Jorgensen wrote:
Well, I just realized you are trying to work with the form while the
transaction is in effect. Form activity can never be managed by a code-based
transaction, ....


I can't seem to find the first of this thread so I'm not sure if this is
pertinent, but in a simple situation, it seems to work. Is it form
activity managed by a code-based transaction?

Option Explicit

Dim c As ADODB.Connection

Private Sub Form_Close()
With c
If MsgBox("Save Changes?", vbYesNo Or vbQuestion) = vbYes Then
.CommitTrans
Else
.RollbackTrans
End If
End With
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim r As ADODB.Recordset

Set c = CurrentProject.Connection
c.BeginTrans

Set r = New ADODB.Recordset

With r
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.CursorType = adOpenKeyset
.ActiveConnection = c
.Open "SELECT * FROM Table1"
End With

Set Me.Recordset = r
End Sub


Nov 13 '05 #8

P: n/a
Steve Jorgensen wrote:
My statement was incomplete - bound form activity can never be managed by a
code-based transaction.


There as an implication in MS-Access (2002) help that a form whose
recordset is "SET" is "bound" as ...

**** begin quote ****
Use the Recordset property:

To bind multiple forms to a common data set. This allows synchronization
of multiple forms. For example,
Set Me.Recordset = Forms!Form1.Recordset
To use methods with the Recordset object that aren't directly supported
on forms. For example, you can use the Recordset property with the ADO
Find or DAO Find methods in a custom dialog for finding a record.
To wrap a transaction (which can be rolled back) around a set of edits
that affect multiple forms.
.....
**** end quote ....

As this section also says/implies that only a form recordset (set) based
on SQL (and not JET) is editable, (which seems to be quite wrong; the
example I posted is JET and editable ), perhaps we cannot accept it as
authoritative, of course.
Nov 13 '05 #9

P: n/a
Franksly, I've found that manipulating a recordset in code in any way that is
bound to a form is a great way to crash Access, though I guess it might allow
form activity to be encapsulated in a transaction until Access crashes. I
also imagine, though, that it would only work for a transaction initiated
before beginning an edit in the form, and not committed until after the form
data was saved. That's a transaction that's active for several thousand times
longer than it should be.

On Tue, 08 Feb 2005 03:48:04 -0500, Lyle Fairfield <ly******@yahoo.ca> wrote:
Steve Jorgensen wrote:
My statement was incomplete - bound form activity can never be managed by a
code-based transaction.


There as an implication in MS-Access (2002) help that a form whose
recordset is "SET" is "bound" as ...

**** begin quote ****
Use the Recordset property:

To bind multiple forms to a common data set. This allows synchronization
of multiple forms. For example,
Set Me.Recordset = Forms!Form1.Recordset
To use methods with the Recordset object that aren't directly supported
on forms. For example, you can use the Recordset property with the ADO
Find or DAO Find methods in a custom dialog for finding a record.
To wrap a transaction (which can be rolled back) around a set of edits
that affect multiple forms.
....
**** end quote ....

As this section also says/implies that only a form recordset (set) based
on SQL (and not JET) is editable, (which seems to be quite wrong; the
example I posted is JET and editable ), perhaps we cannot accept it as
authoritative, of course.


Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.