3034 You tried to commit or rollback a transaction

R

RCrawfordBocaRaton

Help!

I have a procedure that has worked for years and yesterday I upgraded all
our machines and databases to Access 2003 and the following error occured in
our Billing procedure.

"3034 You tried to commit or rollback a transaction without first beginning
a transaction"

The code works in Access 2000, I have an unconverted copy on my laptop for
testing. The only diff is the Access versions.

BELOW IS CODE ON BUTTON

Private Sub Print_Button_Click()
'New version written 11/6/96 by P. Salsich
On Error GoTo Print_Button_Click_Err
If Me!POSTED = 0 Then
PostInvoice
Else
MsgBox "This invoice has already been posted. No new posting will be
done.", 48, "Ready to Print"
End If
DoCmd.OpenReport "CONTRACT SALES INVOICE", A_NORMAL, , "[ID] =" & Me!ID
DoCmd.OpenReport "CONTRACT SALES INVOICE", A_NORMAL, , "[ID] =" & Me!ID

Print_Button_Click_Exit:
Exit Sub

Print_Button_Click_Err:
MsgBox Err & " " & Error$, , "Print_Button_Click Error"
Beep
MsgBox "This posting and printing will not be done.", 48, "Rollback"

Resume Print_Button_Click_Exit

End Sub

BELOW IS CODE "PostInvoice"

Sub PostInvoice()
' 1. create new record in td_SYSBatchNumbers
' 2. create new record in invoice headers
' 3. create new record in invoice details
' 4. change appropriate record in [td_SYSPeoplePlacesThings]

On Error GoTo PostInvoice_errhandler

Dim db As Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim rst3 As Recordset
Dim rst4 As Recordset
Dim intBatchnum, intInvoiceKey As Long
Dim wrk2 As Workspace

Set wrk2 = DBEngine.Workspaces(0)
Set db = DBEngine.Workspaces(0).Databases(0)

intBatchnum = DMax("[fknBatchNumber]", "td_SYSBatchNumbers") + 1

wrk2.BeginTrans

Set rst1 = db.OpenRecordset("td_SYSBatchNumbers", DB_OPEN_DYNASET,
DB_DENYREAD)
rst1.AddNew
rst1![fknBatchNumber] = intBatchnum
rst1![dtBatch] = Forms![AR JOB INVOICE]![ARINVDATE]
rst1![fknCompany] = DLookup("[fknCompany]", "td_SYSCompanyProfile")
rst1![sAccessedFrom] = "LCIARJobInvoice"
rst1![cBatchTotal] = Forms![AR JOB INVOICE]![TOTALAMT]

rst1![Discount cAmount] = 0
rst1![cDiscountTaken] = 0

rst1![archived?] = 0
rst1![dtCreated by] = "Admin"
rst1![dtModified] = Forms![AR JOB INVOICE]![ARINVDATE]
rst1![dtModified by] = Null
rst1.Update
rst1.Close



Set rst2 = db.OpenRecordset("td_ARInvoiceHeader", DB_OPEN_DYNASET,
DB_DENYREAD)
rst2.AddNew
rst2![fknCustomer] = Forms![AR JOB INVOICE]![CustKey]
rst2![fknCompany] = DLookup("[fknCompany]", "td_SYSCompanyProfile")
rst2![fknBatchNumber] = intBatchnum
rst2![dtBatch] = Forms![AR JOB INVOICE]![ARINVDATE]
rst2![sInvoiceNumber] = Forms![AR JOB INVOICE]![NUMBER]
rst2![Date] = Forms![AR JOB INVOICE]![ARINVDATE]
rst2![dtProjectedPay] = Forms![AR JOB INVOICE]![ARINVDATE] + 30
rst2![cAmount] = Forms![AR JOB INVOICE]![TOTALAMT]
rst2![fknARGL] = DLookup("[fknARGL]", "td_SYSCompanyProfile")
rst2![Discount cAmount] = 0
rst2![dtDiscount] = Null
rst2![cDiscountTaken?] = 0
rst2![cBalanceDue] = Forms![AR JOB INVOICE]![TOTALAMT]
rst2![dtModified] = Forms![AR JOB INVOICE]![ARINVDATE]
rst2![dtModified by] = "Admin"
rst2![sGeneratedBy] = "LCIARJobInvoice"
rst2![InvType] = "J"
intInvoiceKey = rst2![pkcARInvoice]
rst2.Update
rst2.Close



