subreport not displaying fields

[JohnLute]

I have two tables with the following fields to report:

tblProfiles
txtProfileID (PK)
Description
Type

tblPKProfilesAssociations
txtProfileID (PK)
ProfilesAssociations (PK)
Quantity
Comments

tblProfiles is related to tblPKProfilesAssociations in a one-to-many
relationship and functions as something of a bill of material. This is a
tricky design. tblProfiles houses many types of entities that can relate to
each other. Hence the one-to-many relationship: one txtProfileID may relate
to many txtProfileIDs.

Now that you have the pertinent table design background; here is the
pertinent report design:

I have a subreport:
srptProfilesAssociations
RecordSource:
SELECT tblPKProfilesAssociations.*, tblProfiles.Description, tblProfiles.Type
FROM tblProfiles INNER JOIN tblPKProfilesAssociations ON
tblProfiles.txtProfileID = tblPKProfilesAssociations.txtProfileID;

My rptFinishedGoods uses this subreportwhich is linked:
Link Child Field: tblProfilesAssociations.ProfilesAssociations
Master Child Field: tblProfiles.txtProfileID

When I run rptFinishedGoods everything is fine except that the following two
fields do not display:
tblProfilesAssociations.Quantity
tblProfilesAssociations.Comments.

When I run srptProfilesAssociations all fields display. Only when I run
rptFinishedGoods do the two fields not display.

Do you see anything in the design that’s causing the fields to not display?
I can’t seem to work this one out. Any help would be greatly appreciated!

THANKS!!!

 

[Lynn Trapp]

SELECT tblPKProfilesAssociations.*, tblProfiles.Description,

tblProfiles.Type
FROM tblProfiles INNER JOIN tblPKProfilesAssociations ON
tblProfiles.txtProfileID = tblPKProfilesAssociations.txtProfileID;

John,
If you run this query by itself, do you get the expected data returned?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[JohnLute]

Hi, Lynn!!!

Thanks for the response!

Yes. The data returns as expected. The srptProfilesAssociations also returns
the expected data when executed, however, the rptFinishedGoods (master
report) returns everything except the "quantity" and "comments" fields.

 

[Lynn Trapp]

then the next question is, are you doing anything in the Open or Format
events of the report to hide the data?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[JohnLute]

Thanks, Lynn!

No. Nothing is coded for the Open or Format of either report.

I have to revise my previous claim. The data queried does NOT return as
expected. I've been a little confused by everything and I initially thought
it was OK.

I've reviewed this again and see where I was a bit off course with my
initial post. Ultimately, I'm trying to pull data from two tables but have
made a mess. Here are the tables again followed by a quesion:

tblProfiles
txtProfileID (PK)
Description
Type

tblPKProfilesAssociations
txtProfileID (PK)
ProfilesAssociations (PK)
Quantity
Comments

My subreport needs to return one tblProfiles.txtProfileID with many
tblPKProfilesAssociations.ProfilesAssociations. How can I code the
RecordSource of the subreport to do this AND pull the Description and Type
from tblProfiles so that they appear for each
tblPKProfilesAssociations.ProfilesAssociations?

I hope that's a little clearer!

 

[Lynn Trapp]

SELECT tblProfiles.Description, tblProfiles.Type,
tblPKProfilesAssociations.Quantity, tblPKProfilesAssociations.Comments
FROM tblProfiles Join tblProfiles.txtProfileID =
tblPKProfilesAssociations.txtProfileID;
--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html