Subreport linkage problem (1 Viewer)

Sebbyy · Jul 25, 2018

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 · Jul 25, 2018

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 · Jul 25, 2018

MajP said:
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 · Jul 25, 2018

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 · Jul 25, 2018

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 · Jul 25, 2018

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

Sebbyy · Jul 26, 2018

Code:

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_ · Jul 26, 2018

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 · Jul 26, 2018

Code:

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 · Jul 26, 2018

How many times does FID 198 show in this query?

Sebbyy · Jul 26, 2018

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 · Jul 26, 2018

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 · Jul 26, 2018

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 · Jul 26, 2018

GinaWhipp said:
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 · Jul 26, 2018

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

Sebbyy · Jul 26, 2018

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

GinaWhipp · Jul 26, 2018

That is why I asked to see the Relationships window.

Mark_ · Jul 26, 2018

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?

Subreport linkage problem (1 Viewer)

Sebbyy

Registered User.

MajP

You've got your good things, and you've got mine.

Sebbyy

Registered User.

Attachments

GinaWhipp

AWF VIP

Sebbyy

Registered User.

GinaWhipp

AWF VIP

Sebbyy

Registered User.

Mark_

Longboard on the internet

Sebbyy

Registered User.

GinaWhipp

AWF VIP

Sebbyy

Registered User.

GinaWhipp

AWF VIP

GinaWhipp

AWF VIP

Sebbyy

Registered User.

GinaWhipp

AWF VIP

Sebbyy

Registered User.

GinaWhipp

AWF VIP

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread