subreport with parameter query

[JohnLute]

I have a subreport with a parameter query:

SELECT tblPKProfilesAssociations.ProfilesAssociations,
tblProfiles.Description, tblProfiles.Type,
tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments,
tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes,
tblFinishedGoods.Brand
FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID =
tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON
tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations
WHERE (((tblFinishedGoods.Brand)=[Enter Brand Name]));

This report runs fine however, when the main report is executed the
subreport prompts for a brand name but then returns nothing. In other words,
the main report is complete but the subreport is missing.

Does anyone see why this is happening?

Thanks!!!

 

[Allen Browne]

The most obvious reasons why the subreport might show no records would be
if:

a) The Brand field in tblFinishedGoods does not contain the data to match
what you enter. For example, if Brand is actually a lookup field, it might
be of type Number, so you would need to type the number and not the name to
get a match.

b) The LinkMasterFields/LinkChildFields properties of the subreport control
are set incorrectly, so there is no match.

c) The query is not understanding the data type correctly. This typically
happens for Number or Date fields rather than Text fields, so proably does
not apply to your case. The solution is to declare the parameter (Parameters
on Query menu, in query design.)

In any case, I have always found parameters on subreports annoying. As
Access runs the subreport again for each record in the main report, it asks
for the parameter again. You can avoid that if you open a fom in the
background, and enter the desired value there. Then in your query you refer
to the text box on the form, i.e. instead of [Enter Brand Name] you would
have something like this: [Forms].[Form1].[Brand]

 

[JohnLute]

Thanks Allen!

a), b), & c) are not the case - unfortunately. I reread my post and should
clarify that the subreport runs fine. I just doesn't return any data when the
main report is executed.

I use the form trick you described for other areas of my database. In this
area however, my design is a bit different and I wanted to simply use the
parameter query. Ugh. Looks like I need to develop a form to get this. I'm
growing weary of design work!

I've also experienced the multiple running of subreports that you described
below. It's too bad Access has this little "hiccup." I would be saved from so
much design work!

Thanks for your direction!

--
www.Marzetti.com

The most obvious reasons why the subreport might show no records would be
if:

a) The Brand field in tblFinishedGoods does not contain the data to match
what you enter. For example, if Brand is actually a lookup field, it might
be of type Number, so you would need to type the number and not the name to
get a match.

b) The LinkMasterFields/LinkChildFields properties of the subreport control
are set incorrectly, so there is no match.

c) The query is not understanding the data type correctly. This typically
happens for Number or Date fields rather than Text fields, so proably does
not apply to your case. The solution is to declare the parameter (Parameters
on Query menu, in query design.)

In any case, I have always found parameters on subreports annoying. As
Access runs the subreport again for each record in the main report, it asks
for the parameter again. You can avoid that if you open a fom in the
background, and enter the desired value there. Then in your query you refer
to the text box on the form, i.e. instead of [Enter Brand Name] you would
have something like this: [Forms].[Form1].[Brand]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a subreport with a parameter query:

SELECT tblPKProfilesAssociations.ProfilesAssociations,
tblProfiles.Description, tblProfiles.Type,
tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments,
tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes,
tblFinishedGoods.Brand
FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID =
tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON
tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations
WHERE (((tblFinishedGoods.Brand)=[Enter Brand Name]));

This report runs fine however, when the main report is executed the
subreport prompts for a brand name but then returns nothing. In other
words,
the main report is complete but the subreport is missing.

Does anyone see why this is happening?

 

[JohnLute]

Hi, Allen. I've created a form but have run into more trouble. I hope you can
help. I also adjusted the subreport's SQL:

SELECT tblPKProfilesAssociations.ProfilesAssociations,
tblProfiles.Description, tblProfiles.Type,
tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments,
tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes,
tblFinishedGoods.Brand
FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID =
tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON
tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations
WHERE
(((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]))
OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is
Null));

Everything is behaving, however, when the main report is executed it returns
everytihng except the subreport. I can't see what's wrong. Do I need to
adjust the main report's SQL?

I'm lost!

 

[Allen Browne]

If the subreport works on its own, then you have the query and form working
correctly.

If it returns no records as a subreport, it is probably badly linked with
the main report. Open the main report in design view, right-click the edge
of the subreport control, and choose Properties. Do the LinkMasterFields and
LinkChildFields properties make sense?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, Allen. I've created a form but have run into more trouble. I hope you
can
help. I also adjusted the subreport's SQL:

