Two subreports

[reverentcreature]

Hi all,

Im having problems with printing two subreports on a report. The main report contains a Job and a JOBID field. The two subreports are linked JobLabourJobID and the other JobPartJobID.

The report displays the correct data and rest of the database works as it should but when you go to preview the report the subreports appear multiple times. For example if the jobParts subreport contains two parts for the job then the whole subform appears twice in the detail section.

I have seen one solution to place the subreports in the footer section but this makes the report look wrong when previewed especially as both subreports have the CanGrow property set on them.

Any advice would be really appreciated.

 

[dkinley]

Hi all,
For example if the jobParts subreport contains two parts for the job then the whole subform appears twice in the detail section.

I am going to claim that what you are seeing is normal. I would be willing to guess that if you opened the subform then there would be two rows in the detail section. Thus, in the main form - WYSIWYG.

If you are attempting to do some sort of summary, then you will probably need to use some sort of grouping/totalling in the subreport's query to have the subreport present itself once in the report.

For an oversimplified example, if I had a main report and a subreport was a summary of expenditures - then I would only want it to report the bottom line. However, the subreport listed every expenditure to the job. I would probably modify the underlying query to total the cost for me (and possibly group based on expenditure type - personnel, supplies, etc.) Once the subreport reported the single total, or grouped items, then that is what I should see in the main report.

Hope that helps.
-dK

 

[reverentcreature]

Hi Dkinley,

I did consier the option of summarising the rows but because its in invoice I really want to list each individual item. If I open the subreport it lists all the parts for every job thats ever been done. The data only appears once though. Because its linked directly to the table.

When it appears on the job invoice it lists all the parts for the JobID but then I get the subreport appearing repeatedly down the page the number of times for which there are parts. So for example if there are two parts instead of seeing the subreport listing the two parts I see two subreports listing the two parts! Same with labour. Are yu sure this is normal and there is no way round this?

Kind regards,

Jason

 

[dkinley]

So for example if there are two parts instead of seeing the subreport listing the two parts I see two subreports listing the two parts!

Ohhhhhhhhhhhhhhhhhhhh. If sureport A is listing both parts, and subreport B is listing both parts - then no, you are correct (as opposed to one listing one part and the other listing the other part). In my experience, this is NOT normal. It sounds as if it is linked correctly and everything.

Is the main report based on a query, too? If so, if you run just the query - does it have two instances of the data that is linked? This could cause something like this to occur. Other than that - outside of some bug - I have no idea since I have never run into this problem working with subreports.

As a last option, you could post a sample of it and I could take a look at it.

-dK

 

[reverentcreature]

Hi there,

You were right the query when run had more entries that there should have been. Before I created the subreports I had ALL the tables I needed in the main query for the main report Invoice.

But when I created the subreports A (tblJobParts and tblParts) and and subreport B (tblJobLabour and tblJobs). I should have taken those tables out of the Invoice query but I didnt.

When I went into the main Invoice query and took out the tables (the 4 now in the two subforms) the report opened up correctly.

It must have been creating duplication in the detail section.

This is what the report was looking like before The subreports were repeating onto a second page for each job. 4 lots of labour causing 4 lots of subforms!
https://blgw6w.bay.livefilestore.co...zdQoPSuMEvAqEupeObLLTHcimsWfMjNCA/invoice.bmp

Many thanks,

Jason

 

[dkinley]

Before I created the subreports I had ALL the tables I needed in the main query for the main report Invoice.

... I should have taken those tables out of the Invoice query but I didnt.

Yep. Had that happen to me before - makes ya wanna Der! yourself.

Glad it is working for you now.

-dK