RunTime Error '2001' - FIX!

Hi,

I am getting "runtime error '2001' you cancelled the previous operation" in my VBA code for MS Access. The database is saved on the server and linked to a table of another database which is also on the server and used by many users. But when i put everything locally then i wont get this error. Pls help! Thanks in advance

Cheers

Rohit

Microsoft ISV Community Center Forums3

I get the same error. My response is, "Well duh--that's exactly what I intended to do!"

I have a modular variable vBookmark in my form. OnCurrent, I set vBookmark to Me.Bookmark. Then on MouseWheel (because I don't want to move to another record) I set Me.Bookmark to vBookmark.

Can't do it. I have not found any way around it. Anyone else

Thanks, Tim

I'm having the same problems with the runtime error when working with bookmarks. Any ideas anyone Thanks in advace...I noticed no one has posted anything. Here's my code where I'm getting the problem (I might have some redundat code here I'm sure):

Private Sub ReMakeList()
    If Me.FilterKWH.Value = True Then
        Me.Combo16.RowSource = "SELECT SurveyNumber FROM tbSurvey WHERE (SurveyYear = " & Str(Nz(Me![Combo14], 0)) & " AND SurveyKWH < 1) ORDER BY SurveyNumber"
    Else
        Me.Combo16.RowSource = "SELECT SurveyNumber FROM tbSurvey WHERE (SurveyYear = " & Str(Nz(Me![Combo14], 0)) & ") ORDER BY SurveyNumber"
    End If

    Me.Combo16.Requery
    Me.Recordset.Update
    Me.Refresh
    Me.Recordset.MoveFirst
    Dim rs As Recordset
    Set rs = Me.Recordset.Clone
    rs.Find "[SurveyNumber] = " & Str(Nz(Me.Combo16.ItemData(0), 0)), , adSearchForward
    Me.Bookmark = rs.Bookmark <---------------------- !!!!!!!!!!!!!!!!! Error says it's here when debugging the runtime error !!!!!!!!!!!!!!!!
    Me.Combo16 = Me.SurveyNumber

End Sub

Hi,

a few people have been getting the runtime error 2001 'you cancelled the previous operation' and after many a search I found the reason behind the extremely vague error message...

The answer is here...

http://www.cse.dmu.ac.uk/~mcspence/Access%20and%20VBA%20FAQ.htm

This message is far from helpful, as it bears no obvious relation to the problem!

It can occur in VBA when Access cannot make sense of part of an embedded SQL statement, or when elements within a Domain Aggregate Function are incorrectly specified.

Example:

Look at the Staff Holiday Booking example database. The Holiday form uses a DSum statement to add up the total holiday days booked:

Forms![Staff]!txtSumOfDays = DSum("days", "qryHolDates", "[staffId] = " & Me!staffId)

If you change the "days" to "day" for the first parameter to the DSum statement then you will duly get the error message, as there is no field called "day" on qryHolDates.

The solution is to look carefully at each element of the SQL or Function, and check that they are entirely correct. Put all SQL in string variables, then you can check the contents at run (i.e. failure) time in the Debugger. It could also be useful to put the 3rd parameter (the SQL criterion) for a Domain Aggregate Function in a variable for checking at run/failure time.

Tip. it can be useful to try the function without the optional 3rd parameter (the SQL criterion). If it does not fail, then the fault is in the criterion, so check that carefully. If it still fails, then the fault lies with one (or both!) of the first two parameters.

See the end of section 6.6 of the 'Getting Started' VBA Trainer for a list of things that cause errors in embedded SQL - many of these also apply to Domain Aggregate Function code.

You may also get this error message when you hit OK on an unexpected parameter box, without actually entering a value. The parameter boxes (as you should know) pop up when Access cannot find the item specified - the usual cause is a misspelled name, or an incorrect name for a form or field.

Basically check your spelling and also check your SQL statements are correct taking into consideration the data types of the fields the SQL statement include.

I hope this 'elps.

I beleive it has something to do with the bookmarks becuase I comment out that line above and everthing didn't work as it did with using the bookmarks, but I didn't get the error messages. I was doing the above code when requerying my records on my form to get rid of ones that didn't meet the criteria I had set...this being only the two sql statements at the top of the code. When entering the form, although it goes through this function, there is not that runtime error 2001. When I run through it the second time, I still don't get the error, but when I run through it the third time, I get the error. Here's how I fixed the problem since I couldn't do what I wanted/needed to do with out the bookmarks with still using the same code.

I just redesigned the way I was doing everything. Instead of using the "Recordset.Find" command ("rs.Find" above), I changed all of these to the "DoCmd.GoToRecord" statement. Do a search in the VBA editor on "GoToRecord" to learn how this method works. This works really well, as you can use the the offset of a combo box (ex. Combobox.ItemIndex + 1) to determine which record to go too, when using the "acGoTo" as a parameter. The "GoToRecord" method also uses "acFirst", "acLast", "acPrevious", "acNext", etc. Here's what I replaced the entire code above with when I was finished:

Private Sub ReMakeList()
    If Me.FilterKWH.Value = True Then
        Me.Combo16.RowSource = "SELECT SurveyNumber FROM tbSurvey WHERE (SurveyYear = " & Str(Nz(Me![Combo14], 0)) & " AND SurveyKWH < 1) ORDER BY SurveyNumber"
    Else
        Me.Combo16.RowSource = "SELECT SurveyNumber FROM tbSurvey WHERE (SurveyYear = " & Str(Nz(Me![Combo14], 0)) & ") ORDER BY SurveyNumber"
    End If

    Me.Combo16.Requery
    Me.Recordset.Update
    Me.Refresh

    DoCmd.GoToRecord , , acGoTo, 1

End Sub

This set's my "RowSource" to either of the SQL queries and for "Me.Recordset", I just used the "FilterOn" method, before entering the function, to make the Recordset match what the sql statement for RowSource in the combo box was producing (as in this example the filter would be "SurveyKWH=0" when FilterKWH was checked true).

I hope this helps anyone who might be having this problem, as you don't have to use bookmarks with this solution and there for I didn't have the runtime error 2001 as mentioned above.

Btw, nice site Derek.

Hi HMote

In what application are you working What does "ME" represent I get the
impression this may be an Access database form Are you certain "Me" has
a bookmark property If it does, then you probably need
Set Me.Bookmark = rs.Bookmark

-- Cindy (Word MVP)

Yes Cindy, you are correct, it is an Access db form. I have done as you have said in my above code towards the bottom but that's where it takes me when debugging the runtime error. Anyways, I stopped using bookmarks in my posted other code below and everything works out fine now using DoCmd's. Thanks for the post!