Solved

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

Posted on 2013-01-28
8
677 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
8 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
Comment Utility
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
Comment Utility
try to build your query in a string and open it from
Set r = db.OpenRecordset(strSQL)
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Example:

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

Author Closing Comment

by:DebAnn
Comment Utility
Thanks so much - both of these solutions worked and so quickly!! You saved me!
0
 
LVL 65

Expert Comment

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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now