469,328 Members | 1,324 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,328 developers. It's quick & easy.

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

133 100+
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:

Expand|Select|Wrap|Line Numbers
  1. Set rstTemp = CurrentDb.OpenRecordset("sql_Approvers_to_MktPlacePOs")
  2.  
The entire code is as follows:


Expand|Select|Wrap|Line Numbers
  1. Private Sub Create_tbl_DistinctPOsApprovers()
  2.  
  3. On Error GoTo Err_Hndlr
  4.  
  5. strPO_TEMP = " "
  6. strApprover_Level_TEMP = " "
  7. strApprover_Username_TEMP = " "
  8. strApprover_Last_Name_TEMP = " "
  9. strApprover_First_Name_TEMP = " "
  10.  
  11.  
  12. 'Delete temporary table
  13. DoCmd.RunSQL "DROP TABLE tbl_DistinctPOsApprovers;"
  14.  
  15.  
  16. 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))")
  17.  
  18. 'Bind rstTemp to the temporary table
  19. Set rstTemp = CurrentDb.OpenRecordset("sql_Approvers_to_MktPlacePOs")
  20. Set rstSummary = CurrentDb.OpenRecordset("tbl_DistinctPOsApprovers")
  21.  
  22.  
  23. rstTemp.MoveFirst
  24.  
  25. Do While rstTemp.EOF = False
  26.     If rstTemp!PO <> strPO_TEMP Then
  27.                 strPO_TEMP = rstTemp!PO
  28.                 strApprover_Level_TEMP = rstTemp!Approver_Level
  29.                 strApprover_Username_TEMP = rstTemp!Approver_Username
  30.                 strApprover_Last_Name_TEMP = rstTemp!Approver_Last_Name
  31.                 strApprover_First_Name_TEMP = rstTemp!Approver_First_Name
  32.  
  33. '***        write record
  34.             rstSummary.AddNew
  35.                 rstSummary!PO = strPO_TEMP
  36.                 rstSummary!Approver_Level = strApprover_Level_TEMP
  37.                 rstSummary!Approver_Username = strApprover_Username_TEMP
  38.                 rstSummary!Approver_Last_Name = strApprover_Last_Name_TEMP
  39.                 rstSummary!Approver_First_Name = strApprover_First_Name_TEMP
  40.             rstSummary.Update
  41.  
  42. '                    Debug.Print Forms!F_PR_Status!txb_Start_Date; " "; Forms!F_PR_Status!txb_End_Date
  43.  
  44. '***  assign temp values
  45. '            strProductNumber_TEMP = rstTemp!ProductNumber
  46. '            strProductDescription_TEMP = rstTemp!ProductDescription
  47. '*** clear out fields
  48.  
  49.             strApprover_Level_TEMP = " "
  50.             strApprover_Username_TEMP = " "
  51.             strApprover_Last_Name_TEMP = " "
  52.             strApprover_First_Name_TEMP = " "
  53.  
  54.     End If
  55.  
  56.     rstTemp.MoveNext
  57. Loop
  58.  
  59.  
  60. rstTemp.Close
  61. rstSummary.Close
  62.  
  63. Create_tbl_DistinctPOsApprovers_Exit:
  64.   Exit Sub
  65.  
  66.  
  67. Err_Hndlr:
  68.     MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Create_tbl_DistinctPOsApprovers()"
  69. End Sub
  70.  
Sep 7 '10 #1
6 12054
Stewart Ross
2,545 Expert Mod 2GB
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):

Expand|Select|Wrap|Line Numbers
  1. Dim rstTemp as DAO.Recordset
  2. 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
Sep 7 '10 #2
parodux
26
If you run sql_Approvers_to_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:

Expand|Select|Wrap|Line Numbers
  1. Dim db as DAO.Database
  2. Dim qdf as DAO.Querydef
  3. Dim rstTemp as DAO.Recordset
  4. Set db = CurrentDb()
  5. Set qdf = db.QueryDefs("sql_Approvers_to_MktPlacePOs")
  6. qdf.Parameters(0) = Forms!YourForm!DateFrom
  7. qdf.Parameters(1) = Forms!YourForm!DatoTo
  8. Set rstTemp = qdf.OpenRecordset
  9.  