SELECT tblPKProfilesAssociations.ProfilesAssociations,
tblProfiles.Description, tblProfiles.Type,
tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments,
tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes,
tblFinishedGoods.Brand
FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID =
tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON
tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations
WHERE
(((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]))
OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is
Null));

Everything is behaving, however, when the main report is executed it
returns
everytihng except the subreport. I can't see what's wrong. Do I need to
adjust the main report's SQL?

I'm lost!

--
www.Marzetti.com

The most obvious reasons why the subreport might show no records would be
if:

a) The Brand field in tblFinishedGoods does not contain the data to match
what you enter. For example, if Brand is actually a lookup field, it
might
be of type Number, so you would need to type the number and not the name
to
get a match.

b) The LinkMasterFields/LinkChildFields properties of the subreport
control
are set incorrectly, so there is no match.

c) The query is not understanding the data type correctly. This typically
happens for Number or Date fields rather than Text fields, so proably
does
not apply to your case. The solution is to declare the parameter
(Parameters
on Query menu, in query design.)

In any case, I have always found parameters on subreports annoying. As
Access runs the subreport again for each record in the main report, it
asks
for the parameter again. You can avoid that if you open a fom in the
background, and enter the desired value there. Then in your query you
refer
to the text box on the form, i.e. instead of [Enter Brand Name] you would
have something like this: [Forms].[Form1].[Brand]

 

[JohnLute]

The subreport doesn't work on its own due to: WHERE
(((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]))
OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is
Null));

However, prior to that WHERE statement the subreport worked fine on its own.

I checked the child/master links again and they're fine.

Is the WHERE statement creating a conflict perhaps? I use a similar WHERE
statement in other reports and have encountered no problems. Only in this
instance as it's in the subreport.

--
www.Marzetti.com

If the subreport works on its own, then you have the query and form working
correctly.

If it returns no records as a subreport, it is probably badly linked with
the main report. Open the main report in design view, right-click the edge
of the subreport control, and choose Properties. Do the LinkMasterFields and
LinkChildFields properties make sense?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi, Allen. I've created a form but have run into more trouble. I hope you
can
help. I also adjusted the subreport's SQL:

SELECT tblPKProfilesAssociations.ProfilesAssociations,
tblProfiles.Description, tblProfiles.Type,
tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments,
tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes,
tblFinishedGoods.Brand
FROM (tblProfiles LEFT JOIN tblFinishedGoods ON tblProfiles.txtProfileID =
tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON
tblProfiles.txtProfileID = tblPKProfilesAssociations.ProfilesAssociations
WHERE
(((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]))
OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is
Null));

Everything is behaving, however, when the main report is executed it
returns
everytihng except the subreport. I can't see what's wrong. Do I need to
adjust the main report's SQL?

I'm lost!

--
www.Marzetti.com

The most obvious reasons why the subreport might show no records would be
if:

a) The Brand field in tblFinishedGoods does not contain the data to match
what you enter. For example, if Brand is actually a lookup field, it
might
be of type Number, so you would need to type the number and not the name
to
get a match.

b) The LinkMasterFields/LinkChildFields properties of the subreport
control
are set incorrectly, so there is no match.

c) The query is not understanding the data type correctly. This typically
happens for Number or Date fields rather than Text fields, so proably
does
not apply to your case. The solution is to declare the parameter
(Parameters
on Query menu, in query design.)

In any case, I have always found parameters on subreports annoying. As
Access runs the subreport again for each record in the main report, it
asks
for the parameter again. You can avoid that if you open a fom in the
background, and enter the desired value there. Then in your query you
refer
to the text box on the form, i.e. instead of [Enter Brand Name] you would
have something like this: [Forms].[Form1].[Brand]

 

[Allen Browne]

Access often gets confused by the data types.
Open your tblFinishedGoods table in design view, and see what Data Type the
Brand field is.

Then declare the parameter of that type in your query. In query design view,
choose Parameters on the Query menu. Access opens a dialog. If the field is
a Number of size Long, you would enter:
[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand] Long

If the combo is unbound, it also helps to set its Format property to General
Number or something that indicates Access should understand it as a number.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The subreport doesn't work on its own due to: WHERE
(((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]))
OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is
Null));

However, prior to that WHERE statement the subreport worked fine on its
own.

I checked the child/master links again and they're fine.

