RunTime Error '2001'

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 Forums1

Per our support engineer:

As our ISV buddy only provide some code of his issue. I can not debug it.

But we can give them some suggestion. They should pay attention to the select cause of the statement.

And a document of “Description of DLookup() usage, examples, and troubleshooting in Access 2000” http://support.microsoft.com/kb/q208786/ is some thing useful.

You can find some trouble shooting way in the document. Tips for Troubleshooting and Debugging DLookup() Expressions http://support.microsoft.com/kb/q208786/#XSLTH3303121123120121120120

If our ISV needs more suggestions, would you please ask them to provide us a demo script to reproduce the issue. Thanks.

-brenda (ISV Buddy Team)

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

i have the following code and i get this error. Please help because this is really frustrating.

The Code

Option Compare Database
Option Explicit

Private Sub command2_Click()
Dim strWhere As String

Dim lngLen As Long

Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.Text0) Then
strWhere = strWhere & "([ID] = """ & Me.Text0 & """) AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then

MsgBox "No criteria", vbInformation, "Nothing to do."
Else

strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
Private Sub command3_Click()
Dim ctl As Control
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
Me.FilterOn = False
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub
Private Sub Form_Open(Cancel As Integer)
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

Please Help ME

Hi Zac,

What versions of the OS and Access are you running What are you trying to do with this code I just need more detail in order to send it on to our support team.

Thanks,

-brenda (ISV Buddy Team)

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.hide-link.com/ ~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 De****. 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.

Access 2002

Windows Xp Pro Sp2

And

Windows Xp Media Center Edition Sp2

(I tried both. Don't worry, no multi-booting here)

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)

Hi Zac,

I had hoped to get our internal support team to assist you with this question, but the official support for Access 2002 has reached the end of its lifecycle. My recommendation is to connect with Access users and experts in one of the Access newsgroups. Here's a link to the general newsgroup, but there are others listed in the Access Database menu in the left column.

http://www.microsoft.com/office/community/en-us/default.mspx dg=microsoft.public.access&lang=en&cr=US

I'm sorry that I couldn't help you with this.

-brenda (ISV Buddy Team)

Code Snippet
Private Sub cmdViewReport_Click()
    Dim strSQL As String
    Dim strQueryName As String
    Dim strtranselect As String
    Dim response As Variant

    strQueryName = "qryCertCardOneUpdate"
    strtranselect = "(Left((tblTransaction.TransType),4)) = 'CERT'"
    strSQL = "SELECT tblTransaction.SSAN, tblTransaction.CERT_NO, tblTransaction.CardNo, tblTransaction.CertYear, tblTransaction.TransType, tblTransaction.PrintCard, tblCertification.LNAME, tblCertification.FNAME, tblCertification.MI, tblCertification.PRTHOME, tblCertification.PRTLOCATION, tblCertification.PRTBUSINESS, tblCertification.LOCATION, tblCertification.ADDR, tblCertification.CITY, tblCertification.ST, tblCertification.ZIP, tblCertification.STATUS, tblCertification.BUSINESS, tblCertification.BS_ADDR, tblCertification.BS_CITY, tblCertification.BS_ST, tblCertification.BS_ZIP FROM tblTransaction "
    strSQL = strSQL & "INNER JOIN tblCertification ON (tblTransaction.SSAN = tblCertification.SSAN) AND (tblTransaction.CERT_NO = tblCertification.CERT_NO) "
    strSQL = strSQL & "WHERE tblTransaction.CardNo Is Null AND tblTransaction.CertYear='" & Trim(Me.txtCertYear) & "' AND tblTransaction.PrintCard=No And ((tblTransaction.SSAN) = '" & [Forms]![frmContractor]![txtSSAN] & "') AND (tblCertification.STATUS='Active' Or tblCertification.STATUS='Inactive') AND " & strtranselect & " ORDER BY tblCertification.CERT_NO;"
    CreateCertQuery strSQL, strQueryName
    If DCount("[CERT_NO]", "qryCertCardOne") = 0 Then
        response = MsgBox("No cards found for the selected certification year", 64, "Error Prompt")
    Else
        DoCmd.OpenReport "rptCertCardOne", acViewPreview
    End If
    If DCount("[CERT_NO]", "qryCertCardOneView", "[GOODCARD] = False") > 0 Then
        response = MsgBox("Card will not print due to CE Shortfall.  Would you like to view the certified contractor data " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "Click <Yes> to view or <No> to skip this view", vbQuestion + vbYesNo, "View Card with CE Shortfalls")
        If response = vbYes Then
            DoCmd.OpenForm "frmCardShortfallOne"
        End If
    End If
End Sub

With the following code, I keep getting a Runtime Error 2001 message. Any ideas why I am using Windows XP SP2 and I am running Access 2003.

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!