Error 3061: Too Few Parameters. Expected 2 (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:41
Joined
Feb 19, 2002
Messages
42,970
Change the parameters in the query. Instead of referencing a form field use a parameter. Therefore


qd.Parameters![Forms]![frmACPData]![UnitNo] = UnitNo
qd.Parameters![Forms]![frmACPData]![EpisodeID] = EpisodeID

becomes:


qd.Parameters![Unit No] = [Forms]![frmACPData]![UnitNo]
qd.Parameters![Episode ID] = [Forms]![frmACPData]![EpisodeID]

But what is the point of opening a recordset and not doing anyghing with it?
 

Ally

Registered User.
Local time
Today, 14:41
Joined
Sep 18, 2001
Messages
617
I'm sorry but I don't understand what you mean about opening the recordset and "not doing anything with it"?

I have changed the code but it still comes up with "Item not found in this collection" and it highlights the line when debugged:

qd.Parameters![UnitNo] = [Forms]![frmACPData]![UnitNo]
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:41
Joined
Feb 19, 2002
Messages
42,970
You need to change the query to match. Also, you'll need to use something other than the actual column name.

Where UnitNo = [Unit No]


[Unit No] is the parameter name and UnitNo is the column name.
 

Ally

Registered User.
Local time
Today, 14:41
Joined
Sep 18, 2001
Messages
617
Right ... there's some progress forward but a couple of steps back. I put the code in a separate module and declared the parameters in the query and it has got rid of the error messages I was getting ... BUT

  • When I open the first form (from another) I get parameter value boxes appear "Unit No" and "Episode ID".
  • The first form that I call the second form from, is erroring with a flicker in the calculated field boxes. I put on a command button with the same calculation on a msgbox and got the following message:
  • The information I enter on the second form does not appear in the first forms listbox.


    "Run time error '64479': The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'Unit No'."
 

K-CJ

New member
Local time
Today, 16:41
Joined
May 6, 2010
Messages
5
I had exactly the same problem as Ally. After spending many hours trying most of the suggestions above which created the same results as for Ally, I finally found the solution to my particular problem. So, if anyone else comes across the same problem, I'd like to share the other thing that might be wrong when referencing to text boxes on a form in code.

This is an extract from my code where it went wrong:

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()

Set rs1 = db.OpenRecordset("qryEmailCirculationRecipients")
rs1.Filter = "BPlanApplication.idBplanApplication =" & [Forms]![Form1]![idBplanApplication]
Set rs2 = rs1.OpenRecordset

It didn't matter if I defined my query in a SQL statement in code, or tried defining the paramater with a QueryDef, or adding the WHERE clause in my saved query, I got the various errors as Ally got.

The solution was very simple and the error lied in my query:
In my "qryEmailCirculationRecipients" query I had my unique ID (idBplanApplication) that links my contacts table to my main table added from the table in my subform (BPlanCirculationContacts.idBplanApplication) instead of the ID from my main table (BPlanApplication.idBplanApplication).
I still don't know why this would make a difference, but it solved my problem. Thus, the correct field that the paramater refers to should be present in your query.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:41
Joined
Sep 12, 2006
Messages
15,613
because apparently you cant use form variables in recordsets.

by adding the same value into the query, you avoided this.
 

K-CJ

New member
Local time
Today, 16:41
Joined
May 6, 2010
Messages
5
In my case the problem was that I added the wrong field in my query that the paramater is based on. The parameter ([Forms]![Form1]![idBplanApplication]) I did not add in my query but as a filter for my recordset. By adding the correct fieldname (BPlanApplication.idBplanApplication from my main table) in my query, the error message ("Item not found in this collection") disappeared.
 

bejaranoangel

New member
Local time
Today, 07:41
Joined
Jun 24, 2014
Messages
11
Here is a DAO example of how to pass parameters when you execute a query in code. I don't know how to do this with ADO but ADO does not have a parameters collection so the method would be different.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
Set dbs = CurrentDb
Set qd = dbs.QueryDefs!qryQCMainSizeCheck
qd.Parameters![YourParm1] = "SomeValue"
qd.Parameters![YourParm2] = "SomeValue"
Set rst = qd.OpenRecordset

If you are only going to execute this query from code I would change the parm names which I gather are now in the form -
Forms!YourFormName!YourControl. You would replace [YourParm1] with whatever the form field reference is -
qd.Parameters![Forms]![YourForm]![YourControl] = [Forms]![YourForm]![YourControl]


Hello!!

above code worked for me!!
I just had to put parameters in the following way!!

QDF As QueryDef

Set MYDA = CurrentDb

Set QDF = MYDA.QueryDefs!RecibosS
QDF.Parameters(0).Value = [Forms]![ImpExp]![Cuadrocombinado11]
QDF.Parameters(1).Value = [Forms]![ImpExp]![Combo112]
Set Tbla = QDF.OpenRecordset

I Hope this helps...

Cheers :)
 

Users who are viewing this thread

Top Bottom