Is the WHERE statement creating a conflict perhaps? I use a similar WHERE
statement in other reports and have encountered no problems. Only in this
instance as it's in the subreport.

--
www.Marzetti.com

If the subreport works on its own, then you have the query and form
working
correctly.

If it returns no records as a subreport, it is probably badly linked with
the main report. Open the main report in design view, right-click the
edge
of the subreport control, and choose Properties. Do the LinkMasterFields
and
LinkChildFields properties make sense?

Hi, Allen. I've created a form but have run into more trouble. I hope
you
can
help. I also adjusted the subreport's SQL:

SELECT tblPKProfilesAssociations.ProfilesAssociations,
tblProfiles.Description, tblProfiles.Type,
tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments,
tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes,
tblFinishedGoods.Brand
FROM (tblProfiles LEFT JOIN tblFinishedGoods ON
tblProfiles.txtProfileID =
tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON
tblProfiles.txtProfileID =
tblPKProfilesAssociations.ProfilesAssociations
WHERE
(((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]))
OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])
Is
Null));

Everything is behaving, however, when the main report is executed it
returns
everytihng except the subreport. I can't see what's wrong. Do I need to
adjust the main report's SQL?

I'm lost!

--
www.Marzetti.com


:

The most obvious reasons why the subreport might show no records would
be
if:

a) The Brand field in tblFinishedGoods does not contain the data to
match
what you enter. For example, if Brand is actually a lookup field, it
might
be of type Number, so you would need to type the number and not the
name
to
get a match.

b) The LinkMasterFields/LinkChildFields properties of the subreport
control
are set incorrectly, so there is no match.

c) The query is not understanding the data type correctly. This
typically
happens for Number or Date fields rather than Text fields, so proably
does
not apply to your case. The solution is to declare the parameter
(Parameters
on Query menu, in query design.)

In any case, I have always found parameters on subreports annoying. As
Access runs the subreport again for each record in the main report, it
asks
for the parameter again. You can avoid that if you open a fom in the
background, and enter the desired value there. Then in your query you
refer
to the text box on the form, i.e. instead of [Enter Brand Name] you
would
have something like this: [Forms].[Form1].[Brand]

 

[JohnLute]

Brand is a text field. Not sure if this is the issue.

What about filters on the main report? Mine uses this:
([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID])

Could this be clashing with what I'm trying to do?


--
www.Marzetti.com

Access often gets confused by the data types.
Open your tblFinishedGoods table in design view, and see what Data Type the
Brand field is.

Then declare the parameter of that type in your query. In query design view,
choose Parameters on the Query menu. Access opens a dialog. If the field is
a Number of size Long, you would enter:
[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand] Long

If the combo is unbound, it also helps to set its Format property to General
Number or something that indicates Access should understand it as a number.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The subreport doesn't work on its own due to: WHERE
(((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]))
OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]) Is
Null));

However, prior to that WHERE statement the subreport worked fine on its
own.

I checked the child/master links again and they're fine.

Is the WHERE statement creating a conflict perhaps? I use a similar WHERE
statement in other reports and have encountered no problems. Only in this
instance as it's in the subreport.

--
www.Marzetti.com

If the subreport works on its own, then you have the query and form
working
correctly.

If it returns no records as a subreport, it is probably badly linked with
the main report. Open the main report in design view, right-click the
edge
of the subreport control, and choose Properties. Do the LinkMasterFields
and
LinkChildFields properties make sense?

Hi, Allen. I've created a form but have run into more trouble. I hope
you
can
help. I also adjusted the subreport's SQL:

SELECT tblPKProfilesAssociations.ProfilesAssociations,
tblProfiles.Description, tblProfiles.Type,
tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments,
tblPKProfilesAssociations.txtProfileID, tblProfiles.AssociationNotes,
tblFinishedGoods.Brand
FROM (tblProfiles LEFT JOIN tblFinishedGoods ON
tblProfiles.txtProfileID =
tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON
tblProfiles.txtProfileID =
tblPKProfilesAssociations.ProfilesAssociations
WHERE
(((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]))
OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])
Is
Null));

Everything is behaving, however, when the main report is executed it
returns
everytihng except the subreport. I can't see what's wrong. Do I need to
adjust the main report's SQL?

I'm lost!

--
www.Marzetti.com


:

The most obvious reasons why the subreport might show no records would
be
if:

