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?
Vs
Another thing, sometimes I try
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!
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?
Code:
SQL = "SELECT * FROM....."
Set MyQry = MyDb.QueryDefs("qryQueryName")
MyQry.SQL = SQL
MyQry.Close
Code:
'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
Code:
Set MyRs = OpenRecordset("qryQueryName")
That error happens if the query has criterias included, so I cant use it in recordet variable?
Thanks!
Last edited: