Subreports based on user input from queries

[Guest]

Hoping someone can help me!!!!!
I have 4 queries which are needed to generate this one report. Query1 asks
for users input for a [PART] number, Queries 2 and 3 then reference this part
number for their records and finally Query4 is a UNION query to join the
records from Queries 2 & 3. When the queries are run independently of the
report itself I only have to input the [PART] number once as requested by
Query1, then 2,3 and 4 follow suit using the [PART] input. BUT, when I embed
these queries into reports it requeries Query1 over and over again asking for
input to generate the report. I have tried linking master and child fields
and that has no effect. The reports are layered as follows:

MainReport: Source is Query1 [PART] input needed
SubReport1(In MainReport Footer): Source is Query2
SubReport2(In SubReport1 Footer): Source is UNION Query 4

Is there a way to get the subreports to reference the main report [PART]? I
tried removing the reference to the Query1 [PART] field for the subreports
queries, but I can not get it to work with the UNION QUERY 4; it gives me an
ODBC call failed error message. Presumably because the program is getting
confused. Any suggestions???

 

[Duane Hookom]

Kick your development up a notch and don't use parameter prompts. Always use
controls on forms for users to enter criteria. Your subreport record sources
would have criteria like:

Forms!frmReportCriteria!cboPart

 

[Guest]

Thanks for your help Duane. Yes, the form input criteria works good but I'm
getting an error from somewhere and don't know why. I followed the directions
in the Access Help file called "Create A Form to Enter Report Criteria". The
error message I get is 'The expression you entered has a function name that
Microsoft Access can't find'. Even still, the report info is accurate. Do you
have a guess as to which function Access is referring to? Also I need to take
it a step further and have the report launch directly when the OK button is
clicked.

Kick your development up a notch and don't use parameter prompts. Always use
controls on forms for users to enter criteria. Your subreport record sources
would have criteria like:

Forms!frmReportCriteria!cboPart


--
Duane Hookom
MS Access MVP

Hoping someone can help me!!!!!
I have 4 queries which are needed to generate this one report. Query1 asks
for users input for a [PART] number, Queries 2 and 3 then reference this
part
number for their records and finally Query4 is a UNION query to join the
records from Queries 2 & 3. When the queries are run independently of the
report itself I only have to input the [PART] number once as requested by
Query1, then 2,3 and 4 follow suit using the [PART] input. BUT, when I
embed
these queries into reports it requeries Query1 over and over again asking
for
input to generate the report. I have tried linking master and child fields
and that has no effect. The reports are layered as follows:

MainReport: Source is Query1 [PART] input needed
SubReport1(In MainReport Footer): Source is Query2
SubReport2(In SubReport1 Footer): Source is UNION Query 4

Is there a way to get the subreports to reference the main report [PART]?
I
tried removing the reference to the Query1 [PART] field for the subreports
queries, but I can not get it to work with the UNION QUERY 4; it gives me
an
ODBC call failed error message. Presumably because the program is getting
confused. Any suggestions???

 

[Duane Hookom]

I am having trouble seeing what you have created. I usually have a form open
where users enter criteria and the click a button that opens the report. I
don't have any idea if this is how your solution is set up. Sometimes Help
makes suggestions that I detest so I don't use them.
--
Duane Hookom
MS Access MVP

Thanks for your help Duane. Yes, the form input criteria works good but
I'm
getting an error from somewhere and don't know why. I followed the
directions
in the Access Help file called "Create A Form to Enter Report Criteria".
The
error message I get is 'The expression you entered has a function name
that
Microsoft Access can't find'. Even still, the report info is accurate. Do
you
have a guess as to which function Access is referring to? Also I need to
take
it a step further and have the report launch directly when the OK button
is
clicked.

Kick your development up a notch and don't use parameter prompts. Always
use
controls on forms for users to enter criteria. Your subreport record
sources
would have criteria like:

Forms!frmReportCriteria!cboPart


--
Duane Hookom
MS Access MVP

Hoping someone can help me!!!!!
I have 4 queries which are needed to generate this one report. Query1
asks
for users input for a [PART] number, Queries 2 and 3 then reference
this
part
number for their records and finally Query4 is a UNION query to join
the
records from Queries 2 & 3. When the queries are run independently of
the
report itself I only have to input the [PART] number once as requested
by
Query1, then 2,3 and 4 follow suit using the [PART] input. BUT, when I
embed
these queries into reports it requeries Query1 over and over again
asking
for
input to generate the report. I have tried linking master and child
fields
and that has no effect. The reports are layered as follows:

MainReport: Source is Query1 [PART] input needed
SubReport1(In MainReport Footer): Source is Query2
SubReport2(In SubReport1 Footer): Source is UNION Query 4

Is there a way to get the subreports to reference the main report
[PART]?
I
tried removing the reference to the Query1 [PART] field for the
subreports
queries, but I can not get it to work with the UNION QUERY 4; it gives
me
an
ODBC call failed error message. Presumably because the program is
getting
confused. Any suggestions???

 

[Guest]

That's OK - I went back through eveything and reprogrammed it the way I think
it should work and it works fine now without any errors. BUT, I do have one
last question for you concerning this. If I'm using the
"Forms!frmReportCriteria!txtPart" as the criteria in my underlying queries,
do I still need to Link Master and Child for my subreports, or is that
redundant?

I am having trouble seeing what you have created. I usually have a form open
where users enter criteria and the click a button that opens the report. I
don't have any idea if this is how your solution is set up. Sometimes Help
makes suggestions that I detest so I don't use them.
--
Duane Hookom
MS Access MVP

Thanks for your help Duane. Yes, the form input criteria works good but
I'm
getting an error from somewhere and don't know why. I followed the
directions
in the Access Help file called "Create A Form to Enter Report Criteria".
The
error message I get is 'The expression you entered has a function name
that
Microsoft Access can't find'. Even still, the report info is accurate. Do
you
have a guess as to which function Access is referring to? Also I need to
take
it a step further and have the report launch directly when the OK button
is
clicked.

Kick your development up a notch and don't use parameter prompts. Always
use
controls on forms for users to enter criteria. Your subreport record
sources
would have criteria like:

Forms!frmReportCriteria!cboPart


--
Duane Hookom
MS Access MVP

Hoping someone can help me!!!!!
I have 4 queries which are needed to generate this one report. Query1
asks
for users input for a [PART] number, Queries 2 and 3 then reference
this
part
number for their records and finally Query4 is a UNION query to join
the
records from Queries 2 & 3. When the queries are run independently of
the
report itself I only have to input the [PART] number once as requested
by
Query1, then 2,3 and 4 follow suit using the [PART] input. BUT, when I
embed
these queries into reports it requeries Query1 over and over again
asking
for
input to generate the report. I have tried linking master and child
fields
and that has no effect. The reports are layered as follows:

MainReport: Source is Query1 [PART] input needed
SubReport1(In MainReport Footer): Source is Query2
SubReport2(In SubReport1 Footer): Source is UNION Query 4

Is there a way to get the subreports to reference the main report
[PART]?
I
tried removing the reference to the Query1 [PART] field for the
subreports
queries, but I can not get it to work with the UNION QUERY 4; it gives
me
an
ODBC call failed error message. Presumably because the program is
getting
confused. Any suggestions???

 

[Duane Hookom]

If you are using the Link Master/Child to filter the records displaying on
your subreport, I would remove the criteria from the subreport's record
source query.
--
Duane Hookom
MS Access MVP

That's OK - I went back through eveything and reprogrammed it the way I
think
it should work and it works fine now without any errors. BUT, I do have
one
last question for you concerning this. If I'm using the
"Forms!frmReportCriteria!txtPart" as the criteria in my underlying
queries,
do I still need to Link Master and Child for my subreports, or is that
redundant?

I am having trouble seeing what you have created. I usually have a form
open
where users enter criteria and the click a button that opens the report.
I
don't have any idea if this is how your solution is set up. Sometimes
Help
makes suggestions that I detest so I don't use them.
--
Duane Hookom
MS Access MVP

Thanks for your help Duane. Yes, the form input criteria works good but
I'm
getting an error from somewhere and don't know why. I followed the
directions
in the Access Help file called "Create A Form to Enter Report
Criteria".
The
error message I get is 'The expression you entered has a function name
that
Microsoft Access can't find'. Even still, the report info is accurate.
Do
you
have a guess as to which function Access is referring to? Also I need
to
take
it a step further and have the report launch directly when the OK
button
is
clicked.

:

Kick your development up a notch and don't use parameter prompts.
Always
use
controls on forms for users to enter criteria. Your subreport record
sources
would have criteria like:

Forms!frmReportCriteria!cboPart


--
Duane Hookom
MS Access MVP

Hoping someone can help me!!!!!
I have 4 queries which are needed to generate this one report.
Query1
asks
for users input for a [PART] number, Queries 2 and 3 then reference
this
part
number for their records and finally Query4 is a UNION query to join
the
records from Queries 2 & 3. When the queries are run independently
of
the
report itself I only have to input the [PART] number once as
requested
by
Query1, then 2,3 and 4 follow suit using the [PART] input. BUT, when
I
embed
these queries into reports it requeries Query1 over and over again
asking
for
input to generate the report. I have tried linking master and child
fields
and that has no effect. The reports are layered as follows:

MainReport: Source is Query1 [PART] input needed
SubReport1(In MainReport Footer): Source is Query2
SubReport2(In SubReport1 Footer): Source is UNION Query 4

Is there a way to get the subreports to reference the main report
[PART]?
I
tried removing the reference to the Query1 [PART] field for the
subreports
queries, but I can not get it to work with the UNION QUERY 4; it
gives
me
an
ODBC call failed error message. Presumably because the program is
getting
confused. Any suggestions???

 

[Guest]

Is one more efficient than the other?

If you are using the Link Master/Child to filter the records displaying on
your subreport, I would remove the criteria from the subreport's record
source query.
--
Duane Hookom
MS Access MVP

