Combox box query tries to run again

[JMF]

I asked several days ago how to create a parameter query where the user is
offered a combo box, and received a number of great responses, including the
one attached below. Following those instructions, I successfully created a
form that creates a query from a combo box.

At that point, I became more ambitious and wanted to finish the job, adding
that last step mentioned by John Vinson:

It's convenient to base a second Form or Report on the resulting query to
display the results; if you put a button on frmCriteria to launch that
form
or report, the user can enter the criterion and view the results in one
simple operation!

That is, I decided to add a report at the end, based on my
combo-box-generated query.

However, this is what happened: when I launch that report, it tries to run
the query again, asking for the parameter input of that form's combo box. I
wanted the report to just run based on the already-finished query, not
running it again.

Clearly I've missed something conceptually here. Can somebody straighten me
out?

Thanks!

John

----- Original Message -----
From: "John Spencer" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Friday, April 28, 2006 5:53 PM
Subject: Re: New to queries: parameter query with dropdown box?

Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm


A brief quote from an John Vinson (Access MVP) posting.

You'll need to create a small unbound Form (let's call it frmCriteria)
with
a Combo Box control (cboCrit) on it. Use the combo box wizard to select
the
table for the selections, and be
sure that the bound field of the combo is the value you want to use as a
criterion. Save this form.

Now use

=[Forms]![frmCriteria]![cboCrit]

as the criterion in your Query.

It's convenient to base a second Form or Report on the resulting query to
display the results; if you put a button on frmCriteria to launch that
form
or report, the user can enter the criterion and view the results in one
simple operation!

Quoting John Vinson

 

[JMF]

However, this is what happened: when I launch that report, it tries to run

the query again, asking for the parameter input of that form's combo box.
I wanted the report to just run based on the already-finished query, not
running it again.

Clearly I've missed something conceptually here. Can somebody straighten
me out?

Well, folks, I sort of figured it out. Turns out I was closing the form
before running the report.

I had to leave it open, right? Once I did that, the report ran OK.

John

 

[Wayne Morgan]

When the report opens it will run the query used as its RecordSource. If
that query is a parameter query and it can't find the value for the
parameter, it will prompt for it. For the query to get its value from the
form, the form must be open. If you're wanting to use this for a report, I
would recommend adding code to the Open event of the report to open the form
in Dialog mode. This will pause the code and the report's opening until you
close or hide the form. You will need 2 buttons on the form, a Continue or
Ok and a Cancel.

When the user is done making the necessary selection(s) they would click the
Ok button. The code behind this button would hide the form (Me.Visible =
False). This will allow the report to continue opening. Since the form is
still open, but hidden, the query will be able to get its parameter value
from the form. Close the form in the report's Close event.

If the user chooses to Cancel, the code in the Cancel button's Click event
would close the form (DoCmd.Close acForm, Me.Name). This also will allow the
report to continue opening. In the line of code after the one where you
opened the form, check to see if the form is still open. If so, continue to
open the report (i.e. do nothing). If the form isn't still open, Cancel the
opening of the report (Cancel = True). If you opened the report from code
(i.e. such as by clicking a button), this will cause a 2501 error in the
calling code. If so, just trap the error and ignore it.

--
Wayne Morgan
MS Access MVP

I asked several days ago how to create a parameter query where the user is
offered a combo box, and received a number of great responses, including
the one attached below. Following those instructions, I successfully
created a form that creates a query from a combo box.

At that point, I became more ambitious and wanted to finish the job,
adding that last step mentioned by John Vinson:

It's convenient to base a second Form or Report on the resulting query to
display the results; if you put a button on frmCriteria to launch that
form
or report, the user can enter the criterion and view the results in one
simple operation!

That is, I decided to add a report at the end, based on my
combo-box-generated query.

However, this is what happened: when I launch that report, it tries to run
the query again, asking for the parameter input of that form's combo box.
I wanted the report to just run based on the already-finished query, not
running it again.

Clearly I've missed something conceptually here. Can somebody straighten
me out?

Thanks!

John

----- Original Message -----
From: "John Spencer" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Friday, April 28, 2006 5:53 PM
Subject: Re: New to queries: parameter query with dropdown box?

Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm


A brief quote from an John Vinson (Access MVP) posting.

You'll need to create a small unbound Form (let's call it frmCriteria)
with
a Combo Box control (cboCrit) on it. Use the combo box wizard to select
the
table for the selections, and be
sure that the bound field of the combo is the value you want to use as a
criterion. Save this form.

Now use

=[Forms]![frmCriteria]![cboCrit]

as the criterion in your Query.

It's convenient to base a second Form or Report on the resulting query to
display the results; if you put a button on frmCriteria to launch that
form
or report, the user can enter the criterion and view the results in one
simple operation!

Quoting John Vinson

 

[JMF]

Excellent! Thanks very much, Wayne!

John

When the report opens it will run the query used as its RecordSource. If
that query is a parameter query and it can't find the value for the
parameter, it will prompt for it. For the query to get its value from the
form, the form must be open. If you're wanting to use this for a report, I
would recommend adding code to the Open event of the report to open the
form in Dialog mode. This will pause the code and the report's opening
until you close or hide the form. You will need 2 buttons on the form, a
Continue or Ok and a Cancel.

When the user is done making the necessary selection(s) they would click
the Ok button. The code behind this button would hide the form (Me.Visible
= False). This will allow the report to continue opening. Since the form
is still open, but hidden, the query will be able to get its parameter
value from the form. Close the form in the report's Close event.

If the user chooses to Cancel, the code in the Cancel button's Click event
would close the form (DoCmd.Close acForm, Me.Name). This also will allow
the report to continue opening. In the line of code after the one where
you opened the form, check to see if the form is still open. If so,
continue to open the report (i.e. do nothing). If the form isn't still
open, Cancel the opening of the report (Cancel = True). If you opened the
report from code (i.e. such as by clicking a button), this will cause a
2501 error in the calling code. If so, just trap the error and ignore it.

--
Wayne Morgan
MS Access MVP

I asked several days ago how to create a parameter query where the user is
offered a combo box, and received a number of great responses, including
the one attached below. Following those instructions, I successfully
created a form that creates a query from a combo box.

At that point, I became more ambitious and wanted to finish the job,
adding that last step mentioned by John Vinson:

It's convenient to base a second Form or Report on the resulting query
to
display the results; if you put a button on frmCriteria to launch that
form
or report, the user can enter the criterion and view the results in one
simple operation!

That is, I decided to add a report at the end, based on my
combo-box-generated query.

However, this is what happened: when I launch that report, it tries to
run the query again, asking for the parameter input of that form's combo
box. I wanted the report to just run based on the already-finished query,
not running it again.

Clearly I've missed something conceptually here. Can somebody straighten
me out?

Thanks!

John

----- Original Message -----
From: "John Spencer" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Friday, April 28, 2006 5:53 PM
Subject: Re: New to queries: parameter query with dropdown box?

Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm


A brief quote from an John Vinson (Access MVP) posting.

You'll need to create a small unbound Form (let's call it frmCriteria)
with
a Combo Box control (cboCrit) on it. Use the combo box wizard to select
the
table for the selections, and be
sure that the bound field of the combo is the value you want to use as a
criterion. Save this form.

Now use

=[Forms]![frmCriteria]![cboCrit]

as the criterion in your Query.

It's convenient to base a second Form or Report on the resulting query
to
display the results; if you put a button on frmCriteria to launch that
form
or report, the user can enter the criterion and view the results in one
simple operation!

Quoting John Vinson