VBA Code - OpenRecordSet - Error [3061] too few parameters. Expected 1.

[dowlingm815]

I am receiving error code [3061] too few parameters. Expected 1. I can't seem to find the issue. The error code occurs when on this line of code:

Set rstTemp = CurrentDb.OpenRecordset("sql_Approvers_to_MktPlacePOs")
 

The entire code is as follows:

Private Sub Create_tbl_DistinctPOsApprovers()
 
On Error GoTo Err_Hndlr
 
strPO_TEMP = " "
strApprover_Level_TEMP = " "
strApprover_Username_TEMP = " "
strApprover_Last_Name_TEMP = " "
strApprover_First_Name_TEMP = " "
 
 
'Delete temporary table
DoCmd.RunSQL "DROP TABLE tbl_DistinctPOsApprovers;"
 
 
CurrentDb.Execute ("CREATE TABLE tbl_DistinctPOsApprovers(PO VARCHAR(14), Approver_Level numeric,  Approver_Username VARCHAR(20), Approver_Last_Name VARCHAR(30),  Approver_First_Name VARCHAR(30))")
 
'Bind rstTemp to the temporary table
Set rstTemp = CurrentDb.OpenRecordset("sql_Approvers_to_MktPlacePOs")
Set rstSummary = CurrentDb.OpenRecordset("tbl_DistinctPOsApprovers")
 
 
rstTemp.MoveFirst
 
Do While rstTemp.EOF = False
    If rstTemp!PO <> strPO_TEMP Then
                strPO_TEMP = rstTemp!PO
                strApprover_Level_TEMP = rstTemp!Approver_Level
                strApprover_Username_TEMP = rstTemp!Approver_Username
                strApprover_Last_Name_TEMP = rstTemp!Approver_Last_Name
                strApprover_First_Name_TEMP = rstTemp!Approver_First_Name
 
'***        write record
            rstSummary.AddNew
                rstSummary!PO = strPO_TEMP
                rstSummary!Approver_Level = strApprover_Level_TEMP
                rstSummary!Approver_Username = strApprover_Username_TEMP
                rstSummary!Approver_Last_Name = strApprover_Last_Name_TEMP
                rstSummary!Approver_First_Name = strApprover_First_Name_TEMP
            rstSummary.Update
 
'                    Debug.Print Forms!F_PR_Status!txb_Start_Date; " "; Forms!F_PR_Status!txb_End_Date
 
'***  assign temp values
'            strProductNumber_TEMP = rstTemp!ProductNumber
'            strProductDescription_TEMP = rstTemp!ProductDescription
'*** clear out fields
 
            strApprover_Level_TEMP = " "
            strApprover_Username_TEMP = " "
            strApprover_Last_Name_TEMP = " "
            strApprover_First_Name_TEMP = " "
 
    End If
 
    rstTemp.MoveNext
Loop
 
 
rstTemp.Close
rstSummary.Close
 
Create_tbl_DistinctPOsApprovers_Exit:
  Exit Sub
 
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Create_tbl_DistinctPOsApprovers()"
End Sub
 

[Stewart Ross]

Hi. There are two different types of recordset available in Access: Data Access Objects (the built-in recordsets in Access) and ActiveX Data Objects (ADOX). Unfortunately the two recordset types use different methods, and the error message you are getting on the Openrecordset method is typical if in fact an ADO recordset is implicitly declared when a DAO recordset type is being expected.

To overcome this you will need to qualify the declaration of your recordsets (which I do not see in the code you provided):

Dim rstTemp as DAO.Recordset
Dim rstSummary as DAO.Recordset

To avoid compilation errors you must make sure you have a reference set to the MS DAO object library - from the VBA Editor select Tools, References and ensure that you have a tick under the Microsoft DAO 3.6 library (or its equivalent).

-Stewart

[parodux]

If you run sql_Approvers_t o_MktPlacePOs on its own then it will ask you about the criteria you have in the query. If you have something like: BETWEEN [forms]![YourForm]![DateFrom] AND [forms]![YourForm]![DateTo] then your solution would look like this:

Dim db as DAO.Database
Dim qdf as DAO.Querydef
Dim rstTemp as DAO.Recordset
Set db = CurrentDb()
Set qdf = db.QueryDefs("sql_Approvers_to_MktPlacePOs")
qdf.Parameters(0) = Forms!YourForm!DateFrom
qdf.Parameters(1) = Forms!YourForm!DatoTo
Set rstTemp = qdf.OpenRecordset
 

