Run-time error "3061" Too few parameters. Expected 1.

  • Thread starter Thread starter Gary S
  • Start date Start date
G

Gary S

When I run the following code, I get the error message "Run-time error '3061'
Too few parameters. Expected 1." It happens on the open recordset line. Help
please!

Dim Db As Database
Dim Rst As Recordset
Dim SpecInstruct As String

Set Db = CurrentDb
Set Rst = Db.OpenRecordset("qryPackingListCustIdComments")

Rst.MoveFirst
Do While Not Rst.EOF
SpecInstruct = Rst("packlist")
SpecInstruct = SpecInstruct & " , "
Rst.MoveNext
Loop
Me.txtSpecialInstructions = SpecInstruct

Rst.Close
Set Rst = Nothing
Set Db = Nothing
 
Does qryPackingListCustIdComments contain any parameters? What happens if
you try to run it directly, rather than using it to build the recordset. Are
you prompted for values? Does it expect to find values from a form?

If it expects to find values from a form, try something like:

Dim Db As Database
Dim Qdf As QueryDef
Dim prm As Parameter
Dim Rst As Recordset
Dim SpecInstruct As String

Set Db = CurrentDb
Set Qdf = Db.QueryDefs("qryPackingListCustIdComments")
For Each prm in Qdf.Parameters
prm.Value = Eval(prm.Name)
Next ptm
Set Rst = Qdf.OpenRecordset

Rst.MoveFirst
Do While Not Rst.EOF
SpecInstruct = Rst("packlist")
SpecInstruct = SpecInstruct & " , "
Rst.MoveNext
Loop
Me.txtSpecialInstructions = SpecInstruct

Rst.Close
Set Rst = Nothing
Set Db = Nothing
 
Doug,

That worked great!

Yes, the qry was referencing a form for selection criteria.
But why is it necessary to do that extra code?
Could you give me a quick explanation of what
the code is doing?
--
Thanks!
Gary S


Douglas J. Steele said:
Does qryPackingListCustIdComments contain any parameters? What happens if
you try to run it directly, rather than using it to build the recordset. Are
you prompted for values? Does it expect to find values from a form?

If it expects to find values from a form, try something like:

Dim Db As Database
Dim Qdf As QueryDef
Dim prm As Parameter
Dim Rst As Recordset
Dim SpecInstruct As String

Set Db = CurrentDb
Set Qdf = Db.QueryDefs("qryPackingListCustIdComments")
For Each prm in Qdf.Parameters
prm.Value = Eval(prm.Name)
Next ptm
Set Rst = Qdf.OpenRecordset

Rst.MoveFirst
Do While Not Rst.EOF
SpecInstruct = Rst("packlist")
SpecInstruct = SpecInstruct & " , "
Rst.MoveNext
Loop
Me.txtSpecialInstructions = SpecInstruct

Rst.Close
Set Rst = Nothing
Set Db = Nothing
Click to expand...
 
That code is assigning values to the parameters defined to the query.

When you open a query, Jet takes care of that for you. When you're using VBA
to open a recordset, it doesn't: you have to do it explicitly. That's taken
care of by the lines

For Each prm in Qdf.Parameters
prm.Value = Eval(prm.Name)
Next ptm

The parameters will have names like Forms!FormName!ControlName.
Eval(Forms!FormName!ControlName) returns what's contained in the control and
assigns it to the Value property of the parameter.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Gary S said:
Doug,

That worked great!

Yes, the qry was referencing a form for selection criteria.
But why is it necessary to do that extra code?
Could you give me a quick explanation of what
the code is doing?
Click to expand...
 
Back
Top