VBA Run Query and Prompt forQuery Parameters

[Ryan Hubbard]

I would like to run a query from VBA. The query will be one with
parameters. Is there a way to run the query and have Access prompt
for the values like it does if I where to execute it through the
database window?

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you run a parameter query thru VBA w/o supplying the parameters, the
query will "ask" for them in pop-up prompts.

You could also set up the query to use values in an open form: this
will allow the query to retrieve the values w/o a pop-up prompt. E.g.:

Query SQL view:

PARAMETERS Form!form_name!control_name TEXT;
SELECT ...
FROM ...
WHERE text_column = Form!form_name!control_name

See the Access Help article on PARAMETERS declaration.

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

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

iQA/AwUBQSEm7IechKqOuFEgEQKxvgCePW6YvSxC3nwo3NU0h4Znaz AtB6YAoOjY
mKKehSRPtI1JbYI0G80FOZZw
=Qutp
-----END PGP SIGNATURE-----
Ryan Hubbard wrote:

I would like to run a query from VBA. The query will be one with
parameters. Is there a way to run the query and have Access prompt
for the values like it does if I where to execute it through the
database window?

[Ryan Hubbard]

Thanks for the reply. When I run a query that specifies parameters
(without specifing them of course) I get a en error "Too few
parameters. Expected <n>" where n is the number of parameters. I'm
using Office 2000 and DAO 3.6. How do I make Access "ask" for the
parameters?

Also do you know if it is possible to get the recordset from an open
query? I would like to be able to gather data from a query that is
currently open. I can get the QueryDef opject from Queries that are
open but can't seem to get the recordset. Is it possible?

Thanks

MGFoster <[e-mail]> wrote in message news:<0G*******************@newsread1.news.pas.ear thlink.net>...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you run a parameter query thru VBA w/o supplying the parameters, the
query will "ask" for them in pop-up prompts.

You could also set up the query to use values in an open form: this
will allow the query to retrieve the values w/o a pop-up prompt. E.g.:

Query SQL view:

PARAMETERS Form!form_name!control_name TEXT;
SELECT ...
FROM ...
WHERE text_column = Form!form_name!control_name

See the Access Help article on PARAMETERS declaration.

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

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

iQA/AwUBQSEm7IechKqOuFEgEQKxvgCePW6YvSxC3nwo3NU0h4Znaz AtB6YAoOjY
mKKehSRPtI1JbYI0G80FOZZw
=Qutp
-----END PGP SIGNATURE-----
Ryan Hubbard wrote:

I would like to run a query from VBA. The query will be one with
parameters. Is there a way to run the query and have Access prompt
for the values like it does if I where to execute it through the
database window?

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I was wrong about running a parameters query from VBA & the qry "asking"
for the parameters. You do have to explicitly assign them. Like this:

set qd = db.querydefs("query name")
qd.parameter(0) = "first parameter"
qd.parameter(1) = "2nd parameter"

' get data into a recordset
set rs = qd.openrecordset()

=====

I believe I once saw a discussion about accessing a query's datasheet
view, but, I can't remember much about it 'cuz I don't ever do that.

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

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

iQA/AwUBQSKJ2IechKqOuFEgEQI7NACg/aCL9GHG2hJyRgjddh2pBvQJRVwAoMuu
vgV4qO6le8OVeDYY+DTgCva6
=BW5n
-----END PGP SIGNATURE-----
Ryan Hubbard wrote:

Thanks for the reply. When I run a query that specifies parameters
(without specifing them of course) I get a en error "Too few
parameters. Expected <n>" where n is the number of parameters. I'm
using Office 2000 and DAO 3.6. How do I make Access "ask" for the
parameters?

Also do you know if it is possible to get the recordset from an open
query? I would like to be able to gather data from a query that is
currently open. I can get the QueryDef opject from Queries that are
open but can't seem to get the recordset. Is it possible?

Thanks

MGFoster <[e-mail]> wrote in message news:<0G*******************@newsread1.news.pas.ear thlink.net>...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you run a parameter query thru VBA w/o supplying the parameters, the
query will "ask" for them in pop-up prompts.

You could also set up the query to use values in an open form: this
will allow the query to retrieve the values w/o a pop-up prompt. E.g.:

Query SQL view:

PARAMETERS Form!form_name!control_name TEXT;
SELECT ...
FROM ...
WHERE text_column = Form!form_name!control_name

See the Access Help article on PARAMETERS declaration.

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

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

iQA/AwUBQSEm7IechKqOuFEgEQKxvgCePW6YvSxC3nwo3NU0h4Znaz AtB6YAoOjY
mKKehSRPtI1JbYI0G80FOZZw
=Qutp
-----END PGP SIGNATURE-----
Ryan Hubbard wrote:

I would like to run a query from VBA. The query will be one with
parameters. Is there a way to run the query and have Access prompt
for the values like it does if I where to execute it through the
database window?