Solved

Access runtime error 2001:  You cancelled the previous operation

Posted on 1998-07-14
9
422 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

747 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

15 Experts available now in Live!

Get 1:1 Help Now