Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am having a terrible time with a line of code / query.

    The line of code...

    Set appraisalRS = appraisalDB.OpenRecordset("qsAppraisalData_Output" )

    simply sets a recordset.

    My problem is I keep getting an error when the code runs.

    Too few parameters. Expected 1.

    I can open and run the query from the database window with no errors. I can open and run each of the queries feeding the main query with no errors.
    I have been fighting this thing all afternoon.


    Any ideas?


    Thanks
    Richard

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Do any of the queries that feed this have a parameter in them.
    Either a user parameter, or a reference to a form control
    in the form Forms!NameOfForm!ControlName

    If they do it will trigger the message.
    You can run a query manually, but if you use DAO or ADODB you must pass the parameters.

    With DAO one method is to add to the Parameters Collection of the Query Def

    Below are 2 example routine that use a Reference to a Parameter Forms!dlgParams!txtDate (the only Parameter)
    in a query that is opened with a recordset.

    The First Fails

    The second sets the Parameter in the QueryDef and then opens the Recordset from the QueryDefs Recordset

    Code:
    Private Sub cmdBad_Click()
    
    Dim rst As DAO.Recordset
    'This query has a reference to the Parameter
    'Forms!dlgParams!txtDate  in it but will fail
    'with the Expected Parameters message
    Set rst = CurrentDb.OpenRecordset("qryFromDate")
    MsgBox "Did that work or did you get an error"
    
    End Sub
    
    Private Sub cmdGood_Click()
    
    Dim rst As DAO.Recordset, prm As DAO.Parameter
    Dim qdf As DAO.QueryDef
    Dim lngRecs As Long
    
    Set qdf = CurrentDb.QueryDefs("qryFromDate")
    Set prm = qdf.Parameters(0)
    'Instead of 0 (if there was more than 1 you could use the number or the actual name
    'e.g. Using a Named Parameter as below is an alternative
    'Set prm = qdf.Parameters("Forms!dlgParams!txtDate")
    
    'Now assign the Value from the Form to the Parameter
    prm = Forms!dlgParams!txtDate
    
    'Then Open the Recordset from the QueryDef
    Set rst = qdf.OpenRecordset
    
    If Not rst.EOF Then
        rst.MoveLast
        lngRecs = rst.RecordCount
        rst.MoveFirst
    Else
        lngRecs = 0
    End If
    
    MsgBox "This one opened OK with " & lngRecs & " Records"
    
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set prm = Nothing
    
    End Sub
    Andrew

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Can you run the main query by itself - that is, not in code. If so do you have an entry in the sort by or filter by properties of that query that refer to something like QueryN.xxx - I've seen that sort of thing cause errors when you try to run the query in code.
    Wendell

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    If, as AKW suggested, that you might have a reference to a form in your query, the easiest way is to use wrap the reference in the Eval function. For example, you might have this:

    ... WHERE BeginDate = Forms!frmSomeForm!txtBegDate

    Change it to:

    ...WHERE BeginDate = eval("Forms!frmSomeForm!txtBegDate")

    Make sure you use the quotes.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Lounger
    Join Date
    Jun 2010
    Location
    Manchester, NH
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can you post the SQL statement that is in the query :qsAppraisalData_Output?

    Bob Oxford
    Bob Oxford
    Software Wizards, Inc.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Found the problem. I reworked the query and found the issue. TWO field names that were spelled incorrectly.


    Thanks to all for your help.
    Richard

Posting Permissions

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