• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 732
  • Last Modified:

Access Run-time error '3061': Too few parameters. Expected 2.

I am getting the following error when executing the code below.  Any idea what I'm doing wrong?  The query uses a start and end date field from the calling form.  Is this the issue perhaps??
Thanks for the help.....

Private Sub Command65_Click()
'Print to pdf 1099 Statements for all advisors with activity

Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = CurrentDb.OpenRecordset("SELECT [Rep] FROM [1099 Details Query]")

rst.MoveFirst

Do Until rst.EOF
    'View and Save the report as a pdf
    DoCmd.OpenReport "[1099 Details Report]", acViewPreview, , "Rep=" & rst!Rep, acHidden
    DoCmd.OutputTo acOutputReport, "[1099 Details Report]", acFormatPDF, "J:\1099 Reports\" & rst!Rep & "1099 Details 2012.pdf"
    DoCmd.Close acReport, "1099 Details Report"
    rst.MoveNext
Loop
    MsgBox "Done"
   
rst.Close
Set rst = Nothing

End Sub
0
DebAnn
Asked:
DebAnn
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
In you query, wrap the Form references with the Eval () function


Eval("Forms!YourFormName!YourControlName")

Including the double quotes ...

mx
0
 
nickinthoozCommented:
try to build your query in a string and open it from
Set r = db.OpenRecordset(strSQL)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<The query uses a start and end date field from the calling form.  Is this the issue perhaps??
>>

 Yes. When opening a recordset in code, Access leaves everything up to you and you need to resolve any references to actual values.

With form references, then it's easy to handle:

Dim db As Database
Dim qdef As QueryDef
Dim rs As Recordset


Set db = CurrentDb()
Set qdef = db.QueryDefs(source)
qdef.Parameters(0) = Eval(qdef.Parameters(0).Name)
Set rs = qdef.OpenRecordset()


Since the parameter name is a reference to the form and control, using Eval() gets the value for you. If you had multiple parameters, then you would just loop on the parameters collection doing a Eval() for each like this:

Dim db As Database
Dim qdef As QueryDef
Dim prm as Parameter
Dim rs As Recordset


Set db = CurrentDb()
Set qdef = db.QueryDefs(source)
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdef.OpenRecordset()  


You could also set the paramertes by name individually:

qdef.Parameters("<name>") = <some value>

or by index:

qdef.Parameters(0) = <some value>
qdef.Parameters(1) = <some value>

JimD.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what are the values of rst!Rep ? numerical or text?

if it's text, you will need:
 DoCmd.OpenReport "[1099 Details Report]", acViewPreview, , "Rep='" & rst!Rep & "'", acHidden 

Open in new window

and if the value could have (single) quotes:
 DoCmd.OpenReport "[1099 Details Report]", acViewPreview, , "Rep='" & replace(rst!Rep, "'", "''") & "'", acHidden 

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Eyeball the query [1099 Details Query] to see if it has any form references or [Enter some value here], which is not compatiable with opening a recordset object without them.

>The query uses a start and end date field from the calling form
Yep.  If it's not reading from the form when you Set rst, then you'll probably have to..

(1)  Create a copy of [1099 Details Query] without the form parameters and save.
(2)  Change the VBA code 'Set rst' line to something like this:

Dim sSQL as String
sSQL = "SELECT [Rep] FROM new_query WHERE start_date=#" & Me.txtStartDate & "# AND end_date=#" & Me.txtEndDate & "#"

'Then use sSQL instead of a query name
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Example:

SELECT tblEmp.*
FROM tblEmp
WHERE tblEmp.SomeDate Between Eval("Forms!YourFormName!txtStartDate") AND Eval("Forms!YourFormName!txtEndDate")
0
 
DebAnnAuthor Commented:
Thanks so much - both of these solutions worked and so quickly!! You saved me!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now