Solved

Access runtime error 2001:  You cancelled the previous operation

Posted on 1998-07-14
9
442 Views
Last Modified: 2008-02-01
I'm trying to use vba to have access dynamically build a sql query from a form.  When the command DoCmd.OpenQuery "Query1" executes I get error 2001: You cancelled the previous operation.  What does this mean, and how can I correct the problem?
0
Comment
Question by:Beans0063
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 17

Expert Comment

by:ramrom
ID: 1976655
Pray tell what preceeds this error, how you are invoking the command, and what the command has to to with dynamically building a query.
0
 

Author Comment

by:Beans0063
ID: 1976656
Here's the code, based upon help I received from a previous question answered by Tomook:

Private Sub FindSales_Click()

Dim qd As QueryDef
Dim strSQL As String
Set qd = CurrentDb.QueryDefs("Query1")
strSQL = "SELECT Sales.[" & Combo0.Column(1) & "] FROM Sales WHERE [" & Combo0.Column(1) & "]=1;"
qd.SQL = strSQL
Set qd = Nothing
DoCmd.OpenQuery "Query1"

hope this is all you need!
0
 
LVL 17

Expert Comment

by:ramrom
ID: 1976657
I tried your query, replacing ths combo reference with a hard-coded column name, and it works flawlessly. What is the rowsource for combo0? Have you tried opening query1 in design view after running the code to see what the generated SQL looks like?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Beans0063
ID: 1976658
This is what puzzles me... I opened Query1 in design view and it is exactly right.

The source of Combo0 is a table 'Salespeople', which contains two fields: IDNum & Salesperson Name.  This table is different from the table 'Sales', which has Salespeople names as its fields.  Since the tables dont have a relationship (at least one that access can define), I'm using Combo0 so the user can select a field in 'Sales'.

You may want to refer to
http://www.experts-exchange.com/Q.10062968, which explains the overall problem I'm trying to solve.

Thanks very much!
0
 
LVL 17

Expert Comment

by:ramrom
ID: 1976659
Tomook's answer was:

SELECT AdID, [" & Combo1 & "] FROM Sales WHERE [" & Combo1 & "] = 1;

which looks correct. You have presented it as:

SELECT Sales.[" & Combo0.Column(1) & "] FROM Sales WHERE [" & Combo0.Column(1) & "]=1;

which will just give you a list of 1's. What happened to AdID?

None of this, of course, will solve the 2001 problem. Do you get the problem if you start Access, select from the combo and run the query before doing anything else?

I highly suggest that you normalize the data model and NOT use data for column names.
0
 

Author Comment

by:Beans0063
ID: 1976660
Hmmm it looks like my last comment didnt get posted.  Excuse me if this appears twice:

The query I'm running here is purely for testing.  The actual query will actually contain useful information.

The error still occurs when executing directly after access starts up.

I'm not so fond of my data source either, but its all that I have to work with.  Could Access or Excel automagically sort and rework them every time new data needs to be imported?  I think this would be a tough solution.  What I've got here nearly works, but what the hell is a 2001 error?  I cant even find a reference to go into any more detail about it.

Thanks a lot for the help thus far.  It is greatly APPRECIATED.
0
 

Author Comment

by:Beans0063
ID: 1976661
An interesting discovery... Although Access will let me look in the design view and SQL view of my query, it will not actually run it.  It claims "Data Type Mismatch in Criteria Expression".  I'm sure this applies to the Field name.  Access must automatically be cancelling the query because it cannot run it.  Any thoughts about this?
0
 
LVL 17

Expert Comment

by:ramrom
ID: 1976662
What is the datatype of the SalesPersonA etc. It sounds like it is text, in which case modify the sql:

Sales WHERE [" & Combo0.Column(1) & "]= '1' ";

That is, surround the 1 with apostrophes, to make it text instead of number.
0
 
LVL 9

Accepted Solution

by:
perove earned 50 total points
ID: 1976663
Hmmm, I've been here before. This was not an error in access 20 but in 97 it difinitively is a BUG.
What I concluded with then and I thing it is still the matter is a Bug under the Docmd.openquery.If there is a TYPE error in the query you will get this rather myserious message 2001.
If you add in a "" or somthing it will work OK.
So what you have to do is check the TYPE property of the field you are adding to the criteria
Here's an example
        Select Case Db.QueryDefs(Query1).Fields(Combo0.Column(1) ).Type
        Case DB_TEXT
               'Add a " before and after the field
            Case DB_DATE
                'convert the date to american with the #
            Case Else
                'Should work OK no need for the " chr(34)
        End Select
Do this for every field you add after the WHERE statment.
This will not correct the BUG but will prevent you from getting the bug at all.
good luck
perove

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question