By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,628 Members | 1,175 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,628 IT Pros & Developers. It's quick & easy.

A2K3: Running query via currentdb.openrecordset()

P: n/a
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

Mar 16 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
'---------

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

Mar 16 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.