can't open recordset based on UNION query 1

[diwin]

Set rs = db.OpenRecordset("qryCheckMainFBI3", DB_OPEN_DYNASET)

Why does this line in frmSite cause...

run-time error '3061':
too few paramaters. Expect 3.???


qryCheckMainFBI1

SELECT qryFillTempTables.SiteCode, qryFillTempTables.ID, tblTempYear.YearListID AS CheckYear, qryFillTempTables.SeasonID, qryFillTempTables.MainPointID, qryFillTempTables.Point, qryFillTempTables.FBI, qryFillTempTables.MainFBI, qryFillTempTables.f_SiteID, qryFillTempTables.SiteID, qryFillTempTables.YearID AS CheckTempYear
FROM tblTempYear INNER JOIN qryFillTempTables ON tblTempYear.ID = qryFillTempTables.YearID
WHERE (((qryFillTempTables.SeasonID)=[Forms]![frmSite].[cboSeason]) AND ((qryFillTempTables.SiteID)=[Forms]![frmSite].[txtTest]) AND ((qryFillTempTables.YearID)=[Forms]![frmSite].[cboYear]));

qryCheckMainFBI2

SELECT tblSiteInfo.SiteCode, tblPointInfo.ID, tblPointInfo.YearID AS CheckYear, tblPointInfo.SeasonID, tblPointInfo.MainPointID, tblPointInfo.Point, tblPointInfo.FBI, tblPointInfo.MainFBI, tblPointInfo.f_SiteID, tblSiteInfo.SiteID, tblTempYear.ID AS CheckTempYear
FROM tblTempYear INNER JOIN (tblPointInfo INNER JOIN tblSiteInfo ON tblPointInfo.f_SiteID = tblSiteInfo.ID) ON tblTempYear.YearListID = tblPointInfo.YearID
WHERE (((tblPointInfo.SeasonID)=[Forms]![frmSite].[cboSeason]) AND ((tblSiteInfo.SiteID)=[Forms]![frmSite].[txtTest]) AND ((tblTempYear.ID)=[Forms]![frmSite].[cboYear]));

qryCheckMainFBI3

SELECT *
FROM qryCheckMainFBI1
UNION SELECT * FROM qryCheckMainFBI2;

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[RoyVidar]

The line occurs because you have three unresolved parameters in the query "Too few parameters. Expected 3."

Neither DAO nor ADO knows anything about Access objects, so you need to spoon-feed the values (resolve parameters)

Here's some air code for resolving parameters from forms, hopefully not too many spleilng erorrs

[tt]dim qd as dao.querydef
dim db as dao.database
dim prm as dao.parameter

set db = currentdb
set qd = db.querydefs("qryCheckMainFBI3")
for each prm in qd.parameters
prm.value = eval(prm.name)
next prm
set rs = qd.openrecordset()[/tt]

Roy-Vidar

 

[diwin]

How do I read the results of this code?

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[RoyVidar]

It's a recordset, same as your initial recordset - dunno what you intended.

[tt]do while not rs.eof
debug.print rs.fields(0).value, rs.fields(1).value
rs.movenext
loop[/tt]

Roy-Vidar

 

[diwin]

so is it a good idea to...

rewrite the detailed union query in the module and stuff the 3 paramaters from the form controls in the query code?

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[diwin]

OK. I'm lost. I don't understand what resolving is or how I can use it to make my code run.

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[RoyVidar]

Well, you could start with copy/pasting my first suggestion into your code, to replace your line of code that errored.

Then you could try it.

Does it give any errormessages? If not, it probably has succeeded in resolving the parameters and has opened the recordset based on the union query.

Roy-Vidar

 

[diwin]

Oh. I get it. I got it working. I don't have a clue why.

[tab][tab][tab][tab][tab] ^
[tab][tab][tab][tab][tab] < >
[tab][tab][tab][tab][tab] V V


Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[diwin]

OK. I found it on p. 1275.
So much to learn.

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)