Reports, Subreport, crosstabs and passing parameters

[johnb]

Hi All

I have a unbound report which contains 4 sub reports which are bound to 4
crosstab queries and these are based on 4 queries. The question is can I have
a combo box on the unbound report to filter the sub reports. If so. How?

TIA
johnb

 

[Duane Hookom]

Combo boxes on reports have little or no use except displaying looked up
values.

Do you want to use the link master/child properties to limit the records in
every subreport to one specific value in a field?

 

[johnb]

Yes. I have several locations and need the sub reports to show the same
locations

johnb

 

[Duane Hookom]

I mentioned "'one' specific value in a field" and you mention "locations"
which to mean suggests multiple locations. Link master/child is limited to
only a single value of the field to filter the subreport.

Please try again to describe what you have and what you want to happen.

 

[johnb]

Hi Duane
Thank you for your effort.
I need to report on gender, age, ethnicity and issues by location. Location
being a town/city. I created an unboud report and inserted 4 sub reports to
it, this works fine but it gives me all Locations. I need to be able to
select a particular town/city.

Each of the 4 reports has a field Location_ID and its a long int. My initial
thinking was to have a combo box in the main report header and use that to
selection the desired location(town/city).

johnb

 

[Duane Hookom]

Combo boxes can't be manipulated in any way by a user on a report. Bind the
main report to the locations table and set the Link Master/Child of each
subreport to the Location_ID. You can limit the locations displayed in the
main report using any one of several different methods.

 

[johnb]

Hi Duane
I fiddled around this this yesterday and did what you suggested but I now
get an error message "Cannot use a Pass-through-query as a record source for
a subform or subreport" I've setup parameters both on the criteria row and in
Query-Parametrers and used the [forms]![frm_Locations]![txtWhereSeen] in
each. At the Report level it work just fine but when I insert them as
subreports I get the error message.
johnb

 

[Duane Hookom]

What do you mean by "when I insert them as subreports"? If you have the
Location_ID filtered in the main report and then set the Link Master/Child
properties, you don't need to set another filter in the subreports.

Can you tell use anything about the record sources of the subreports? Are
you setting the Column Headings properties of the subreport crosstabs?


--
Duane Hookom
Microsoft Access MVP

Hi Duane
I fiddled around this this yesterday and did what you suggested but I now
get an error message "Cannot use a Pass-through-query as a record source for
a subform or subreport" I've setup parameters both on the criteria row and in
Query-Parametrers and used the [forms]![frm_Locations]![txtWhereSeen] in
each. At the Report level it work just fine but when I insert them as
subreports I get the error message.
johnb

Hi Duane
Thank you for your effort.
I need to report on gender, age, ethnicity and issues by location. Location
being a town/city. I created an unboud report and inserted 4 sub reports to
it, this works fine but it gives me all Locations. I need to be able to
select a particular town/city.

Each of the 4 reports has a field Location_ID and its a long int. My initial
thinking was to have a combo box in the main report header and use that to
selection the desired location(town/city).

johnb

 

[johnb]

Duane, I've cracked it I was omitting the Column Heading property. It works
and I'm a happy boy again thanks to your prompting and assistance.

What do you mean by "when I insert them as subreports"? If you have the
Location_ID filtered in the main report and then set the Link Master/Child
properties, you don't need to set another filter in the subreports.

Can you tell use anything about the record sources of the subreports? Are
you setting the Column Headings properties of the subreport crosstabs?


--
Duane Hookom
Microsoft Access MVP

Hi Duane
I fiddled around this this yesterday and did what you suggested but I now
get an error message "Cannot use a Pass-through-query as a record source for
a subform or subreport" I've setup parameters both on the criteria row and in
Query-Parametrers and used the [forms]![frm_Locations]![txtWhereSeen] in
each. At the Report level it work just fine but when I insert them as
subreports I get the error message.
johnb

Hi Duane
Thank you for your effort.
I need to report on gender, age, ethnicity and issues by location. Location
being a town/city. I created an unboud report and inserted 4 sub reports to
it, this works fine but it gives me all Locations. I need to be able to
select a particular town/city.

Each of the 4 reports has a field Location_ID and its a long int. My initial
thinking was to have a combo box in the main report header and use that to
selection the desired location(town/city).

johnb


:

I mentioned "'one' specific value in a field" and you mention "locations"
which to mean suggests multiple locations. Link master/child is limited to
only a single value of the field to filter the subreport.

Please try again to describe what you have and what you want to happen.


--
Duane Hookom
MS Access MVP

Yes. I have several locations and need the sub reports to show the same
locations

johnb

:

Combo boxes on reports have little or no use except displaying looked up
values.

Do you want to use the link master/child properties to limit the records
in
every subreport to one specific value in a field?

--
Duane Hookom
Microsoft Access MVP


:

Hi All

I have a unbound report which contains 4 sub reports which are bound to
4
crosstab queries and these are based on 4 queries. The question is can
I have
a combo box on the unbound report to filter the sub reports. If so.
How?

TIA
johnb

 

[RyanG]

Is the column heading query property still a requirement in later versions of
Access? I am using Access 2000. I have a similar situation and my crosstab
query contains about 100 column headings. This would be very difficult to
type all those column headings.

Duane, I've cracked it I was omitting the Column Heading property. It works
and I'm a happy boy again thanks to your prompting and assistance.

What do you mean by "when I insert them as subreports"? If you have the
Location_ID filtered in the main report and then set the Link Master/Child
properties, you don't need to set another filter in the subreports.

Can you tell use anything about the record sources of the subreports? Are
you setting the Column Headings properties of the subreport crosstabs?


--
Duane Hookom
Microsoft Access MVP

Hi Duane
I fiddled around this this yesterday and did what you suggested but I now
get an error message "Cannot use a Pass-through-query as a record source for
a subform or subreport" I've setup parameters both on the criteria row and in
Query-Parametrers and used the [forms]![frm_Locations]![txtWhereSeen] in
each. At the Report level it work just fine but when I insert them as
subreports I get the error message.
johnb


:

Hi Duane
Thank you for your effort.
I need to report on gender, age, ethnicity and issues by location. Location
being a town/city. I created an unboud report and inserted 4 sub reports to
it, this works fine but it gives me all Locations. I need to be able to
select a particular town/city.

Each of the 4 reports has a field Location_ID and its a long int. My initial
thinking was to have a combo box in the main report header and use that to
selection the desired location(town/city).

johnb


:

I mentioned "'one' specific value in a field" and you mention "locations"
which to mean suggests multiple locations. Link master/child is limited to
only a single value of the field to filter the subreport.

Please try again to describe what you have and what you want to happen.


--
Duane Hookom
MS Access MVP

Yes. I have several locations and need the sub reports to show the same
locations

johnb

:

Combo boxes on reports have little or no use except displaying looked up
values.

Do you want to use the link master/child properties to limit the records
in
every subreport to one specific value in a field?

--
Duane Hookom
Microsoft Access MVP


:

Hi All

I have a unbound report which contains 4 sub reports which are bound to
4
crosstab queries and these are based on 4 queries. The question is can
I have
a combo box on the unbound report to filter the sub reports. If so.
How?

TIA
johnb