Set rst3 = db.OpenRecordset("td_ARInvoiceDetail", DB_OPEN_DYNASET,
DB_DENYREAD)
rst3.AddNew
rst3![sInvoiceNumber pkcPayrollTimeHistory] = intInvoiceKey
rst3![nSplitNumber] = 1
rst3![fknCompany] = DLookup("[fknCompany]", "td_SYSCompanyProfile")
rst3![Sales fknDefaultGL] = DLookup("[Sales fknDefaultGL]",
"td_SYSCompanyProfile")
rst3![sReferenceNumber] = Forms![AR JOB INVOICE]![JOBNUMBER]
rst3![Type of Sale] = 11
rst3![Split cAmount] = Forms![AR JOB INVOICE]![TOTALAMT]
rst3![sCreditGLList] = "4100.0.0.0"

rst3.Update
rst3.Close

Set rst4 = db.OpenRecordset("td_SYSPeoplePlacesThings", DB_OPEN_DYNASET,
DB_DENYREAD)
rst4.FindFirst "[pkcPPT] = " & Forms![AR JOB INVOICE]![PPTKey]
rst4.Edit
rst4![ynCustomerInvoicesOutstanding] = True
rst4![nCustomerInvoiceCount] = rst4![nCustomerInvoiceCount] + 1
rst4.Update
rst4.Close




Forms![AR JOB INVOICE]![chkPOSTED] = -1

wrk2.CommitTrans

MsgBox "Posting has been completed.", , "Ready to Print"
DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD, , A_MENU_VER20


PostInvoice_exit:
Exit Sub

PostInvoice_errhandler:
wrk2.Rollback
MsgBox Error

Resume PostInvoice_exit

End Sub
 
O

Ofer

In the error handler, put the message box before the rollback, it could be
that the error happened before the begin trans code, and the error you
getting its from the rollback in the error capture.

Follow this steps:
1. When you get in the error message, press control break, you will be put
in the code
2. Type resume
3. Step the code by clicking the F8 key, that will bring you to the line
where the error happened

Check where the error

RCrawfordBocaRaton said:
Help!

I have a procedure that has worked for years and yesterday I upgraded all
our machines and databases to Access 2003 and the following error occured in
our Billing procedure.

"3034 You tried to commit or rollback a transaction without first beginning
a transaction"

The code works in Access 2000, I have an unconverted copy on my laptop for
testing. The only diff is the Access versions.

BELOW IS CODE ON BUTTON

Private Sub Print_Button_Click()
'New version written 11/6/96 by P. Salsich
On Error GoTo Print_Button_Click_Err
If Me!POSTED = 0 Then
PostInvoice
Else
MsgBox "This invoice has already been posted. No new posting will be
done.", 48, "Ready to Print"
End If
DoCmd.OpenReport "CONTRACT SALES INVOICE", A_NORMAL, , "[ID] =" & Me!ID
DoCmd.OpenReport "CONTRACT SALES INVOICE", A_NORMAL, , "[ID] =" & Me!ID

Print_Button_Click_Exit:
Exit Sub

Print_Button_Click_Err:
MsgBox Err & " " & Error$, , "Print_Button_Click Error"
Beep
MsgBox "This posting and printing will not be done.", 48, "Rollback"

Resume Print_Button_Click_Exit

End Sub

BELOW IS CODE "PostInvoice"

Sub PostInvoice()
' 1. create new record in td_SYSBatchNumbers
' 2. create new record in invoice headers
' 3. create new record in invoice details
' 4. change appropriate record in [td_SYSPeoplePlacesThings]

On Error GoTo PostInvoice_errhandler

Dim db As Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim rst3 As Recordset
Dim rst4 As Recordset
Dim intBatchnum, intInvoiceKey As Long
Dim wrk2 As Workspace

Set wrk2 = DBEngine.Workspaces(0)
Set db = DBEngine.Workspaces(0).Databases(0)

intBatchnum = DMax("[fknBatchNumber]", "td_SYSBatchNumbers") + 1

