A2K3: Running query via currentdb.openr ecordset()

[jnikle]

I have a parameter query named "qry_employee_i nfo_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_trans ition," which is also a parameter query. Both
querys use the same parameter: a control called "txtSecondD ate" 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 "txtSecondD ate" to the appropriate
date, and then use the CurrentDB.OpenR ecordset method like this: Set
GetEmployeeCoun t =
CurrentDB.OpenR ecordset("qry_e mployee_info_re ports"). I've tried using
different types like dbOpenForwardOn ly, 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_trans ition."

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("q ry_employee_inf o_reports")

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

set rs = qdf.openrecords et.

'---------
CurrentDB.OpenR ecordset is a DAO method.

DoCmd.OpenRepor t 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.c om> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.com.. .

I have a parameter query named "qry_employee_i nfo_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_trans ition," which is also a parameter query. Both
querys use the same parameter: a control called "txtSecondD ate" 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 "txtSecondD ate" to the appropriate
date, and then use the CurrentDB.OpenR ecordset method like this: Set
GetEmployeeCoun t =
CurrentDB.OpenR ecordset("qry_e mployee_info_re ports"). I've tried using
different types like dbOpenForwardOn ly, 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_trans ition."

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