Results 1 to 4 of 4
  1. #1
    Campbell MH is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    1

    Parameter Issues

    I have an Access Database that is reading data from a SharePoint list,
    then outputting the data into an excel spreadsheet There are many queries involved, about 40 total.


    Here is the format of each query:

    Code:
          
            Set qdf20 = db.QueryDefs("<Query Name Here>")
            qdf20!Param1 = Param
            Set rst20 = qdf20.OpenRecordset()           
            qdf20.Close
            rst20.Close
            Set qdf20 = Nothing
            Set rst20 = Nothing

    This is in a 'For' loop, and the 'Param' changes with each iteration of the loop.


    The problem: Some queries work, and some do not. The only thing that is changed
    is the query name, the record set name and the query def name. The errors are
    totally random. The parameters are in an array, and the for the queries that work,
    all the parameters are successfully transferred.


    Solutions I have tried are using the Parameters Collection Object, and using the SQL commands directly in VBA, but that didn't help.
    The error I receive is: "Too few parameters, expected 1". I have read the other posts concerning this same error, but they do not solve the problem.


    I'm at my wits-end trying to find a solution. Can anyone offer any insight?
    It would be very helpful and much appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    'for 1 and only param:
    qdf.parameters(0) = "bob"

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

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    It would help to see the entire code, including how you handle the parameter array, but usually I use something like this:
    Code:
    Set qdf20 = db.QueryDefs("<Query Name Here>")
    'qdf20!Param1 = Param
    Dim prm as Parameter
    For each prm in qdf20.Parameters
         prm.Value=Eval(prm.Name)
    Next prm
    Set rst20 = qdf20.OpenRecordset()           
    qdf20.Close
    rst20.Close
    Set qdf20 = Nothing
    Set rst20 = Nothing
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 2
    Last Post: 07-13-2019, 02:45 PM
  2. Replies: 2
    Last Post: 06-23-2017, 11:20 AM
  3. Replies: 2
    Last Post: 01-26-2017, 08:19 AM
  4. Replies: 1
    Last Post: 02-28-2013, 01:20 PM
  5. Replies: 13
    Last Post: 01-10-2012, 09:56 AM

Tags for this Thread

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