[dowlingm815]

Good morning,

The code declared both record sets as DAOs and the VBA Editor had select the Microsoft DAO 3.6 library (or its equivalent). And the issue still occurred.

[Stewart Ross]

please post the SQL for sql_Approvers_t o_MktPlacePOs, which is what is being opened as a recordset.

Please note that if you have a reference to a form control in the Where clause of the query this can cause the 'too few parameters' failure you mention.

-Stewart

[dowlingm815]

Yes, it was the form that was generating this error. thank you for your assistance. when it was declared, the error code went away....

i appreciate your help.

[dowlingm815]

I am receiving an error now "[3265]: Item not found in the collection" at the line below. The value of Forms!F_Waiver_ Yr!Txt_Waiver_Y r is 10.

qdf.Parameters(0) = Forms!F_Waiver_Yr!Txt_Waiver_Yr
 

The code is as follows:

Private Sub Create_tbl_DistinctPOsApprovers()
 
On Error GoTo Err_Hndlr
 
 
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstTemp As DAO.Recordset
 
Set dbs = CurrentDb()
 
 
' when query are used with form parameters, they must be declared in procedures as follows:
 
Set qdf = dbs.QueryDefs("sql_Approvers_to_MktPlacePOs")
qdf.Parameters(0) = Forms!F_Waiver_Yr!Txt_Waiver_Yr
Set rstTemp = qdf.OpenRecordset
 
 
 
strPO_TEMP = " "
strApprover_Level_TEMP = " "
strApprover_Username_TEMP = " "
strApprover_Last_Name_TEMP = " "
strApprover_First_Name_TEMP = " "
 
'**** create output table
'Delete temporary table
DoCmd.RunSQL "DROP TABLE tbl_DistinctPOsApprovers;"
 
'Create temporary table
CurrentDb.Execute ("CREATE TABLE tbl_DistinctPOsApprovers(PO VARCHAR(14), Approver_Level numeric,  Approver_Username VARCHAR(20), Approver_Last_Name VARCHAR(30),  Approver_First_Name VARCHAR(30))")
 
'Bind rstTemp to the temporary table
'Set rstTemp = CurrentDb.OpenRecordset("tbl_BlankWaiver_Approvers")
 
Set rstSummary = CurrentDb.OpenRecordset("tbl_DistinctPOsApprovers")
 
 
rstTemp.MoveFirst
 
Do While rstTemp.EOF = False
    If rstTemp!PO <> strPO_TEMP Then
                strPO_TEMP = rstTemp!PO
                strApprover_Level_TEMP = rstTemp!Approver_Level
                strApprover_Username_TEMP = rstTemp!Approver_Username
                strApprover_Last_Name_TEMP = rstTemp!Approver_Last_Name
                strApprover_First_Name_TEMP = rstTemp!Approver_First_Name
 
'***        write record
            rstSummary.AddNew
                rstSummary!PO = strPO_TEMP
                rstSummary!Approver_Level = strApprover_Level_TEMP
                rstSummary!Approver_Username = strApprover_Username_TEMP
                rstSummary!Approver_Last_Name = strApprover_Last_Name_TEMP
                rstSummary!Approver_First_Name = strApprover_First_Name_TEMP
            rstSummary.Update
 
'                    Debug.Print Forms!F_PR_Status!txb_Start_Date; " "; Forms!F_PR_Status!txb_End_Date
 
'***  assign temp values
'            strProductNumber_TEMP = rstTemp!ProductNumber
'            strProductDescription_TEMP = rstTemp!ProductDescription
'*** clear out fields
 
 
            strApprover_Level_TEMP = " "
            strApprover_Username_TEMP = " "
            strApprover_Last_Name_TEMP = " "
            strApprover_First_Name_TEMP = " "
 
 
 
    End If
 
    rstTemp.MoveNext
Loop
 
'***        write last record
 
 
 
rstTemp.Close
rstSummary.Close
 
Create_tbl_DistinctPOsApprovers_Exit:
  Exit Sub
 
 
Err_Hndlr:
    MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Create_tbl_DistinctPOsApprovers()"
End Sub