Paramater query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...

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.
 
Chi said:
We can run query by using a list on the combo box on the form.
Click to expand...


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.
 
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




fredg said:
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
Click to expand...

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.
Click to expand...
 
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

fredg said:
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
Click to expand...

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.
Click to expand...
Click to expand...

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.
 
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
------------------------
fredg said:
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

fredg said:
On Fri, 16 Nov 2007 14:22:01 -0800, Chi wrote:

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

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.
Click to expand...
Click to expand...

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.
Click to expand...
 
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
------------------------
fredg said:
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

:

On Fri, 16 Nov 2007 14:22:01 -0800, Chi wrote:

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

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.
Click to expand...

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.
Click to expand...
Click to expand...

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.
 
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,

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
------------------------
fredg said:
On Mon, 19 Nov 2007 09:15:03 -0800, Chi wrote:

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

:

On Fri, 16 Nov 2007 14:22:01 -0800, Chi wrote:

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

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.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


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.
Click to expand...
Click to expand...

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.
Click to expand...
 
Hi Fredg,

Thank you so much, I got it.

fredg said:
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
------------------------
fredg said:
On Mon, 19 Nov 2007 09:15:03 -0800, Chi wrote:

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

:

On Fri, 16 Nov 2007 14:22:01 -0800, Chi wrote:

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

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.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


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.
Click to expand...
Click to expand...

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.
Click to expand...
 
Back
Top