Crazy Query: Too few parameters. Expected 0 ?? 1

[georgesOne]

Good afternoon,

I need to run the VBA command line

Set rst = Currentdb.Openrecordset("sqrySummary", dbOpendynaset)

and this produces an error:
Run-time error: '3061'. Too few parameters.Expected 0.(that is correct: zero!)

Surprsingly, when I run the command line in the same module/procedure

DoCmd.OpenQuery("sqrySummary")

the correct result is produced.

The query "sqrySummary" is based on three crosstab queries as follows. The parameter [Forms]!frm_PPTReports![tbxCut] gives a date.

1. For month 'sqryDTmonth'

PARAMETERS [Forms]!frm_PPTReports![tbxCut] DateTime;
TRANSFORM nz(Count(tbl_Incidents.UpStart),0) AS DT
SELECT tbl_Tools.Tool, tbl_Tools.Active, tbl_Tools.KeyTool, tbl_Tools.ToolRank
FROM tbl_Tools INNER JOIN tbl_Incidents ON tbl_Tools.ToolID = tbl_Incidents.Tool
WHERE (((Year([tbl_Incidents].[Upstart]))>Year([Forms]![frm_PPTReports]![tbxCut])-3) AND tbl_Incidents.Upstart < [Forms]!frm_PPTReports![tbxCut] AND ((tbl_Tools.Active)=True))
GROUP BY tbl_Tools.Tool, tbl_Tools.Active, tbl_Tools.KeyTool, tbl_Tools.KeyTool, tbl_Tools.ToolRank
PIVOT "M" & DateDiff("m",[DownStart],[Forms]![frm_PPTReports]![tbxCut]);

2. For quarter 'sqryDTQuart':

PARAMETERS [Forms]!frm_PPTReports![tbxCut] DateTime;
TRANSFORM nz(Count(tbl_Incidents.UpStart),0) AS DT
SELECT tbl_Tools.Tool, tbl_Tools.Active, tbl_Tools.KeyTool, tbl_Tools.ToolRank
FROM tbl_Tools INNER JOIN tbl_Incidents ON tbl_Tools.ToolID = tbl_Incidents.Tool
WHERE (((Year([tbl_Incidents].[Upstart]))>Year([Forms]![frm_PPTReports]![tbxCut])-3) AND tbl_Incidents.Upstart < [Forms]!frm_PPTReports![tbxCut] AND ((tbl_Tools.Active)=True))
GROUP BY tbl_Tools.Tool, tbl_Tools.Active, tbl_Tools.KeyTool, tbl_Tools.KeyTool, tbl_Tools.ToolRank
PIVOT "Q" & DateDiff("Q",[DownStart],[Forms]![frm_PPTReports]![tbxCut]);

3. For year 'sqryDTYear':

PARAMETERS [Forms]!frm_PPTReports![tbxCut] DateTime;
TRANSFORM nz(Count(tbl_Incidents.UpStart),0) AS DT
SELECT tbl_Tools.Tool, tbl_Tools.Active, tbl_Tools.KeyTool, tbl_Tools.ToolRank
FROM tbl_Tools INNER JOIN tbl_Incidents ON tbl_Tools.ToolID = tbl_Incidents.Tool
WHERE (((Year([tbl_Incidents].[Upstart]))>Year([Forms]![frm_PPTReports]![tbxCut])-3) AND ((tbl_Incidents.UpStart)<[Forms]![frm_PPTReports]![tbxCut]) AND ((tbl_Tools.Active)=True))
GROUP BY tbl_Tools.Tool, tbl_Tools.Active, tbl_Tools.KeyTool, tbl_Tools.KeyTool, tbl_Tools.ToolRank
PIVOT "Y" & DateDiff("yyyy",[DownStart],[Forms]![frm_PPTReports]![tbxCut]) in ("Y0", "Y1", "Y2");

4. Now I want to combine some of these data in 'sqrySummary'

PARAMETERS Forms!frm_PPTReports!tbxCut DateTime;
SELECT sqryDTMonth.Tool AS xVal, Cint(sqryDTMonth.M1) AS yVal1, IIf(Month([Forms]![frm_PPTReports].[tbxCut]) = 1, Format(sqryDTYear.Y1/bss_fMonth(), "0.00"), Format(sqryDTYear.Y0/bss_fMonth(), "0.00")) AS yVal2, IIf(Month([Forms]![frm_PPTReports].[tbxCut]) = 1, Cint(sqryDTYear.Y1), Cint(sqryDTYear.Y0)) AS yVal3, sqryDTMonth.ToolRank As yVal4
FROM (sqryDTMonth INNER JOIN sqryDTQuart ON sqryDTMonth.Tool = sqryDTQuart.Tool) INNER JOIN sqryDTYear ON sqryDTQuart.Tool = sqryDTYear.Tool
WHERE (((sqryDTMonth.KeyTool)=True))
UNION SELECT tbl_Tools.Tool AS xVal, 0 AS yVal1, 0 AS yVal2, 0 AS yVal3, 0 As Val4
FROM tbl_Tools LEFT JOIN sqryDTMonth ON tbl_Tools.Tool = sqryDTMonth.Tool
WHERE (((tbl_Tools.KeyTool)=True) AND ((tbl_Tools.Active)=True) AND ((sqryDTMonth.Tool) Is Null));

I am getting crazy... How do I get a parameter which is not needed ... any help would be highly appreciated.

Thankz in advance, Georges

 

[PHV]

Replace this:
Set rst = Currentdb.Openrecordset("sqrySummary", dbOpendynaset)
with this:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset
Set db = CurrentDb
Set qdf = db.QueryDefs("sqrySummary")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[georgesOne]

PHV,

that worked great. Thanks a lot.
Now there is a small change. I actually have the sql of query 'sqrySummary' stored in a table field and have pulled that string into vba.

Set rst = Currentdb.Openrecordset(sSQL, dbOpendynaset)

where
ssql = "SELECT....sqrySummary sql..." string.
I would like to keep that, because I have approx. 50 queries used or a Powerpoint presentation.
Is that possible... I guess the querydef collection does not apply here.
May be I should think about it myself, but I am so exited now, because I have spent almost the whole weekend over this.
Thanks again, Georges

 

[georgesOne]

PHV,

never mind... I could find it out... so case it closed.
Anyway, I again learned a lot today (sure I will forget soon, however).

Thanks again, Georges