Editing an existing query vs new query

[Hello1]

I would like to know when to use these 2 options.
For example, right now I have a query (qryQueryName) which is used as a record source for a report , only criteria it has is Id field = to Id field on the form, the report is something like a bill for a customer.
Now I need a new report which will have another (or same) query with another criteria and one more field in the query, instead one bill it will show all the bills for a selected customer (combobox on the form) and a date period (criteria on the date field, from to).
Instead making a new query in access, it would be better to write the SQL in VBA and use the QueryDefs("qryQueryName") to save the SQL to the query?
I suppose I have to write the whole SQL from scratch because i cant remove the criteria from other fields in the query with the QueryDefs parameters option?

SQL = "SELECT * FROM....."
Set MyQry = MyDb.QueryDefs("qryQueryName")
                 MyQry.SQL = SQL
                 MyQry.Close

Vs

'This I suppose I cant use because I need to get rid of the existing criteria in the query which is on another fields
Set MyQry = MyDb.QueryDefs("qryQueryName")
    MyQry.Parameters![Forms!frmFormName!cboName] = [Forms]![frmFormName]![cboName]

Another thing, sometimes I try

Set MyRs = OpenRecordset("qryQueryName")

to put a existing query into a recordset variable without any modification but it gives me an error of too few parameters, expected more (something like that, cant remember now).
That error happens if the query has criterias included, so I cant use it in recordet variable?
Thanks!

 

[CJ_London]

why not just use the criteria parameter for docmd.openreport?

 

[Hello1]

So to add the criteria on open report action?
But what if the query already has a criteria in it which I dont need for that report? How can I remove that criteria? And I mean remove it, not change it. Because I need criteria on another field.
Btw, I edited my previous post

 

[June7]

I often just build SQL statement directly in report RecordSource instead of referencing a saved query object.

If you want a query object to serve multiple outputs, don't apply parameters in query. Apply to form or report.

I have never resorted to QueryDefs to modify query structure for report. I have passed an SQL statement to report via OpenArgs and Open event code behind report sets its own RecordSource. Code in Format event sets visibility of textboxes.

 

[Hello1]

That makes sense, so to leave the query empty of parameters and then apply on open. What about the speed? And difference when setting as RecordSource an already existing query vs putting a build SQL statement?

 

[June7]

If speed is different, I've never been impeded.

 

[Hello1]

I have few subreports on that report, how can I reference to the recordsource of them in VBA?
From Report_Open

 

[CJ_London]

but it gives me an error of too few parameters

you can't open a recordset based on a query where the criteria references a form.

But what if the query already has a criteria in it which I dont need for that report?

depends on the query but typically you would only include criteria that would apply to all options, using the criteria parameter to refine further as required

 

[CJ_London]

I have few subreports on that report, how can I reference to the recordsource of them in VBA?

can't envision why this would be the case, but you can also pass values through the openargs parameter and then use the report open event or perhaps load or current events - or perhaps the subreport linkchild/master properties