wrk2.BeginTrans

Set rst1 = db.OpenRecordset("td_SYSBatchNumbers", DB_OPEN_DYNASET,
DB_DENYREAD)
rst1.AddNew
rst1![fknBatchNumber] = intBatchnum
rst1![dtBatch] = Forms![AR JOB INVOICE]![ARINVDATE]
rst1![fknCompany] = DLookup("[fknCompany]", "td_SYSCompanyProfile")
rst1![sAccessedFrom] = "LCIARJobInvoice"
rst1![cBatchTotal] = Forms![AR JOB INVOICE]![TOTALAMT]

rst1![Discount cAmount] = 0
rst1![cDiscountTaken] = 0

rst1![archived?] = 0
rst1![dtCreated by] = "Admin"
rst1![dtModified] = Forms![AR JOB INVOICE]![ARINVDATE]
rst1![dtModified by] = Null
rst1.Update
rst1.Close



Set rst2 = db.OpenRecordset("td_ARInvoiceHeader", DB_OPEN_DYNASET,
DB_DENYREAD)
rst2.AddNew
rst2![fknCustomer] = Forms![AR JOB INVOICE]![CustKey]
rst2![fknCompany] = DLookup("[fknCompany]", "td_SYSCompanyProfile")
rst2![fknBatchNumber] = intBatchnum
rst2![dtBatch] = Forms![AR JOB INVOICE]![ARINVDATE]
rst2![sInvoiceNumber] = Forms![AR JOB INVOICE]![NUMBER]
rst2![Date] = Forms![AR JOB INVOICE]![ARINVDATE]
rst2![dtProjectedPay] = Forms![AR JOB INVOICE]![ARINVDATE] + 30
rst2![cAmount] = Forms![AR JOB INVOICE]![TOTALAMT]
rst2![fknARGL] = DLookup("[fknARGL]", "td_SYSCompanyProfile")
rst2![Discount cAmount] = 0
rst2![dtDiscount] = Null
rst2![cDiscountTaken?] = 0
rst2![cBalanceDue] = Forms![AR JOB INVOICE]![TOTALAMT]
rst2![dtModified] = Forms![AR JOB INVOICE]![ARINVDATE]
rst2![dtModified by] = "Admin"
rst2![sGeneratedBy] = "LCIARJobInvoice"
rst2![InvType] = "J"
intInvoiceKey = rst2![pkcARInvoice]
rst2.Update
rst2.Close



Set rst3 = db.OpenRecordset("td_ARInvoiceDetail", DB_OPEN_DYNASET,
DB_DENYREAD)
rst3.AddNew
rst3![sInvoiceNumber pkcPayrollTimeHistory] = intInvoiceKey
rst3![nSplitNumber] = 1
rst3![fknCompany] = DLookup("[fknCompany]", "td_SYSCompanyProfile")
rst3![Sales fknDefaultGL] = DLookup("[Sales fknDefaultGL]",
"td_SYSCompanyProfile")
rst3![sReferenceNumber] = Forms![AR JOB INVOICE]![JOBNUMBER]
rst3![Type of Sale] = 11
rst3![Split cAmount] = Forms![AR JOB INVOICE]![TOTALAMT]
rst3![sCreditGLList] = "4100.0.0.0"

rst3.Update
rst3.Close

Set rst4 = db.OpenRecordset("td_SYSPeoplePlacesThings", DB_OPEN_DYNASET,
DB_DENYREAD)
rst4.FindFirst "[pkcPPT] = " & Forms![AR JOB INVOICE]![PPTKey]
rst4.Edit
rst4![ynCustomerInvoicesOutstanding] = True
rst4![nCustomerInvoiceCount] = rst4![nCustomerInvoiceCount] + 1
rst4.Update
rst4.Close




Forms![AR JOB INVOICE]![chkPOSTED] = -1

wrk2.CommitTrans

MsgBox "Posting has been completed.", , "Ready to Print"
DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD, , A_MENU_VER20


PostInvoice_exit:
Exit Sub

PostInvoice_errhandler:
wrk2.Rollback
MsgBox Error

Resume PostInvoice_exit

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

error 3034 "You tried to commit or rollback.." 0

Top