[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 730
  • 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 MVP, Access and Data Platform)Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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 MVP, Access and Data Platform)Commented:
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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