That's OK - I went back through eveything and reprogrammed it the way I
think
it should work and it works fine now without any errors. BUT, I do have
one
last question for you concerning this. If I'm using the
"Forms!frmReportCriteria!txtPart" as the criteria in my underlying
queries,
do I still need to Link Master and Child for my subreports, or is that
redundant?

I am having trouble seeing what you have created. I usually have a form
open
where users enter criteria and the click a button that opens the report.
I
don't have any idea if this is how your solution is set up. Sometimes
Help
makes suggestions that I detest so I don't use them.
--
Duane Hookom
MS Access MVP

Thanks for your help Duane. Yes, the form input criteria works good but
I'm
getting an error from somewhere and don't know why. I followed the
directions
in the Access Help file called "Create A Form to Enter Report
Criteria".
The
error message I get is 'The expression you entered has a function name
that
Microsoft Access can't find'. Even still, the report info is accurate.
Do
you
have a guess as to which function Access is referring to? Also I need
to
take
it a step further and have the report launch directly when the OK
button
is
clicked.

:

Kick your development up a notch and don't use parameter prompts.
Always
use
controls on forms for users to enter criteria. Your subreport record
sources
would have criteria like:

Forms!frmReportCriteria!cboPart


--
Duane Hookom
MS Access MVP

Hoping someone can help me!!!!!
I have 4 queries which are needed to generate this one report.
Query1
asks
for users input for a [PART] number, Queries 2 and 3 then reference
this
part
number for their records and finally Query4 is a UNION query to join
the
records from Queries 2 & 3. When the queries are run independently
of
the
report itself I only have to input the [PART] number once as
requested
by
Query1, then 2,3 and 4 follow suit using the [PART] input. BUT, when
I
embed
these queries into reports it requeries Query1 over and over again
asking
for
input to generate the report. I have tried linking master and child
fields
and that has no effect. The reports are layered as follows:

MainReport: Source is Query1 [PART] input needed
SubReport1(In MainReport Footer): Source is Query2
SubReport2(In SubReport1 Footer): Source is UNION Query 4

Is there a way to get the subreports to reference the main report
[PART]?
I
tried removing the reference to the Query1 [PART] field for the
subreports
queries, but I can not get it to work with the UNION QUERY 4; it
gives
me
an
ODBC call failed error message. Presumably because the program is
getting
confused. Any suggestions???

 

[Duane Hookom]

I would always choose the Link master/child if available. That way your
subreport will still work if you change the way the main report is
filtered.
--
Duane Hookom
MS Access MVP

Is one more efficient than the other?

If you are using the Link Master/Child to filter the records displaying
on
your subreport, I would remove the criteria from the subreport's record
source query.
--
Duane Hookom
MS Access MVP

That's OK - I went back through eveything and reprogrammed it the way I
think
it should work and it works fine now without any errors. BUT, I do have
one
last question for you concerning this. If I'm using the
"Forms!frmReportCriteria!txtPart" as the criteria in my underlying
queries,
do I still need to Link Master and Child for my subreports, or is that
redundant?

:

I am having trouble seeing what you have created. I usually have a
form
open
where users enter criteria and the click a button that opens the
report.
I
don't have any idea if this is how your solution is set up. Sometimes
Help
makes suggestions that I detest so I don't use them.
--
Duane Hookom
MS Access MVP

Thanks for your help Duane. Yes, the form input criteria works good
but
I'm
getting an error from somewhere and don't know why. I followed the
directions
in the Access Help file called "Create A Form to Enter Report
Criteria".
The
error message I get is 'The expression you entered has a function
name
that
Microsoft Access can't find'. Even still, the report info is
accurate.
Do
you
have a guess as to which function Access is referring to? Also I
need
to
take
it a step further and have the report launch directly when the OK
button
is
clicked.

:

Kick your development up a notch and don't use parameter prompts.
Always
use
controls on forms for users to enter criteria. Your subreport
record
sources
would have criteria like:

Forms!frmReportCriteria!cboPart


--
Duane Hookom
MS Access MVP

Hoping someone can help me!!!!!
I have 4 queries which are needed to generate this one report.
Query1
asks
for users input for a [PART] number, Queries 2 and 3 then
reference
this
part
number for their records and finally Query4 is a UNION query to
join
the
records from Queries 2 & 3. When the queries are run
independently
of
the
report itself I only have to input the [PART] number once as
requested
by
Query1, then 2,3 and 4 follow suit using the [PART] input. BUT,
when
I
embed
these queries into reports it requeries Query1 over and over
again
asking
for
input to generate the report. I have tried linking master and
child
fields
and that has no effect. The reports are layered as follows:

MainReport: Source is Query1 [PART] input needed
SubReport1(In MainReport Footer): Source is Query2
SubReport2(In SubReport1 Footer): Source is UNION Query 4

Is there a way to get the subreports to reference the main report
[PART]?
I
tried removing the reference to the Query1 [PART] field for the
subreports
queries, but I can not get it to work with the UNION QUERY 4; it
gives
me
an
ODBC call failed error message. Presumably because the program is
getting
confused. Any suggestions???