Problem With Parameter Query Containing Criteria Also

[Micron]

The project I was asked to enhance had qry1 as a source for qry2 which has qry2 as a source for qry3, which is an action query. I researched whether or not parameters were passed to sub queries when the top level query was called in code (yes). Qry1 also has 3 criteria fields that are not parameters. So to automate the process, I added two parameters to qry1, and passed them in code when executing qry3 and it worked fine. I released the finished project and end user reported that it was functioning well. Now for the problem.



Yesterday, he reported there was an issue, and when I checked it out, the error code is 3061, too few parameters - expected 5. This happens only when executing qry3 (no other queries are using criteria AND parameters). To convey to all that it is indeed a parameter query, it starts with PARAMETERS STOCK_CD Text ( 255 ), DEPT Text ( 255 );
All queries in the project that are called in code have parameters which are passed to a function that use the execute method to execute like so:
Function runActionQuery(qryName As String, svParam1 As String, Optional svParam2 As String)
Dim db As DAO.Database
Dim qdf1 As DAO.QueryDef
Dim param As DAO.Parameter

Set db = CurrentDb
Set qdf1 = db.QueryDefs(qryName)
qdf1.Parameters("STOCK_CD") = svParam1
If HasNoData(svParam2) = False Then qdf1.Parameters("DEPT") = svParam2
qdf1.Execute
Set db = Nothing
End Function

My questions are
1) why would this stop working if no changes were made (everything in his db looks as original to me, and my development copy has the same issue)?
2) What are the solution options? I'm thinking I may have to convert the query to sql and run it rather than call it.
Thanks for looking.

[ranman256]

why are you running this query via code?
ALL queries can be run via docmd.openquery. Zero code.
the parameters are also saved in the query.

[Micron]

Not sure I recall why I adopted the method and stuck with it. Maybe to avoid having to turn warnings on/off; sometimes to pass criteria that does not come from form controls such as class properties, I guess.
"the parameters are also saved in the query" I think your statement is limited to criteria that comes from sources such as form controls, yes? In this case, I should be able to use your suggestion. However, attempting to do so has uncovered an issue that may be the problem. Will reply when I've addressed that.

[Micron]

I was mistaken in that a last minute change on Friday to a sub query introduced a parameter that wasn't referenced. I guess it caused a cascading effect in that neither the offending query or sub query parameters were resolved, so the total of missing parameters was reported. As for using docmd to run queries, I haven't changed my outlook. Prefer not to turn off things that can set db properties even though resetting them on error would be correct. I lose my network connection 6 times a day around here. It's gotten so bad that I moved my work to the desktop (Citrix is not an improvement, IMHO).