Subreport linkage problem

[Sebbyy]

Hi,

So I have a report and subreport linked on a long integer "FID" and I have currently got the main report set on a particular FID for testing purposes while I've designed it. However, when I added the subreport that is linked on "FID", access nearly crashes as it pulls every single record for the subreport instead of only the ones associated with the 'FID'.

Has anyone had this problem? Any recommendations?

Thanks

 

[MajP]

Has anyone had this problem?

No. Not if you have the correct master / child links. It will not randomly fail.

Any recommendations?

Ensure you have the proper master / child links for the subform control. Post these properties.

 

[Sebbyy]

No. Not if you have the correct master / child links. It will not randomly fail.

Ensure you have the proper master / child links for the subform control. Post these properties.

See attached.

 

[GinaWhipp]

Hmm, does the recordsource of the Main Report have the FID more than once? If it does then the subreport will repeat for every time that FID appears. You need to make sure the FID only appears for one record.

 

[Sebbyy]

I thought that was probably the problem after reading your post but I went back and none of the queries underlying the reports have duplicate FIDs.


I think opens on the single FID then access freaks out and opens all????

 

[GinaWhipp]

Hmm, please post the Recordsource of the Main Report in SQL View.

 

[Sebbyy]

SELECT Fam.FID, Fam.Oncogemonic_Family_ID, Fam.FamilyName, Fam.SourceName, Fam.Article, Fam.JournalName, Fam.LabName, Fam.Contact, Fam.Pedigree_Attachment, Fam.CountryName, StateID.State, Fam.Descent, Fam.Comments_Family, Fam.Individual, Fam.Date_Edited_Family, Fam.FGID, Fam.Main_Fam, Fam.NucleotideNumber, Fam.Variant, Fam.PJcDNA_Change, Fam.cDNA_Change, Fam.PJChromosome_Position, Fam.Chromosome_Position, Fam.rs, Fam.Exon, Fam.ClinVar, Fam.Pathogenicity, Fam.AminoAcid, Fam.[Protein Change], Fam.FunctionalRegion, Fam.FunctionalRegion2, Fam.MutationType
FROM Fam LEFT JOIN StateID ON Fam.State = StateID.StateID
WHERE (((Fam.FID)=198));

 

[Mark_]

What is the record source for the child? Does the child actually have a valid FID that would allow you to work with it?

 

[Sebbyy]

SELECT Individual.PID, Individual.FID, Individual.OncogenomicsID, Individual.[Epi ID], Individual.[NSW ID], GenerationID.Generation, GenPositionID.GenPosition, Individual.Proband, GenderID.Gender, Individual.Age_at_last_update, Individual.Date_of_age_update, Individual.Age_at_death, Individual.BirthDay, Individual.BirthMonth, Individual.BirthYear, Individual.DeathDay, Individual.DeathMonth, Individual.DeathYear, Individual.[DNA available], Individual.PathReport, Individual.Comments_Individual, Individual.Date_Edited_Individual, Imutationsq.IGID, Imutationsq.Confirmation, Imutationsq.Main_Ind, Imutationsq.Main_Fam, Imutationsq.NucleotideNumber, Imutationsq.Variant, Imutationsq.PJcDNA_Change, Imutationsq.cDNA_Change, Imutationsq.PJChromosome_Position, Imutationsq.Chromosome_Position, Imutationsq.rs, Imutationsq.Exon, Imutationsq.ClinVar, Imutationsq.Pathogenicity, Imutationsq.AminoAcid, Imutationsq.[Protein Change], Imutationsq.FunctionalRegion, Imutationsq.FunctionalRegion2, Imutationsq.MutationType
FROM GenderID RIGHT JOIN (GenPositionID RIGHT JOIN (GenerationID RIGHT JOIN (Individual LEFT JOIN Imutationsq ON Individual.PID = Imutationsq.PID) ON GenerationID.GID = Individual.Generation) ON GenPositionID.GPID = Individual.GenPosition) ON GenderID.GeID = Individual.Gender;

 

[GinaWhipp]

How many times does FID 198 show in this query?

 

[Sebbyy]

19 times in the query that is record source of the child report.


I should mention there is a subreport inside the child report as well, however, the linkage is working perfectly between those two.

 

[GinaWhipp]

Umm, if the FID shows 19 times in the query (recordsource) for the main report than the subreport will print 19 times. It can only show once in the query (recordsource) for the main report in order to get the subreport to stop repeating.

 

[GinaWhipp]

I should have also mentioned that not so sure about your table design. Sounds more like a spreadsheet (but I could be wrong). Please post your relationships.

 

[Sebbyy]

Umm, if the FID shows 19 times in the query (recordsource) for the main report than the subreport will print 19 times. It can only show once in the query (recordsource) for the main report in order to get the subreport to stop repeating.

Ok - I needed to limit it to main mutations then redesign the multiple mutation part.


Thank you.

 

[GinaWhipp]

I am going to say yes. I am also going say you need to look at the design of your tables.

 

[Sebbyy]

The reason you may think the tables are screwed as all those queries are combinations of many tables. The base tables work well.

 

[GinaWhipp]

That is why I asked to see the Relationships window.

 

[Mark_]

For debugging, I'd start by having one table for each parent and child. Once you get those working, start linking back in your associated tables until you figure out what is causing your relationships to go off.

Also, are any of your tables intended as 1 to 1 relationships?