Sep 8 '10 #3
dowlingm815
133 100+
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.
Sep 8 '10 #4
Stewart Ross
2,545 Expert Mod 2GB
please post the SQL for sql_Approvers_to_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
Sep 8 '10 #5
dowlingm815
133 100+
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.
Sep 8 '10 #6
dowlingm815
133 100+
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_Yr is 10.

Expand|Select|Wrap|Line Numbers
  1. qdf.Parameters(0) = Forms!F_Waiver_Yr!Txt_Waiver_Yr
  2.  
The code is as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Create_tbl_DistinctPOsApprovers()
  2.  
  3. On Error GoTo Err_Hndlr
  4.  
  5.  
  6. Dim qdf As DAO.QueryDef
  7. Dim dbs As DAO.Database
  8. Dim rstTemp As DAO.Recordset
  9.  
  10. Set dbs = CurrentDb()
  11.  
  12.  
  13. ' when query are used with form parameters, they must be declared in procedures as follows:
  14.  
  15. Set qdf = dbs.QueryDefs("sql_Approvers_to_MktPlacePOs")
  16. qdf.Parameters(0) = Forms!F_Waiver_Yr!Txt_Waiver_Yr
  17. Set rstTemp = qdf.OpenRecordset
  18.  
  19.  
  20.  
  21. strPO_TEMP = " "
  22. strApprover_Level_TEMP = " "
  23. strApprover_Username_TEMP = " "
  24. strApprover_Last_Name_TEMP = " "
  25. strApprover_First_Name_TEMP = " "
  26.  
  27. '**** create output table
  28. 'Delete temporary table
  29. DoCmd.RunSQL "DROP TABLE tbl_DistinctPOsApprovers;"
  30.  
  31. 'Create temporary table
  32. 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))")
  33.  
  34. 'Bind rstTemp to the temporary table
  35. 'Set rstTemp = CurrentDb.OpenRecordset("tbl_BlankWaiver_Approvers")
  36.  
  37. Set rstSummary = CurrentDb.OpenRecordset("tbl_DistinctPOsApprovers")
  38.  
  39.  
  40. rstTemp.MoveFirst
  41.  
  42. Do While rstTemp.EOF = False
  43.     If rstTemp!PO <> strPO_TEMP Then
  44.                 strPO_TEMP = rstTemp!PO
  45.                 strApprover_Level_TEMP = rstTemp!Approver_Level
  46.                 strApprover_Username_TEMP = rstTemp!Approver_Username
  47.                 strApprover_Last_Name_TEMP = rstTemp!Approver_Last_Name
  48.                 strApprover_First_Name_TEMP = rstTemp!Approver_First_Name
  49.  
  50. '***        write record
  51.             rstSummary.AddNew
  52.                 rstSummary!PO = strPO_TEMP
  53.                 rstSummary!Approver_Level = strApprover_Level_TEMP
  54.                 rstSummary!Approver_Username = strApprover_Username_TEMP
  55.                 rstSummary!Approver_Last_Name = strApprover_Last_Name_TEMP
  56.                 rstSummary!Approver_First_Name = strApprover_First_Name_TEMP
  57.             rstSummary.Update
  58.  
  59. '                    Debug.Print Forms!F_PR_Status!txb_Start_Date; " "; Forms!F_PR_Status!txb_End_Date
  60.  
  61. '***  assign temp values
  62. '            strProductNumber_TEMP = rstTemp!ProductNumber
  63. '            strProductDescription_TEMP = rstTemp!ProductDescription
  64. '*** clear out fields
  65.  
  66.  
  67.             strApprover_Level_TEMP = " "
  68.             strApprover_Username_TEMP = " "
  69.             strApprover_Last_Name_TEMP = " "
  70.             strApprover_First_Name_TEMP = " "
  71.  
  72.  
  73.  
  74.     End If
  75.  
  76.     rstTemp.MoveNext
  77. Loop
  78.  
  79. '***        write last record
  80.  
  81.  
  82.  
  83. rstTemp.Close
  84. rstSummary.Close
  85.  
  86. Create_tbl_DistinctPOsApprovers_Exit:
  87.   Exit Sub
  88.  
  89.  
  90. Err_Hndlr:
  91.     MsgBox "[" & Err.Number & "]:  " & Err.Description, vbInformation, "Create_tbl_DistinctPOsApprovers()"
  92. End Sub
  93.  
  94.  
  95.  
Sep 10 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Miranda Evans | last post: by
2 posts views Thread by fanfromfla | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.