Not able use openrecordset method on query using form control for criteria
OK, here is the scenario.
It is quite possible that what I am trying is not possible. I have
resolved myself to that, but thought I would ask a question as to why it
can't be done. Expert opinions are welcome!
I have a querydef saved that uses a control on a form as criteria, say
something like: ".... WHERE ((CustID = Forms!frmNew!txtID));". OK, that
part works.
But if I try to open a recordset object of this query in code, I get a
runtime error that the query expected a parameter it has not got (Too
few parameters). The form it is referencing is open in Form view, and
the control contains valid info. (The query can be opened from database
window in datasheet view).
We solved this by creating a 'Parameter' query, opening it as a query
def, setting the parameter, and then opening a recordset from the query
def, as below:
dim myqdf as QueryDef, mydb as DATABASE, myrec as Recordset
set mydb = CurrentDB
set myqdf = mydb.QueryDefs("QueryDef from above")
myqdf!CurrentCustID = Forms!frmNew!txtID
set myrec = myqdf.openrecordset()
The above works. I just don't understand why the reference to the form
does not work when opening the query as a recordset item. Any ideas?
Thank you!