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
Solved

Access runtime error 2001:  You cancelled the previous operation

Posted on 1998-07-14
9
438 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

808 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