Paramater query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chi - 16 Nov 2007 22:22 GMT

Hi,

Running queries from a form is very good! I learned it from the Access
Tips!!!!

We can run query by using a list on the combo box on the form.
---------------------------------------------------------------
Is there a way to run a query from the form and result will show on the
report, not the query because I need to have a nice report to submit?

Thanks
Chi

Reply to this Message

fredg - 17 Nov 2007 00:06 GMT

> Hi,
>
[quoted text clipped - 8 lines]
> Thanks
> Chi

You'll have to adapt the below to your needs.
Let's assume you wish to report on a specific company.

Create a query that displays all companies records.
Create a report, using this query as it's record source.

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
CompanyID field and the Company Name.
Name the Combo Box 'FindCompany'.
Set it's Bound column to 1.
Set the Column Width property to 0";1"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

In the Report's Record Source (the Query) [CompanyID] field criteria
line write:
forms!ParamForm!FindCompany

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the selection of the Company.
Click the command button and then report will run.
When the report closes, it will close the form.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Chi - 19 Nov 2007 17:15 GMT

Thank you Fred and Marshall,

It works very well!!!!!!! Excellent.
________________
Another concern, please
Would you please show me to create CANCEL button that will close out the
ParamForm without asking data for the combo box?

Ex: I opened the report and clicked the X to close the ParamForm since I
don't want to run it that time, I got a message "
forms!ParamForm!FindCompany".

Thank you
Chi

> > Hi,
> >
[quoted text clipped - 44 lines]
> Click the command button and then report will run.
> When the report closes, it will close the form.

Reply to this Message

fredg - 19 Nov 2007 18:23 GMT

> Thank you Fred and Marshall,
>
[quoted text clipped - 59 lines]
>> Click the command button and then report will run.
>> When the report closes, it will close the form.

What version of Access?
Access 2000 or newer?

You don't need a cancel button.
Change the code in the Report's Open Event to:

DoCmd.OpenForm "ParamForm", , , , , acDialog
If Not CurrentProject.AllForms("ParamForm").IsLoaded Then
Cancel = True
End If

The report will not run nor will you get the parameter prompt if the
form ParamForm has been closed.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Chi - 19 Nov 2007 19:50 GMT

Hi Fredg,

Thanks for your response.

I am using Access 2007

I am sorry that I my question wasn't clear.

I would like to have a Cancel button on the ParamForm.

EX: If I want to run the report, I select the Company name from the drop
down list then click the Command Button/Then I will see the report. It is
perfect!

However, after opening the report (The ParamForm always open as well), I
don't want to do anything so I have to close the ParaForm. The problem is as
soon as I click the x on the form to make it close, I get the parameter
prompt.

I would like to close out the form without getting the "parameter prompt"

------
Or Would you show me to change the code in the Report's Close Event to make
the report close without getting the "parameter prompt" if I don't select the
Company name from the drop down list.

Thanks
Chi
------------------------

> > Thank you Fred and Marshall,
> >
[quoted text clipped - 73 lines]
> The report will not run nor will you get the parameter prompt if the
> form ParamForm has been closed.

Reply to this Message

fredg - 19 Nov 2007 20:23 GMT

> Hi Fredg,
>
[quoted text clipped - 103 lines]
>> The report will not run nor will you get the parameter prompt if the
>> form ParamForm has been closed.

Did you not even TRY my solution?

You can add a command button to your form if you wish.
Code it's click event:
DoCmd.Close acForm, "ParamForm"
The above will close the form (just as though you clicked the close
"X" on the form).

BUT YOU STILL MUST CODE THE REPORT'S OPEN EVENT, AS I WROTE.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Chi - 20 Nov 2007 14:27 GMT

Good Morning Fredg,

I did try your solution- Changed the code in the Report's Open Event to:

DoCmd.OpenForm "ParamForm", , , , , acDialog
If Not CurrentProject.AllForms("ParamForm").IsLoaded Then
Cancel = True
End If

However, It didn't work right.

Ex: After opening the report (The ParamForm always open as well), I selected
a Company name from the drop down list and clicked the command button.

It didn't do anything -The report didn't run. Please help.

Thanks
Chi

> > Hi Fredg,
> >
[quoted text clipped - 113 lines]
>
> BUT YOU STILL MUST CODE THE REPORT'S OPEN EVENT, AS I WROTE.

Reply to this Message

Chi - 20 Nov 2007 15:32 GMT

Hi Fredg,

Thank you so much, I got it.

> > Hi Fredg,
> >
[quoted text clipped - 113 lines]
>
> BUT YOU STILL MUST CODE THE REPORT'S OPEN EVENT, AS I WROTE.

Reply to this Message

Marshall Barton - 17 Nov 2007 00:57 GMT

>We can run query by using a list on the combo box on the form.
>---------------------------------------------------------------
>Is there a way to run a query from the form and result will show on the
>report, not the query because I need to have a nice report to submit?

Just set the report's record source to the query you want to
use.

If you have one report that you want to use with several
different queries, then pass the query name to the report in
the OpenReport method's OpenArgs argument. The report can
then set its own record source in its Open event procedure:
Me.RecordSource = Me.OpenArgs

OTOH, I have found that, in a properly normalized database,
there is very little, if any, need to have different queries
for a single report.

Signature

Marsh
MVP [MS Access]

Reply to this Message