Solved

Access runtime error 2001:  You cancelled the previous operation

Posted on 1998-07-14
9
427 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
 

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Form button in access 2 37
Field behavior for "locked" form 12 28
ms/access hyperlink/ftp 7 31
Two list boxes - best structure 3 30
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

947 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now