none
error 3061 too few parameters, expected 2 RRS feed

  • Question

  • I'm getting the subject error on the following OpenRecordset

      Dim curDb As Database, strSql As String, recSite As Recordset, recHealthDesc As Recordset
      Dim strCritLoc As String, strCritHealth As String
      Set curDb = CurrentDb()
    '  DoCmd.SetWarnings False
    '  On Error Resume Next
      strSql = "DELETE * FROM [tblTempInfant Health by Site]"
      curDb.Execute (strSql)
    '  strSql = "SELECT DISTINCT [qryBirthWeight by Site].[First Site], tblSiteList.displayname FROM [qryBirthWeight by Site] INNER JOIN tblSiteList ON [qryBirthWeight by Site].[First Site] = tblSiteList.siteID;"
      Set recSite = curDb.OpenRecordset("qryBirthSites")
    The qryBirthSites query is:
    SELECT DISTINCT [qryBirthWeight by Site].[First Site], tblSiteList.displayname
    FROM [qryBirthWeight by Site] INNER JOIN tblSiteList ON [qryBirthWeight by Site].[First Site] = tblSiteList.siteID;

    I have tried both the query 'strQry' and the saved query (which functions properly) as indicated I am at a loss as to what parameters could be expected.

    Thursday, October 31, 2019 1:41 AM

All replies

  • Does qryBirthWeight by Site have parameters, or does it refer to controls on a form? OpenRecordset cannot handle those directly.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 31, 2019 2:08 PM
  • Does qryBirthWeight by Site have parameters, or does it refer to controls on a form? OpenRecordset cannot handle those directly.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Hans:

    Thank you.

    While it doesn't refer directly a query it refers to does get a start and and date from a form. I tried changing it to a table and using curDB.Execute to populate the table but got the same result.

    I have 30 other reports on the same form which are based on those dates so I'll have to examine them to see what I've done differently with this one.

    Thursday, October 31, 2019 7:29 PM
  • While it doesn't refer directly a query it refers to does get a start and and date from a form.
    That's the problem.  When establishing a recordset on a query with parameters you firstly need to evaluate each parameter in the query's Parameters collection.  You then call the OpenRecordset method of the querydef object.  The following is a simple example:

        Dim rst As DAO.Recordset
        Dim qdf As DAO.QueryDef
        Dim prm As DAO.Parameter
        
        Set qdf = CurrentDb.QueryDefs("qryCurrentContactName")
        
        For Each prm In qdf.Parameters
            prm = Eval(prm.Name)
        Next prm
        
        Set rst = qdf.OpenRecordset

        Debug.Print rst.Fields("FirstName"), rst.Fields("LastName")

    qryCurrentContactName is:

        SELECT FirstName, LastName
        FROM qryCurrentContact;

    and qryCurrentContact, which it references, is:

        SELECT *
        FROM Contacts
        WHERE ContactID = Forms!frmContacts!ContactID;

    which, as you can see, references the ContactID control in the frmContacts form as a parameter.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Friday, November 1, 2019 12:02 AM Typo corrected.
    Thursday, October 31, 2019 11:58 PM
  • While it doesn't refer directly a query it refers to does get a start and and date from a form.

    That's the problem.  When establishing a recordset on a query with parameters you firstly need to evaluate each parameter in the query's Parameters collection.  You then call the OpenRecordset method of the querydef object.  The following is a simple example:

        Dim rst As DAO.Recordset
        Dim qdf As DAO.QueryDef
        Dim prm As DAO.Parameter
        
        Set qdf = CurrentDb.QueryDefs("qryCurrentContactName")
        
        For Each prm In qdf.Parameters
            prm = Eval(prm.Name)
        Next prm
        
        Set rst = qdf.OpenRecordset

        Debug.Print rst.Fields("FirstName"), rst.Fields("LastName")

    qryCurrentContactName is:

        SELECT FirstName, LastName
        FROM qryCurrentContact;

    and qryCurrentContact, which it references, is:

        SELECT *
        FROM Contacts
        WHERE ContactID = Forms!frmContacts!ContactID;

    which, as you can see, references the ContactID control in the frmContacts form as a parameter.


    Ken Sheridan, Stafford, England


    Ken:

    Thank you. I believe that I understand the process as you've explained it but I'm unsure how to apply it here. As noted the query which I invoke does not directly reference the form date values but rather invokes the 'qryBirthWeight by Site'

    SELECT tblClientExtra.[First Site], [qryBirths in Period].birth_weight, [qryBirths in Period].infanthealth
    FROM tblClientExtra INNER JOIN [qryBirths in Period] ON tblClientExtra.clientID = [qryBirths in Period].clientID
    ORDER BY tblClientExtra.[First Site], [qryBirths in Period].birth_weight;

    which in turn invokes 'qryBirths in Period'

    SELECT tblClient.clientID, tblClient.delivery_date, tblinfant.birth_weight, tblinfant.infanthealth
    FROM tblClient RIGHT JOIN tblinfant ON tblClient.clientID = tblinfant.clientID
    WHERE (((tblClient.delivery_date)>=[Forms]![frmReportMenu]![program_start_date] And (tblClient.delivery_date)<=[Forms]![frmReportMenu]![program_end_date]));

    which refers to the subject parameters.

    Can I evaluate the parameters in 'qryBirths in Period' as you've shown then invoke 'qryBirthSites' to get the required information?

    Friday, November 1, 2019 3:20 AM
  • Read my reply again.  As you'll see, where a query references another query, and so on, the parameters in the referenced query or queries become members of the referencing query's Parameters collection, so you simply base the recordset on qryBirthSites and loop through its Parameters collection.  You don't need to mention qryBirths in period at all in the code.

    Ken Sheridan, Stafford, England

    Friday, November 1, 2019 10:27 AM
  • Ken:

    Thanks again. I've been playing with this in spare moments on vacation. I'll get back to it next week and report my progress.

    Saturday, November 2, 2019 3:30 AM