Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    82

    Errors running queries with currentDB.Execute

    Hi all,
    I've been reading up on this over the last week but am struggling to find the root of the issue. I have two queries, an insert and an update, that I am trying to run using the Execute method so I don't have to disable warnings. I can get the queries to run fine using Docmd.OpenQuery but I would rather have customized messageboxes and only click 'OK' once for the process. Right now I get Error 3061: Too few parameters, Expected 1 as the error and I think it has to do with what needs to be listed after the execute command for each query but I don't know enough about this process nor can I find a lot that explains the execute function, can anyone help? Both queries work as required when run either form a macro or using Docmd.OpenQuery, I just get the two notifications for each one.
    VBA and SQL for each query are below:
    Code:
    Option Compare DatabaseOption Explicit
    
    
    Private Sub cmdRCVLine_Click()
    Dim dbs As DAO.Database
    Dim lngRowsAffected As Long
    Dim Answer As Integer
    Set dbs = CurrentDb()
    
    
    CurrentDb.Execute "qryReceiving"
    CurrentDb.Execute "qryrcvdate"
    
    
    
    
    '-------------------Message Affected ----------------
    MsgBox CStr(dbs.RecordsAffected) & " records has been Processed ", vbOKOnly + vbInformation, "Affected Rows"
    
    
    
    
    End Sub
    SQL for INSERT query:


    Code:
    INSERT INTO tblTransactions ( MaterialID, TransactionQTY, TransactionTypeID )SELECT tblPOLines.MaterialID, tblPOLines.POLineQTY, "1" AS TransTypeID
    FROM tblPOLines
    WHERE ((([tblPOLines]![POLineID])=[Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID]));
    SQL for UPDATE query:
    Code:
    UPDATE tblPOLines SET tblPOLines.POLineClosedDate = Date()WHERE ((([tblPOLines]![POLineID])=[Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID]));

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,032

  3. #3
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    82
    Thank you! I'm seeing here that, like the other examples I could find, rather than running a saved query this example runs the SQL as a string, is that a better approach than running the saved query?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,032
    One reason to use the strSQL variable is to allow you to see (via Debug.Print strSQL) how Access renders the SQL string. You can check it for "syntax errors", or you can take the rendered SQL and run it as a query in the query designer window.

    Is your FORM open when you run the Currentdb.execute???

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,433
    The Execute method can't resolve the form references. A way around the error is to wrap the form reference in the Eval() function:

    Eval('Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID]')
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,434
    For consistency and best practice I would do this:

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub cmdRCVLine_Click()
    Dim dbs As DAO.Database
    Dim lngRowsAffected As Long       'not used
    Dim Answer As Integer                'not used
    Set dbs = CurrentDb()
    dbs.Execute "qryReceiving", dbFailOnError
    dbs.Execute "qryrcvdate", dbFailOnError
    
    '-------------------Message Affected ----------------
    MsgBox CStr(dbs.RecordsAffected) & " records has been Processed ", vbOKOnly + vbInformation, "Affected Rows"
    End Sub

  7. #7
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    I just did this in a program I'm working on lol

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryReceiving", acViewNormal
    DoCmd.OpenQuery "qryrcvdate", acViewNormal
    DoCmd.SetWarnings True

    The Set warning will prevent the messages rememeber you must always turn them back on
    All open code examples and free projects are only available from:
    https://databasedreams.createaforum.com/index.php

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,433
    OpenQuery will work but you won't be able to get the counts with RecordsAffected.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    In that case You would have to use the SQL rather than a query Name
    All open code examples and free projects are only available from:
    https://databasedreams.createaforum.com/index.php

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,433
    Quote Originally Posted by Mickjav View Post
    In that case You would have to use the SQL rather than a query Name
    Not if you use the Eval() function I mentioned in post 5.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,434
    But as Paul mentioned, "OpenQuery will work but you won't be able to get the counts with RecordsAffected."

    You can only get the RecordsAffected via the dbs.execute "queryName", dbFailOnError

    or for an sql string:
    dbs.execute sqlString, dbFailOnError
    Last edited by davegri; 01-14-2020 at 04:10 PM. Reason: edit

  12. #12
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    Quote Originally Posted by pbaldy View Post
    Not if you use the Eval() function I mentioned in post 5.
    Noted thanks
    All open code examples and free projects are only available from:
    https://databasedreams.createaforum.com/index.php

  13. #13
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    82
    Thanks everyone for the responses, and Hi to Dave! You helped me a lot about 18 months ago with another similar project, I'd be happy to share a copy of this one with you if you're interested.

    So, I read through the Allen Browne link and it looks like I will either have to run the SQL as a string or try out the Eval() function mentioned. My form is open when the execute command is run, but I have read, in addition to the response here, that a parameter query won't run as a saved query, and mine do have parameters, mainly the reference to the current record on the form. I think I would like to try the string approach out as that will reduce the number of queries I have, but I am interested in learning more about the Eval() function.

    The only thing I am a bit fuzzy on with how to proceed is that I'll be running two queries, and INSERT and an UPDATE and I am not sure how to phrase the return values for the message box - is rowsaffected the right thing for both? And how do I work with getting back info on both? I'm thinking perhaps I can live without getting a message box for the update query as the user will see the result in the form right away anyway.

    I'm new at VBA and trying stuff out, learning as I go, the string approach seems to be the one which I can work with most easily.
    For others reading through this for similar issues - I read a lot on the Docmd.Setwarnings options and it just seems that losing all warnings isn't worth getting rid of the pesky notifications and I do want some kind of messagebox that lets the user know whether or not they were successful.

    Thank you everyone for being here, I appreciate the advice I get here more than I can express.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,777
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,433
    Using Eval() is simplest at this point since all you have to do is wrap the form references in it. Creating an SQL string and executing it in VBA is more dynamic but can be tricky. This may help if you go that way:

    http://www.baldyweb.com/BuildSQL.htm

    In your original code you'd only get the count for the second execute (after changing to use the dbs variable as someone pointed out). You can either use 2 variables to get the counts for each or accumulate the 2 counts into a single variable.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Trouble running a Currentdb.Execute Command
    By DaKetch in forum Access
    Replies: 9
    Last Post: 03-18-2019, 10:08 AM
  2. RunSQL vs. QueryDef.Execute vs. CurrentDB.Execute
    By caubetiep1337 in forum Queries
    Replies: 8
    Last Post: 12-16-2015, 05:35 PM
  3. Error in CurrentDb.Execute
    By Stefan Moser in forum Access
    Replies: 5
    Last Post: 04-17-2015, 01:38 PM
  4. vba code help - Currentdb.execute
    By joycesolomon in forum Programming
    Replies: 5
    Last Post: 08-11-2014, 11:25 AM
  5. Update sql using currentdb.execute
    By laavista in forum Access
    Replies: 13
    Last Post: 08-15-2011, 03:51 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 - Senior Forums