SubReport Causes Main Report to Print Twice (XP SP2)

Topic

New Reply

I have a report with several subreports. All of the subreports except one behave as they should: multiple entries in the subreport print and do not cause the whole report to print twice. However, I have one subreport that is based on a query that pulls data that is between 2 dates and when there is more than entry in this subreport for the parent, the whole parent and subreport sections print twice.

I’ve attached one page which shows the duplication.

Other subreports in this report are the Contacts name (Phil Skilman is in that section), Distributor Sales. If there are multiple entries there, I do not get duplication. Just the Contacted Info causes the duplication.

Where have I messed up?

Reply | Quote

Replies

We’d have to see the database instead of a fragment of a report. Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote

Hans, do you want me to leave some data in or what? Or do you want just the structure?

Reply | Quote

Leave just enough data to demonstrate the problem. Replace sensitive data with dummy values.

Reply | Quote

Hans, I’ve stripped the db down. However, it is too big according to the error report I just got. It is 394K (zipped). I don’t know that I can make it any smaller. What do I do now?

The report that is causing my problem is called: DistList-CONTACTED

Thank you for all your help!!!

Reply | Quote

Did you follow all the instructions in the post I referred to (zip the database, convert to Access 97 format)?

Reply | Quote

You can remove all database objects that are not relevant to the problem.
I’ve never yet seen a database that couldn’t be stripped down far enough.

Reply | Quote

My apologies. I hadn’t seen the link on how to strip down the db.

Here it is now.

Thank you again.

Reply | Quote

Unfortunately, your zip file is empty when I download or open it.

Reply | Quote

You know, Hans, I am not normally so inept . Honest!

Here it is again and it is large enough that there should be data in it.

Reply | Quote

The subreports have nothing to do with the problem, it’s the record source of the main report. If there are multiple contact notes for the same distname, the query DistList1A-NameContacted will return multiple records for that distname.

One workaround is to group the report by distname, and to place the subreports in the group header instead of in the detail section – see DistList-CONTACTED V1.
Another option is to modify the query so that it returns unique distnames – see DistList-CONTACTED V2, it uses DistList1A-NameContacted2 as record source.

See attached version.

Reply | Quote

Hans, thank you for your quick response. I will look at your solutions later today. However, I was wondering how come the grouping I already have on the report works for the Contacts but not the CONTACTED notes? Just curious.

You’re the best!

Reply | Quote

Your report DistList-CONTACTED is sorted but not grouped on DistName. Its record source DistList1A-NameContacted is also sorted but not grouped on DistName. So why would Access group on DistName?

Reply | Quote

Maybe I’ve mis-understood the attached screen which I think says that I am grouping on DistName. I clicked the little box in the upper left corner of the whole report and looked at the Grouping area. Am I nuts?????

Reply | Quote

Your screenshot says that the repors is sorted (Ascending) on DistName.
Since both the Group Header and Group Footer properties are set to No, the report is *not* grouped on DistName. In order to group a report on a field, at least one of these two has to be set to Yes. That is what I did in one of the examples in the version I posted.

Reply | Quote

I thought that if the Group On value was set to Each Value that meant it was grouped. Didn’t realize that I had to use the Group Header or Footer too.

Thanks for clearing that up.

Reply | Quote

Hans,

THANK YOU!!!!

I chose to do the header solution and you made it clear and easy enough for me to accomplish! I couldn’t quite understand the query solution.

Now my report is perfect.

I really appreciate the time and effort you took in solving my problem. It is nice to know that there are people so willing to help.

Reply | Quote