Criteria from Quary used in Report

[Meghan M.]

I designed a query using data from 1 table. The criteria is [Enter ID
Number:].

Then, I designed a report off of the query and it works like it should by
asking [Enter ID] since the report should be about a specific ID.

But, when I add subreports into the report (under detail heading), it asks
[Enter ID] twice. If I don't put anything in the second time the report
still looks fine.

How do I get rid of the second occurence of [Enter ID]?

 

[fredg]

I designed a query using data from 1 table. The criteria is [Enter ID
Number:].

Then, I designed a report off of the query and it works like it should by
asking [Enter ID] since the report should be about a specific ID.

But, when I add subreports into the report (under detail heading), it asks
[Enter ID] twice. If I don't put anything in the second time the report
still looks fine.

How do I get rid of the second occurence of [Enter ID]?

Use a form to enter the parameter in.
You can use a combo box to select the correct ID rather than guessing.
As long as the form is open, any query that needs the same parameter
will use it without further prompting.

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 each Report's Record Source [CompanyID] field criteria line write:
forms!ParamForm!FindCompany

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

Code the main 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.
All sub reports that need the same parameter value will have it
without further prompting.
When the report closes, it will close the form.

 

[Meghan M.]

Fredg- thanks for the quick response. I understand the idea behind your post
and got parts of it to work but I am having trouble with some of the codes.

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"

***Ok here

Add a Command Button to the form.
Code the button's click event:
Me.Visible = False

***MS Access didn't recognize the macro. Can I use a minimize macro here?
I am not sure where to put Me.Visible = False code and what it means (sorry,
codes are new to me!).

Name this form 'ParamForm'.

***Ok here

In each Report's Record Source [CompanyID] field criteria line write:
forms!ParamForm!FindCompany

***Where do I put this code? Do I put it in the Report Record Source and
all of the places Company ID appears? If the table that the report is built
from is called 'Company Information'- do I replace 'Company Information' in
the Report's Record Source with forms!ParamForm!FindCompany or do I put that
code as the source on Company ID properties?

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

***Used Open Form Macro

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

***Used Close Form Macro

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.
All sub reports that need the same parameter value will have it
without further prompting.
When the report closes, it will close the form.

***Things seems to be running but doesn't recognize the record source and
says to select an existing table or query.

--
Thanks-
Meghan M.

I designed a query using data from 1 table. The criteria is [Enter ID
Number:].

Then, I designed a report off of the query and it works like it should by
asking [Enter ID] since the report should be about a specific ID.

But, when I add subreports into the report (under detail heading), it asks
[Enter ID] twice. If I don't put anything in the second time the report
still looks fine.

How do I get rid of the second occurence of [Enter ID]?

Use a form to enter the parameter in.
You can use a combo box to select the correct ID rather than guessing.
As long as the form is open, any query that needs the same parameter
will use it without further prompting.

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 each Report's Record Source [CompanyID] field criteria line write:
forms!ParamForm!FindCompany

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

Code the main 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.
All sub reports that need the same parameter value will have it
without further prompting.
When the report closes, it will close the form.

 

[fredg]

On Tue, 27 Feb 2007 19:19:00 -0800, Meghan M. wrote:

See my comments in-line below....

Fredg- thanks for the quick response. I understand the idea behind your post
and got parts of it to work but I am having trouble with some of the codes.

***Ok here

***MS Access didn't recognize the macro. Can I use a minimize macro here?
I am not sure where to put Me.Visible = False code and what it means (sorry,
codes are new to me!).

I suspect you are trying to write the code I gave you directly on the
property sheet line. That's not where it goes.

Let's use the PaamForm's Command Button click event
Me.Visible = False
as an example.

To write the code, display the ParamForm's Command Button's property
sheet.
Click on the Event tab.
On the Click event line, write:
[Event Procedure]
Then click on the little button with the 3 dots that appears on that
line.
When the VBA code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those lines write

Me.Visible = False

Exit the code window.

Now do the same thing for each of the other code events I mentioned
above using the appropriate event.

Name this form 'ParamForm'.

