This Operation is not supported within transactions???

[snicker]

I am tring to delete a record of a sale and add that Item back into inventory. this is the code I'm using.

Option Compare Database

Private Sub Delete_Click()
On Error GoTo Err_Delete_Click
RunSQL "UPDATE DISTINCTROW Products, ProductsSold SET Products.Stock = [Stock]+Forms!EditPrice!EditPriceProductsSub.Form!Ammount WHERE (((Products.ItemNum)=Forms!EditPrice!EditPriceProductsSub.Form!Combo2));"
DoCmd.RunCommand acCmdDeleteRecord
Exit_Delete_Click:
Exit Sub

Err_Delete_Click:
MsgBox Err.Description
Resume Exit_Delete_Click

End Sub


I an error message, not every time, only when I make a change to the code,
This Operation is not supported within transactions.

I've tried everything I can think of, even macros. I cant really figure out print.debug so I dont know what my next step would be. Any suggestions.

Thx Mike

 

[WayneRyan]

Mike,

Here's the RunSQL command. But you should not store things
that you can calculate. Why do you delete the record on the
form?

DoCmd.RunSQL "UPDATE DISTINCTROW Products " & _
             "SET Stock = [Stock] + " & Forms!EditPrice!EditPriceProductsSub.Form!Ammount " & _
             "WHERE ItemNum = " & Forms!EditPrice!EditPriceProductsSub.Form!Combo2 & ";"

Wayne

 

[snicker]

Mike,

...you should not store things
that you can calculate. Why do you delete the record on the
form?

Wayne

I store a sale "quantity" ([ammount]=number of Items sold in that sale), because the sale might 12 items sold or one item sold. So I need to get that number somewhere. right? Is there a better way?

I'm deleting things from the form so the user can select a specific day and sale and remove that one sale. then the code will automatically update the inventory accordingly.


This is the code I'm using, I cant get it to compile and I get a syntax error.

Private Sub Delete_Click()
On Error GoTo Err_Delete_Click
DoCmd.RunSQL "UPDATE DISTINCTROW Products " & _
"SET Stock = [Stock] + " & Forms!EditPrice!EditPriceProductsSub.Form!Ammount" & _
"WHERE ItemNum = " & Forms!EditPrice!EditPriceProductsSub.Form!Combo2 & ";"
DoCmd.RunCommand acCmdDeleteRecord
Exit_Delete_Click:
Exit Sub

Err_Delete_Click:
MsgBox Err.Description
Resume Exit_Delete_Click

End Sub

I know that when I used sql in other statements in my db it would not tolerate any extra ". So I replaced all "(except the first and the last) with '. This fixed the problem before but not this time. Did I do it wrong?

the db is written in Access 2000
Windows xp
Office xp

 

[WayneRyan]

Mike,

OK, if you can't calculate it, store it.

DoCmd.RunSQL "UPDATE DISTINCTROW Products " & _ 
"SET Stock = [Stock] + " & Forms!EditPrice!EditPriceProductsSub.Form!Ammount " & _ 
"WHERE ItemNum = " & Forms!EditPrice!EditPriceProductsSub.Form!Combo2 & ";"

Missed space at end of second line.

This assumes that the Ammount, and combo are numbers.

Wayne

 

[snicker]

I fixed the first error I was talking about. I had to update my Jet Engine to 4.0. My DB was corrupt. Now when I run the macro ( I switched back to macros) It will delete the record but it doesnt run the SQL.

If I make a change in the macro and then save it, It will run the sql untill I close the form. When I reopen the form it wont run the sql, just delete. This is the same with code. Any Ideas?

I'm wondering if this has something to do with the way I'm opening the form. When I click the button to open it I am setting the me.recordsource programically. It seems to work, but who knows

Thx Mike,

 

[PaulGer]

I think there was a " at the end of the 2nd line that shouldn't be there?

DoCmd.RunSQL "UPDATE DISTINCTROW Products " & _ 
"SET Stock = [Stock] + " & Forms!EditPrice!EditPriceProductsSub.Form!Ammount & _ 
" WHERE ItemNum = " & Forms!EditPrice!EditPriceProductsSub.Form!Combo2 & ";"