A2K3: Running query via currentdb.openrecordset()

[jnikle]

I have a parameter query named "qry_employee_info_reports" that I need
to run in the OnOpen event of a form. I'm after its total number of
records. The query's got several joins in it, and one of them is to
query "qry_last_transition," which is also a parameter query. Both
querys use the same parameter: a control called "txtSecondDate" on a
pop up form.

What I've been trying to do up to this point is to open up this
parameter form, set the date of "txtSecondDate" to the appropriate
date, and then use the CurrentDB.OpenRecordset method like this: Set
GetEmployeeCount =
CurrentDB.OpenRecordset("qry_employee_info_reports "). I've tried using
different types like dbOpenForwardOnly, etc. as well. No matter what I
try though, i keep getting the "Too few parameters. Expected 1."
error; I suspect because of "qry_last_transition."

My questions are then first, how can I run this query in code to get
its total number of records; and second, how is what I'm doing here any
different then when I run a report based on this query? When I run a
report, whatever needs the parameter just looks at the parameter form
and gets it, right? Why wouldn't this be the same thing?

Any help is greatly appreciated.

-Josh Nikle

[david epsom dot com dot au]

'---------

dim qdf as dao.querydef
dim db as dao.database
dim rs as dao.recordset

set db = currentdb
set qdf = db.querydefs("qry_employee_info_reports")

qdf.parameters(1) = eval(qdf.parameters(1))

set rs = qdf.openrecordset.

'---------
CurrentDB.OpenRecordset is a DAO method.

DoCmd.OpenReport is an Access method.

When you use Access methods, the query is passed through
Access, which gives Access the chance to evaluate Form
references. When you use DAO methods, Access never gets
to see the sql, and never gets to evaluate the Form references

An alternative is to use a table instead of a form. Join
the query to the parameter table, and write the parameter
values to the table before calling the query.
(david)
<jn****@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

I have a parameter query named "qry_employee_info_reports" that I need
to run in the OnOpen event of a form. I'm after its total number of
records. The query's got several joins in it, and one of them is to
query "qry_last_transition," which is also a parameter query. Both
querys use the same parameter: a control called "txtSecondDate" on a
pop up form.

What I've been trying to do up to this point is to open up this
parameter form, set the date of "txtSecondDate" to the appropriate
date, and then use the CurrentDB.OpenRecordset method like this: Set
GetEmployeeCount =
CurrentDB.OpenRecordset("qry_employee_info_reports "). I've tried using
different types like dbOpenForwardOnly, etc. as well. No matter what I
try though, i keep getting the "Too few parameters. Expected 1."
error; I suspect because of "qry_last_transition."

My questions are then first, how can I run this query in code to get
its total number of records; and second, how is what I'm doing here any
different then when I run a report based on this query? When I run a
report, whatever needs the parameter just looks at the parameter form
and gets it, right? Why wouldn't this be the same thing?

Any help is greatly appreciated.

-Josh Nikle