***Ok here

In each Report's Record Source [CompanyID] field criteria line write:
forms!ParamForm!FindCompany

***Where do I put this code? Do I put it in the Report Record Source and
all of the places Company ID appears? If the table that the report is built
from is called 'Company Information'- do I replace 'Company Information' in
the Report's Record Source with forms!ParamForm!FindCompany or do I put that
code as the source on Company ID properties?

This is not code.
It does not go in the report,
It goes in the query that is used as the record source for the
report.
You must first create a query using your "Company Information" table
as the query's record source.

Here's how.
Click on Queries on the Main Database folder.
Then click on New.
When the new query dialog opens select Design View. Click OK.
Select the name of the table that is to be the record source of the
query "Company Information" ) from the Show Table box. Click Add.
If you need additional tables in the query, add them now.
Then click Close.
Make sure the relationships between each table in the query is
correct.

Drag each field you wish to show in the report from the table box onto
the grid.

Then while still in query design view, on the criteria row of the
CompanyID column write:

forms!ParamForm!FindCompany

Save and name the query.

Make this query the record source for your Report.

***Used Open Form Macro

Please do not use a Macro. Use the same [Event Procedure] method I
showed you above to write the code in the Report's Open event.

***Used Close Form Macro

Please do not use a Macro. Use the same [Event Procedure] method I
showed you above to write the code in the Report's Close event.

***Things seems to be running but doesn't recognize the record source and
says to select an existing table or query.

If you have properly done all the above, it will run properly.

Have fun.

 

[Meghan M.]

It works!!!! Thanks so much!!
--
Thanks-
Meghan M.

On Tue, 27 Feb 2007 19:19:00 -0800, Meghan M. wrote:

See my comments in-line below....

Fredg- thanks for the quick response. I understand the idea behind your post
and got parts of it to work but I am having trouble with some of the codes.

***Ok here

***MS Access didn't recognize the macro. Can I use a minimize macro here?
I am not sure where to put Me.Visible = False code and what it means (sorry,
codes are new to me!).

I suspect you are trying to write the code I gave you directly on the
property sheet line. That's not where it goes.

Let's use the PaamForm's Command Button click event
Me.Visible = False
as an example.

To write the code, display the ParamForm's Command Button's property
sheet.
Click on the Event tab.
On the Click event line, write:
[Event Procedure]
Then click on the little button with the 3 dots that appears on that
line.
When the VBA code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those lines write

Me.Visible = False

Exit the code window.

Now do the same thing for each of the other code events I mentioned
above using the appropriate event.

Name this form 'ParamForm'.

***Ok here

In each Report's Record Source [CompanyID] field criteria line write:
forms!ParamForm!FindCompany

***Where do I put this code? Do I put it in the Report Record Source and
all of the places Company ID appears? If the table that the report is built
from is called 'Company Information'- do I replace 'Company Information' in
the Report's Record Source with forms!ParamForm!FindCompany or do I put that
code as the source on Company ID properties?

This is not code.
It does not go in the report,
It goes in the query that is used as the record source for the
report.
You must first create a query using your "Company Information" table
as the query's record source.

Here's how.
Click on Queries on the Main Database folder.
Then click on New.
When the new query dialog opens select Design View. Click OK.
Select the name of the table that is to be the record source of the
query "Company Information" ) from the Show Table box. Click Add.
If you need additional tables in the query, add them now.
Then click Close.
Make sure the relationships between each table in the query is
correct.

Drag each field you wish to show in the report from the table box onto
the grid.

Then while still in query design view, on the criteria row of the
CompanyID column write:

forms!ParamForm!FindCompany

Save and name the query.

Make this query the record source for your Report.

***Used Open Form Macro

Please do not use a Macro. Use the same [Event Procedure] method I
showed you above to write the code in the Report's Open event.

***Used Close Form Macro

Please do not use a Macro. Use the same [Event Procedure] method I
showed you above to write the code in the Report's Close event.

***Things seems to be running but doesn't recognize the record source and
says to select an existing table or query.

If you have properly done all the above, it will run properly.

Have fun.