Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2013-01-28
8
Medium Priority
?
723 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 - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 1000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

810 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