Results 1 to 3 of 3
  1. #1
    n4trombl is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    2

    Access VBA SQL: Too Few Parameters Expected 1

    Hello. I'm struggling to figure out what I am missing as it relates to a SQL statement I am trying to execute through VBA. Quick description: I have an Access Form that is capturing items in a Listbox. I am trying to loop through the items in the listbox, and for each run an update statement against an Access table to set a flag to TRUE. Problem is when I pull the value from the listbox into a variable I've defined (as LONG), I receive Too Few Parameters Expected 1. However, if I remove the variable from the SQL statement and hard code the value in, the statement executes without issue. That tells me that the statement is valid, but the value is not passing from the variable to the sql statement. I am sure there may also be a better more efficient way of accomplishing setting this flag, but this is the method I've chose thus far. I also tried to put a test textbox in place with a value, and then convert that value to ensure it wasn't a data type issue but still no luck. Thoughts?

    Block of code:

    Dim n As Integer
    Dim varPurOrdDetailID As Long


    Set MyDB = CurrentDb()

    'Update All Line Items To be Marked for Invoiced
    For n = 0 To Me.ListLineItemIDToInvoice.ListCount - 1
    varPurOrdDetailID = Me.ListLineItemIDToInvoice.ItemData(n)
    MyDB.Execute "Update PurchaseOrderDetail Set LineItemInvoiced = True WHERE PurchaseOrderDetail.ID In (varPurOrdDetailID);"
    'MyDB.Execute "Update PurchaseOrderDetail Set LineItemInvoiced = True WHERE PurchaseOrderDetail.ID = varPurOrdDetailID;"



    Next n

    MyDB.Execute "Update PurchaseOrderDetail Set LineItemInvoiced = True WHERE PurchaseOrderDetail.ID = 306;" -->> this works without issue if value is hard coded

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try

    MyDB.Execute "Update PurchaseOrderDetail Set LineItemInvoiced = True WHERE PurchaseOrderDetail.ID =" & varPurOrdDetailID

  3. #3
    n4trombl is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Posts
    2
    I knew I had been looking at this for too long! Thank you Ajax. That worked!

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

Similar Threads

  1. Too Few Parameters Expected 9
    By RunTime91 in forum Access
    Replies: 11
    Last Post: 05-31-2018, 07:10 PM
  2. Too Few Parameters. Expected 1
    By flamesofdoom in forum Programming
    Replies: 1
    Last Post: 02-15-2013, 02:23 PM
  3. Replies: 1
    Last Post: 01-29-2013, 10:24 AM
  4. Too few parameters expected 4
    By forestgump in forum Programming
    Replies: 1
    Last Post: 04-15-2011, 09:10 AM
  5. Too few parameters. Expected 2.
    By PPCMIS2009 in forum Access
    Replies: 0
    Last Post: 01-28-2009, 01:02 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