Really really quick tip
It seems that if you use dashes (-) in your field names for any table, and try to run functions on them in VBA like “DCount()”, you’ll keep encountering a Run-Time Error ‘2001′ “You canceled the previous operation”.
My recommendation: get rid of dashes and underscores in your table field names.
Edit: You will also get this error if you request incorrect fields names in the table. For instance, you are trying to ask for a count of customerIds and write the code as “DCount(“customerId”, “tblCustomers”)”, when the field in the actual table is ‘intCustomerId’ or something along those lines. Sounds obvious but Jet gives you back a non-helpful error message.
Troubleshooting Potential Errors when Using Transactions in Ms Access – Do NOT Close the Workspace!
I was doing some recordset juggling in vba and out of habit I usually close every object I open up explicit using the .close method on that object.
For example if I open up a database using something like this
Dim db as dao.databaseSet db = currentDb
I will close it at the end of the function or procedure
Db.closeSet db = nothing
When using transactions, you make use of the workspace collection and you open it along the lines of
Dim myWrk as dao.workspaceSet myWrk = DBEngine.Workspaces(0)
As usual, I want to close this at the end of the procedure along the lines of
myWrk.closeset myWrk = nothing
This is were I went wrong
I was looping through a DAO recordset to check for conditions then make updates to a table. I wanted to make sure things didn’t screw up half way through so implemented some transactions, and then I started getting an error message
Run-Time Error ‘3420’:Object Invalid or No Longer Set
Specifically when I tried to use the .moveNext method of my recordset.
I began to tear my hair out! Google talks about updating my jet version, but that wasn’t the problem at all – I knew that I had the most up to date Jet engine.
It took me some time and a bit of research but I figured out it was happening when I made a call to another procedure while inside the recordset. Looking through the code, and nothing was wrong. I even pasted the code of the sub-procedure into my main procedure (minus all the variable setting and clean up objects stuff) and it work. Something was up in my sub procedures.
It was eventually a comment in this article on transactions at Allen Browne’s excellent Ms Access tips website – I was explicitly closing the workspace. This is apparently a no-no. I deleted the .close line of code, and voila, it all worked!