a) The Brand field in tblFinishedGoods does not contain the data to
match
what you enter. For example, if Brand is actually a lookup field, it
might
be of type Number, so you would need to type the number and not the
name
to
get a match.

b) The LinkMasterFields/LinkChildFields properties of the subreport
control
are set incorrectly, so there is no match.

c) The query is not understanding the data type correctly. This
typically
happens for Number or Date fields rather than Text fields, so proably
does
not apply to your case. The solution is to declare the parameter
(Parameters
on Query menu, in query design.)

In any case, I have always found parameters on subreports annoying. As
Access runs the subreport again for each record in the main report, it
asks
for the parameter again. You can avoid that if you open a fom in the
background, and enter the desired value there. Then in your query you
refer
to the text box on the form, i.e. instead of [Enter Brand Name] you
would
have something like this: [Forms].[Form1].[Brand]

 

[Allen Browne]

If the main report is returning the correct records, then the filter on the
main report should not affect the subreport. It is certainly a good idea to
declare the parameters in the main report's query as well.

(Of course, the subreport will only show something when the record in the
main report has a match in the subreport.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Brand is a text field. Not sure if this is the issue.

What about filters on the main report? Mine uses this:
([tblProfiles.txtProfileID] = Forms![frmPKFilm].form![txtProfileID])

Could this be clashing with what I'm trying to do?


--
www.Marzetti.com

Access often gets confused by the data types.
Open your tblFinishedGoods table in design view, and see what Data Type
the
Brand field is.

Then declare the parameter of that type in your query. In query design
view,
choose Parameters on the Query menu. Access opens a dialog. If the field
is
a Number of size Long, you would enter:
[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]
Long

If the combo is unbound, it also helps to set its Format property to
General
Number or something that indicates Access should understand it as a
number.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The subreport doesn't work on its own due to: WHERE
(((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]))
OR ((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])
Is
Null));

However, prior to that WHERE statement the subreport worked fine on its
own.

I checked the child/master links again and they're fine.

Is the WHERE statement creating a conflict perhaps? I use a similar
WHERE
statement in other reports and have encountered no problems. Only in
this
instance as it's in the subreport.

--
www.Marzetti.com


:

If the subreport works on its own, then you have the query and form
working
correctly.

If it returns no records as a subreport, it is probably badly linked
with
the main report. Open the main report in design view, right-click the
edge
of the subreport control, and choose Properties. Do the
LinkMasterFields
and
LinkChildFields properties make sense?

Hi, Allen. I've created a form but have run into more trouble. I
hope
you
can
help. I also adjusted the subreport's SQL:

SELECT tblPKProfilesAssociations.ProfilesAssociations,
tblProfiles.Description, tblProfiles.Type,
tblPKProfilesAssociations.Quantity,
tblPKProfilesAssociations.Comments,
tblPKProfilesAssociations.txtProfileID,
tblProfiles.AssociationNotes,
tblFinishedGoods.Brand
FROM (tblProfiles LEFT JOIN tblFinishedGoods ON
tblProfiles.txtProfileID =
tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations
ON
tblProfiles.txtProfileID =
tblPKProfilesAssociations.ProfilesAssociations
WHERE
(((tblFinishedGoods.Brand)=[Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand]))
OR
((([Forms]![frmQueryReportProfilesAssociationsFGBrands]![cbBrand])
Is
Null));

Everything is behaving, however, when the main report is executed it
returns
everytihng except the subreport. I can't see what's wrong. Do I need
to
adjust the main report's SQL?

I'm lost!

--
www.Marzetti.com


:

The most obvious reasons why the subreport might show no records
would
be
if:

a) The Brand field in tblFinishedGoods does not contain the data to
match
what you enter. For example, if Brand is actually a lookup field,
it
might
be of type Number, so you would need to type the number and not the
name
to
get a match.

b) The LinkMasterFields/LinkChildFields properties of the subreport
control
are set incorrectly, so there is no match.

c) The query is not understanding the data type correctly. This
typically
happens for Number or Date fields rather than Text fields, so
proably
does
not apply to your case. The solution is to declare the parameter
(Parameters
on Query menu, in query design.)

In any case, I have always found parameters on subreports annoying.
As
Access runs the subreport again for each record in the main report,
it
asks
for the parameter again. You can avoid that if you open a fom in
the
background, and enter the desired value there. Then in your query
you
refer
to the text box on the form, i.e. instead of [Enter Brand Name] you
would
have something like this: [Forms].[Form1].[Brand]