Multiple Reports (1 Viewer)

Grimwadec

New member
Local time
Today, 05:43
Joined
Nov 5, 2012
Messages
7
In Access 2010 I have a Sub-report embedded in a Report. The sub-report works fine. The problem is that if there is 5 records in the sub-report I get 5 versions of the Main Report. My user has a work around, he just tells the printer to print page 1 only. That's coolbut I want to (know how to) fix it. Can anybody help please?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:43
Joined
Feb 19, 2002
Messages
42,970
The problem is that the main report recordSource query includes a join to the table used for the subreport. Remove the extraneous table from the query and you will get the correct result.
 

Grimwadec

New member
Local time
Today, 05:43
Joined
Nov 5, 2012
Messages
7
Thanks Pat. I Think your suggestion is working but can't be sure because when I open the report, Access cracks a hissy fit and I have to shut it down.
 

Grimwadec

New member
Local time
Today, 05:43
Joined
Nov 5, 2012
Messages
7
In fact I find that when I am able to stop Access from having a fit the report still duplicated
 

JHB

Have been here a while
Local time
Today, 13:43
Joined
Jun 17, 2012
Messages
7,732
What is the record source for the main report, (if a query, show the query string)?
The record source for the main report, must only contain 1 row, else you'll get so many report as row in the record source.
 

Grimwadec

New member
Local time
Today, 05:43
Joined
Nov 5, 2012
Messages
7
In fact I removed the table that was in both queries, and instead populated the fields (that I thought I needed the duplicated table for) using Allen Browne's ELookup
 

JHB

Have been here a while
Local time
Today, 13:43
Joined
Jun 17, 2012
Messages
7,732
I do not know Allen Browne's ELookup, but if you got the report to show what you want, then it is fine! :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:43
Joined
Feb 19, 2002
Messages
42,970
The main report should not be showing data from the subreport so I'm confused by what you were trying to accomplish. That means that I don't understand how eLookup() could have solved the problem. How are you deciding which of the many-side records to show? While eLookup() is more efficient than the built-in dLookup(), it is still an inefficient way to get data for multiple records since it runs a separate query for each row in the Recordset. So, if your report shows a thousand records, a lookup will run a thousand times. That's a lot of overhead. The join would be far better but we're back to the join causing duplication and I'm lost again as to why you would be doing this.
 

Users who are viewing this thread

Top Bottom