Run-time error 3061 - Too few parameters, Expect 1

[Marin]

Hi all,

I am getting this run-time error when I try to execute SQL
statement from VB code in Access 2000. Here is my code"

Dim sCqlStr As String
Dim myDb As DAO.Database
Dim myRs As DAO.Recordset

Set myDb = CurrentDb

cSqlStr = "SELECT Unit FROM q_NotImp2Imp;"

Set myRs = myDb.OpenRecordset(cSqlStr)

This is where it failed.

I can run this query ("Select Unit From q_NotImp2Imp") in
Access SQL query window and it works just fine.

Please help!

Regards, Marin

 

[Marin]

q_NotImp2Imp is SQL query, here is detail

SELECT DISTINCT PL.LotNum AS Primex_P_Id, PL.Mori AS Unit,
PL.InvGroup
FROM [Select
IH.LotNum
, IH.Mori
, IH.InvGroup
From
t_Interfor_Hdr As IH
Left Join
t_InventoryGroups IG
On IH.InvGroup = IG.InvGrp_Name
Where
IG.InvGrp_Id = [Forms]![f_Main]![InvGroup]
]. AS PL LEFT JOIN q_Mapp_IGName AS M ON (PL.InvGroup =
M.InvGrp_Name ) AND (PL.Mori = M.Unit) AND (PL.LotNum =
M.Primex_Id)
WHERE M.Primex_Id Is Null;

And Yes, the field [Forms]![f_Main]![InvGroup] has proper
value selected, it is not Null.

 

[[MVP] S. Clark]

The following is a guess, please take it as such.

Because the parameter is part of the subquery, which is being run from DAO,
it can't locate the form. One of those, "You can't get there from here" kind
of things. I'm inclined to suggest that you work with the Parameters
collection, which is within the querydefs collection, to try to set the
value prior to execution.

Example:
Set qdfTemp = dbsTemp.QueryDefs(strQuery)
qdfTemp.Parameters(strParameter) = varValue

But again, due to the subquery, I don't know if this is possible.

If you want a hard and fast recommendation, it would be to eliminate the
subquery, and build other saved queries to link together. I know that
doesn't sound fun, but I avoid subqueries when I can, for no other reason
then they are difficult to debug.

q_NotImp2Imp is SQL query, here is detail

SELECT DISTINCT PL.LotNum AS Primex_P_Id, PL.Mori AS Unit,
PL.InvGroup
FROM [Select
IH.LotNum
, IH.Mori
, IH.InvGroup
From
t_Interfor_Hdr As IH
Left Join
t_InventoryGroups IG
On IH.InvGroup = IG.InvGrp_Name
Where
IG.InvGrp_Id = [Forms]![f_Main]![InvGroup]
]. AS PL LEFT JOIN q_Mapp_IGName AS M ON (PL.InvGroup =
M.InvGrp_Name ) AND (PL.Mori = M.Unit) AND (PL.LotNum =
M.Primex_Id)
WHERE M.Primex_Id Is Null;

And Yes, the field [Forms]![f_Main]![InvGroup] has proper
value selected, it is not Null.

-----Original Message-----
What is the SQL for q_NotImp2Imp?

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-




.