Prev: Calling SQL Stored Proc from Access (with Parameters)
Next: Rolling 12 Month Average
From: laura on 17 Feb 2005 10:00
Hello,

I've got two queries, one calls another. The first query has a criteria to
select month and year from a date field. The second query calls the first
query.

I want to be able to pass a parameter (a variable) through to the first
query when I call the second query - is this possible?

My first query is called qryEmpEvent and it has a date field where I want to
be able to pass to it a Month and Year. In the Query Design window I put
into the Date field a parameter called "theMonth" (integer) and "theYear"
(integer). If I run the query itself, naturally I get a pop-up box asking me
to put the month and year in which works, but is not what I want.. I want to
be able to pass the variable straight through when I call the second query
query which calls the first and is called qryEmpEventFinal.

My VB Code looks like this... how can I pass the parameter, or variable to
the first query?

stDocName = "qryEmpEventFinal"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Thanks
Laura TD



From: MGFoster on 17 Feb 2005 14:44
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

AFAIK, you can't pass a parameter in an OpenQuery method, you need to
use a Recordset. When you open the "final" query & assign the parameter
to the final query, the query that needs the parameter(s) will get them.
E.g. (DAO):

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

set db = currentdb
set qd = db.querydefs("query_name")
qd(0) = "param1"
qd(1) = "param2"

set rs = qd.openrecordset()

If the query "query_name" uses other queries that have parameters,
assigning the parameters to the "query_name" will pass the parameters to
the nested queries.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQhT0DIechKqOuFEgEQLoQwCeLthdMdCJZUvzpXtOoEzvTzFhnLcAoLw4
Gkuh7JUnlNX4sfRriwKnrIYC
=Yoif
-----END PGP SIGNATURE-----


laura wrote:
> Hello,
>
> I've got two queries, one calls another. The first query has a criteria to
> select month and year from a date field. The second query calls the first
> query.
>
> I want to be able to pass a parameter (a variable) through to the first
> query when I call the second query - is this possible?
>
> My first query is called qryEmpEvent and it has a date field where I want to
> be able to pass to it a Month and Year. In the Query Design window I put
> into the Date field a parameter called "theMonth" (integer) and "theYear"
> (integer). If I run the query itself, naturally I get a pop-up box asking me
> to put the month and year in which works, but is not what I want.. I want to
> be able to pass the variable straight through when I call the second query
> query which calls the first and is called qryEmpEventFinal.
>
> My VB Code looks like this... how can I pass the parameter, or variable to
> the first query?
>
> stDocName = "qryEmpEventFinal"
> DoCmd.OpenQuery stDocName, acNormal, acEdit
>
> Thanks
> Laura TD
>
>
>
From: [MVP] S.Clark on 17 Feb 2005 15:17
You can make a form to hold the two parameter values. In the first query,
reference the fields in the Criteria.

There are other methods, but this is the least VBA intensive.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

"laura" <replyto(a)group.com> wrote in message
news:edLtmFQFFHA.1524(a)TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I've got two queries, one calls another. The first query has a criteria to
> select month and year from a date field. The second query calls the first
> query.
>
> I want to be able to pass a parameter (a variable) through to the first
> query when I call the second query - is this possible?
>
> My first query is called qryEmpEvent and it has a date field where I want
> to be able to pass to it a Month and Year. In the Query Design window I
> put into the Date field a parameter called "theMonth" (integer) and
> "theYear" (integer). If I run the query itself, naturally I get a pop-up
> box asking me to put the month and year in which works, but is not what I
> want.. I want to be able to pass the variable straight through when I call
> the second query query which calls the first and is called
> qryEmpEventFinal.
>
> My VB Code looks like this... how can I pass the parameter, or variable to
> the first query?
>
> stDocName = "qryEmpEventFinal"
> DoCmd.OpenQuery stDocName, acNormal, acEdit
>
> Thanks
> Laura TD
>
>
>


From: laura on 18 Feb 2005 09:03
Hi, I know I can use a form, but the problem is that I am doing it in such a
way that I cannot use a form in this instance. I need to be able to pass the
parameter through the nested queries to the first one (deepest level).

Laura


"[MVP] S.Clark" <steve.clark_NOSPAM(a)FMSInc.com> wrote in message
news:%233B1D3SFFHA.3032(a)TK2MSFTNGP12.phx.gbl...
> You can make a form to hold the two parameter values. In the first query,
> reference the fields in the Criteria.
>
> There are other methods, but this is the least VBA intensive.
>
> --
> Steve Clark, Access MVP
> FMS, Inc.
> www.fmsinc.com/consulting
>
> "laura" <replyto(a)group.com> wrote in message
> news:edLtmFQFFHA.1524(a)TK2MSFTNGP09.phx.gbl...
>> Hello,
>>
>> I've got two queries, one calls another. The first query has a criteria
>> to select month and year from a date field. The second query calls the
>> first query.
>>
>> I want to be able to pass a parameter (a variable) through to the first
>> query when I call the second query - is this possible?
>>
>> My first query is called qryEmpEvent and it has a date field where I want
>> to be able to pass to it a Month and Year. In the Query Design window I
>> put into the Date field a parameter called "theMonth" (integer) and
>> "theYear" (integer). If I run the query itself, naturally I get a pop-up
>> box asking me to put the month and year in which works, but is not what I
>> want.. I want to be able to pass the variable straight through when I
>> call the second query query which calls the first and is called
>> qryEmpEventFinal.
>>
>> My VB Code looks like this... how can I pass the parameter, or variable
>> to the first query?
>>
>> stDocName = "qryEmpEventFinal"
>> DoCmd.OpenQuery stDocName, acNormal, acEdit
>>
>> Thanks
>> Laura TD
>>
>>
>>
>
>


From: Van T. Dinh on 18 Feb 2005 20:25
In your code, I can't see where you pass the Parameter values?

Are the Parameter values (for the first Query) embedded in the SQL of the
second Query?

Please post the SQL Strings of both Queries with brief explanation of the
Table / Field / Query names.

--
HTH
Van T. Dinh
MVP (Access)


"laura" <replyto(a)group.com> wrote in message
news:edLtmFQFFHA.1524(a)TK2MSFTNGP09.phx.gbl...
> Hello,
>
> I've got two queries, one calls another. The first query has a criteria to
> select month and year from a date field. The second query calls the first
> query.
>
> I want to be able to pass a parameter (a variable) through to the first
> query when I call the second query - is this possible?
>
> My first query is called qryEmpEvent and it has a date field where I want
to
> be able to pass to it a Month and Year. In the Query Design window I put
> into the Date field a parameter called "theMonth" (integer) and "theYear"
> (integer). If I run the query itself, naturally I get a pop-up box asking
me
> to put the month and year in which works, but is not what I want.. I want
to
> be able to pass the variable straight through when I call the second query
> query which calls the first and is called qryEmpEventFinal.
>
> My VB Code looks like this... how can I pass the parameter, or variable to
> the first query?
>
> stDocName = "qryEmpEventFinal"
> DoCmd.OpenQuery stDocName, acNormal, acEdit
>
> Thanks
> Laura TD
>
>
>


 |  Next  |  Last
Pages: 1 2
Prev: Calling SQL Stored Proc from Access (with Parameters)
Next: Rolling 12 Month Average