Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Too Few Parameters (2003)

    I have a query that uses a criteria of "[Forms]![frmScoreByEmployee]![Text5]"

    I have another query that uses this query as one of its "tables"

    When I run the query, it works fine.

    When I open that query in VBA as a dataset, I get the dreaded Too Few Parameters error.
    <pre>Set rst = db.OpenRecordset("qryAllScoresForEmployeeID", dbOpenForwardOnly)</pre>



    Any ideas?

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Too Few Parameters (2003)

    From memory you have to supply parameters to the query.
    Like :
    Dim qdf as dao.querydef, rs as da.recordset
    set qdf=CurrentDB.Querydefs("qryAllScoresForEmployeeID ")
    set qdf.parameters(""[Forms]![frmScoreByEmployee]![Text5]") = "[Forms]![frmScoreByEmployee]![Text5]
    set rs=qdf.openrecordset()

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Too Few Parameters (2003)

    It should be

    Dim qdf As DAO.QueryDef, rs As DAO.Recordset

    instead of ... as da.recordset.

    You are assigning a value to a parameter, not an object, so you shouldn't use the keyword Set. Also, there are too many quotes. It should be

    qdf.Parameters("[Forms]![frmScoreByEmployee]![Text5]") = [Forms]![frmScoreByEmployee]![Text5]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Too Few Parameters (2003)

    I often use another solution: create the SQL string for the recordset in code, and substitute the parameter values. Here is a simple example:

    Let's say that you have a parameter query that refers to a text box on a form, with SQL statement:
    <code>
    SELECT tblTest.Field1, tblTest.Field2
    FROM tblTest
    WHERE tblTest.Field3 = [Forms]![frmParam]![txtParam];
    </code>
    To use this in VBA, you need to replace the line breaks with spaces, and in a query based on a single table, you can omit the table name before each field. The semicolon at the end isn't required either:
    <code>
    SELECT Field1, Field2 FROM tblTest WHERE Field3 = [Forms]![frmParam]![txtParam]
    </code>
    To open a recordset on this SQL, you can use:
    <code>
    Dim strSQL As String
    Dim rst As DAO.Recordset

    strSQL = " SELECT Field1, Field2 FROM tblTest WHERE Field3 = " & _
    [Forms]![frmParam]![txtParam]
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
    </code>
    The above assumes that Field3 is a number field.
    If it is a text field, use
    <code>
    strSQL = " SELECT Field1, Field2 FROM tblTest WHERE Field3 = " & _
    Chr(34) & [Forms]![frmParam]![txtParam] & Chr(34)
    </code>
    Chr(34) is the double quote character ".
    If Field3 is a date field, use
    <code>
    strSQL = " SELECT Field1, Field2 FROM tblTest WHERE Field3 = #" & _
    Format([Forms]![frmParam]![txtParam], "mm/dd/yyyy") & "#"
    </code>
    Literal date values must be enclosed in #s, and the Format function ensures that the date is in USA date format regardless of the user's system settings.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Too Few Parameters (2003)

    You need to wrap that reference to the form within the Eval() function, like this: Eval("[Forms]![frmScoreByEmployee]![Text5]")

    Also, notice how the form's reference was place in quotes within Eval.

    It won't interfere with running the query stand-alone, and it enables Access to properly evaluate the reference when it is run from code.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Few Parameters (2003)

    Thanks for the tutorial!

  7. #7
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Few Parameters (2003)

    Mark,

    I decided to try the shortest code first, and this did the trick!

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •