Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access runtime error 2001:  You cancelled the previous operation

Posted on 1998-07-14
9
Medium Priority
?
487 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 100 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

Technology Partners: 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!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

972 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