Solved

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

Posted on 2013-01-28
8
703 Views
Last Modified: 2013-01-29
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
Comment
Question by:DebAnn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 38828297
In you query, wrap the Form references with the Eval () function


Eval("Forms!YourFormName!YourControlName")

Including the double quotes ...

mx
0
 
LVL 6

Expert Comment

by:nickinthooz
ID: 38828300
try to build your query in a string and open it from
Set r = db.OpenRecordset(strSQL)
0
 
LVL 58
ID: 38828304
<<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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38828307
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
 
LVL 66

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 38828326
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
 
LVL 75
ID: 38828330
Example:

SELECT tblEmp.*
FROM tblEmp
WHERE tblEmp.SomeDate Between Eval("Forms!YourFormName!txtStartDate") AND Eval("Forms!YourFormName!txtEndDate")
0
 

Author Closing Comment

by:DebAnn
ID: 38831268
Thanks so much - both of these solutions worked and so quickly!! You saved me!
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38831278
Thanks for the split.  Good luck with your project.  -Jim
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question