runtime error 3061 too few parameters when OpenRecordset

[inso18]

Hi Everyone.

I'm using a code to leave some things dynamic in a report (rptGraph):

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim MyDB As Database, Qry_RoundOrder As Recordset, Qry_GraphData As Recordset
   
    Set MyDB = CurrentDb()
    Set Qry_RoundOrder = MyDB.OpenRecordset("qryRoundOrderCurrNetSel", dbOpenDynaset)
    Debug.Print DCount("abc", "qryGraph") 'prints a positive number
    Set Qry_GraphData = MyDB.OpenRecordset("qryGraph", dbOpenDynaset)

...
the qryGraph SQL contains a HAVING which uses a field from the report

HAVING (((ttblData.BranchId)=[Reports]![rptGraph]![BranchId])

qryGraph runs succesfully if executed while report is running. Also the DCount line in the code above prints the correct number. I get an error "runtime error 3061 too few parameters when OpenRecordset - Expected 1" on the Set Qry_GraphData line of the code, while the other Set line doesn't give me any error.

Anyone knows why do I get the error, and how can I solve this?

Thanks very much
inso18.

 

[CautionMP]

inso18,
First guess is [tt][Reports]![rptGraph]![BranchId][/tt] doesn't exist when the Detail Format event fires.

I'm guessing that [tt][Reports]![rptGraph][/tt] doesn't exist until AFTER the first Detail Format event is complete (the order of report events has never been clear to me.)

A possible solution: Use SQL instead of a saved query.

Set Qry_GraphData = MyDB.OpenRecordset("SELECT ... FROM ... HAVING BranchId=" & Me.BranchId & ")", dbOpenDynaset)

Hope this helps,
CMP



[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[inso18]

Dear CautionMP,
thanks for your reply

I'm guessing that [Reports]![rptGraph] doesn't exist until AFTER the first Detail Format event is complete

In the code I've pasted in my first post I've intentionally made the check to be sure the that [Reports]![rptGraph] does exist before that Set line:

    Set Qry_RoundOrder = MyDB.OpenRecordset("qryRoundOrderCurrNetSel", dbOpenDynaset)
    Debug.Print DCount("abc", "qryGraph") 'prints a positive number
    Set Qry_GraphData = MyDB.OpenRecordset("qryGraph", dbOpenDynaset)

[Reports]![rptGraph] exists before the Set line because that Debug.Print line prints the correct number.

 

[PHV]

What is the SQL code of qryGraph ?

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

 

[inso18]

What is the SQL code of qryGraph ?

SELECT ttblData.NetAcBranchGrade
FROM ttblData INNER JOIN qryRoundCurrNet ON ttblData.RoundId=qryRoundCurrNet.RoundId
GROUP BY ttblData.BranchId, tblRoundAll.NetAcBranchGrade
HAVING (((ttblData.BranchId)=Reports!rptGraph!BranchId))
ORDER BY ttblData.BranchId;

The query shows the correct results after the report is displayed, and the code DCount("NetAcBranchGrade","qryGraph") shows the correct result when run on report_open just before setting the query with OpenRecordset.

 

[PHV]

Really ?
the GROUP BY clause seems very strange to me as tblRoundAll is not in the FROM list and BranchId is not SELECTed.

Anyway, here a starting point:
Set Qry_GraphData = MyDB.OpenRecordset("SELECT DISTINCT D.NetAcBranchGrade FROM ttblData D INNER JOIN qryRoundCurrNet R ON D.RoundId=R.RoundId WHERE D.BranchId=" & Reports!rptGraph!BranchId, dbOpenDynaset)

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