Results 1 to 3 of 3
  1. #1
    jstordah is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    1

    Err 2001 cancelled previous operation when try to docmd.openquery after createqrydef

    I have a form asking a user what data they want to find, and they can enter data in only one box or all 4. Based on their selections I want to build a SQL stmt and create a query that will return that info. (I realize I need to put some more If..Else conditions in there to test what the first box is, right now if the person doesnt enter a state my sql will fail because it will already have an AND condition in it. But I was trying to get the simple version to work first.) It creates the query but when it hits the DoCmd.OpenQuery stmt it fails with error 2001 "you cancelled the previous operation". I am at a loss. In the other posts, I see people suggesting that this should be put into the query itself asking for criteria which is fine if it is only 1-3 items, beyond that it is about impossible to test for all the combinations of blank and filled in choices with Ands and Ors in the query criteria. I am open to a better way to do this.






    MyRequest = "SELECT State,Vision,Status,Client FROM Joblist WHERE "

    'the next 4 lines are from the form asking the user what info they are looking for, eventually this will include another 2 or 3 choices


    If [Forms]![AskMeForm]![whichstate] > " " Then MyRequest = MyRequest & "STATE = JState"
    If [Forms]![AskMeForm]![whichtype] > " " Then MyRequest = MyRequest & " AND TYPE = Jtype"
    If [Forms]![AskMeForm]![eqvalue] > " " Then MyRequest = MyRequest & " AND EQCOST > JValue"
    If [Forms]![AskMeForm]![whichclient] > " " Then MyRequest = MyRequest & " AND Client LIKE JClient"


    Set qdf = db.CreateQueryDef("MytempQuery", MyRequest)


    DoCmd.OpenQuery ("MytempQuery")

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You should include a debug.print MyRequest before your Set statement to help with debugging.
    Try it and let us know what you find.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You realize that if you don't select a state your Where clause will start "WHERE AND ...."? Also using Like without a wildcard is pointless, as it is equivalent to =.

    Consider something like this:

    If Not Isnull([Forms]![AskMeForm]![whichstate] Then MyRequest = MyRequest & "STATE = '" & [Forms]![AskMeForm]![whichstate] & "'"

    You should get the individual variables then build your Where clause using IIFs to add your " AND " between them.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DoCmd.OpenQuery really necessary?
    By Bleekscheetje in forum Programming
    Replies: 3
    Last Post: 09-05-2015, 08:44 AM
  2. DoCmd.OpenQuery with Parameters?
    By mwhcrew in forum Queries
    Replies: 5
    Last Post: 10-10-2014, 01:49 AM
  3. docmd.OpenQuery produces error 2001
    By ultimateguy in forum Programming
    Replies: 4
    Last Post: 08-09-2014, 10:16 PM
  4. Replies: 0
    Last Post: 06-17-2010, 04:51 AM
  5. Replies: 2
    Last Post: 11-02-2009, 10:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums