Sub-Report Problem

I have a sub-report that prints "exactly" as it should, but then repeats 
many, many times.

The stand alone sub-report prints two pages.
The sub-report in the main report prints 4226 pages (duplicates of two 
pages, 2113 times).
The reports are NOT linked.
The sub-report is located in the Detail section of the main report.
I select to print this sub-report from a form, and then make the appropriate 
controls visible/not visible depending on the type of report I want.  It 
works great, just too many pages on the sub-report.  All other main reports 
(as selected from the Form, work fine).

Can someone suggest what may be causing the report to print duplicates of 
the two page sub-report so many times?

 


0
bw
10/23/2007 1:27:33 PM
access.reports 4434 articles. 0 followers. Follow

5 Replies
1012 Views

Similar Articles

[PageSpeed] 40

bw wrote:

>I have a sub-report that prints "exactly" as it should, but then repeats 
>many, many times.
>
>The stand alone sub-report prints two pages.
>The sub-report in the main report prints 4226 pages (duplicates of two 
>pages, 2113 times).
>The reports are NOT linked.
>The sub-report is located in the Detail section of the main report.
>I select to print this sub-report from a form, and then make the appropriate 
>controls visible/not visible depending on the type of report I want.  It 
>works great, just too many pages on the sub-report.  All other main reports 
>(as selected from the Form, work fine).


Because the subreport is in the detail section, it  should
repeat as many times as there are records in the main
report's record source table/query.

-- 
Marsh
MVP [MS Access]
0
Marshall
10/23/2007 2:57:13 PM
"Marshall Barton" <marshbarton@wowway.com> wrote in message 
news:dp2sh3dikm51o40opucu0c9mmbto3touvp@4ax.com...
> bw wrote:
>
>>I have a sub-report that prints "exactly" as it should, but then repeats
>>many, many times.
>>
>>The stand alone sub-report prints two pages.
>>The sub-report in the main report prints 4226 pages (duplicates of two
>>pages, 2113 times).
>>The reports are NOT linked.
>>The sub-report is located in the Detail section of the main report.
>>I select to print this sub-report from a form, and then make the 
>>appropriate
>>controls visible/not visible depending on the type of report I want.  It
>>works great, just too many pages on the sub-report.  All other main 
>>reports
>>(as selected from the Form, work fine).
>
>
> Because the subreport is in the detail section, it  should
> repeat as many times as there are records in the main
> report's record source table/query.
>
> -- 
> Marsh
> MVP [MS Access]

That makes sense, now that you mention it.
So I used the value I get from the Form to select a single item from the 
query that has unique values, and it works!

However, this seems like kind of a hokey way of doing this.  I imagine there 
is a more elegant solution to this, but if I move the subreport anywhere 
else, it just doesn't work right.  For example, other sections/headers where 
I move it will only show one column of a two column report, and other 
sections/headers don't show anything at all, etc.

Do you have some more advice?

Bernie 


0
bw
10/23/2007 4:05:45 PM
bw wrote:
>"Marshall Barton" wrote
>> bw wrote:
>>
>>>I have a sub-report that prints "exactly" as it should, but then repeats
>>>many, many times.
>>>
>>>The stand alone sub-report prints two pages.
>>>The sub-report in the main report prints 4226 pages (duplicates of two
>>>pages, 2113 times).
>>>The reports are NOT linked.
>>>The sub-report is located in the Detail section of the main report.
>>>I select to print this sub-report from a form, and then make the 
>>>appropriate
>>>controls visible/not visible depending on the type of report I want.  It
>>>works great, just too many pages on the sub-report.  All other main 
>>>reports
>>>(as selected from the Form, work fine).
>>
>>
>> Because the subreport is in the detail section, it  should
>> repeat as many times as there are records in the main
>> report's record source table/query.
>>
>
>That makes sense, now that you mention it.
>So I used the value I get from the Form to select a single item from the 
>query that has unique values, and it works!
>
>However, this seems like kind of a hokey way of doing this.  I imagine there 
>is a more elegant solution to this, but if I move the subreport anywhere 
>else, it just doesn't work right.  For example, other sections/headers where 
>I move it will only show one column of a two column report, and other 
>sections/headers don't show anything at all, etc.


I think you're losing me now.  Which query did you use the
form taxt box as a criteria?  Normally, you only need to
filter the main report and let the Link Master/Child
properties take care of selecting the appropriate subreport
records.  But, you said the the subreport "is not linked".
This means that the subreport sould be the same for every
record in the main report.  The only way this would make
much sense is if the subreport contains some kind of boiler
plate or independently calculated values.

Given your original qurstion, I guess you are probably
talking about filtering the main report's record source
query.  If that's the case, then you should remove the
criteria from the query and open the report using the
OpenReport method's WhereCondition argument (see VBA Help
for details).  The code would be something like:

DoCmd.OpenReport "reportname", acViewPreview, _
		WhereCondition:= "somefield=" & Me.thetextbox

-- 
Marsh
MVP [MS Access]
0
Marshall
10/23/2007 5:04:16 PM
"Marshall Barton" <marshbarton@wowway.com> wrote in message 
news:sk9sh3l4a2hdgecknejnaq4vbmelej7t7a@4ax.com...
> bw wrote:
>>"Marshall Barton" wrote
>>> bw wrote:
>>>
>>>>I have a sub-report that prints "exactly" as it should, but then repeats
>>>>many, many times.
>>>>
>>>>The stand alone sub-report prints two pages.
>>>>The sub-report in the main report prints 4226 pages (duplicates of two
>>>>pages, 2113 times).
>>>>The reports are NOT linked.
>>>>The sub-report is located in the Detail section of the main report.
>>>>I select to print this sub-report from a form, and then make the
>>>>appropriate
>>>>controls visible/not visible depending on the type of report I want.  It
>>>>works great, just too many pages on the sub-report.  All other main
>>>>reports
>>>>(as selected from the Form, work fine).
>>>
>>>
>>> Because the subreport is in the detail section, it  should
>>> repeat as many times as there are records in the main
>>> report's record source table/query.
>>>
>>
>>That makes sense, now that you mention it.
>>So I used the value I get from the Form to select a single item from the
>>query that has unique values, and it works!
>>
>>However, this seems like kind of a hokey way of doing this.  I imagine 
>>there
>>is a more elegant solution to this, but if I move the subreport anywhere
>>else, it just doesn't work right.  For example, other sections/headers 
>>where
>>I move it will only show one column of a two column report, and other
>>sections/headers don't show anything at all, etc.
>
>
> I think you're losing me now.  Which query did you use the
> form taxt box as a criteria?  Normally, you only need to
> filter the main report and let the Link Master/Child
> properties take care of selecting the appropriate subreport
> records.  But, you said the the subreport "is not linked".
> This means that the subreport sould be the same for every
> record in the main report.  The only way this would make
> much sense is if the subreport contains some kind of boiler
> plate or independently calculated values.
>
> Given your original qurstion, I guess you are probably
> talking about filtering the main report's record source
> query.  If that's the case, then you should remove the
> criteria from the query and open the report using the
> OpenReport method's WhereCondition argument (see VBA Help
> for details).  The code would be something like:
>
> DoCmd.OpenReport "reportname", acViewPreview, _
> WhereCondition:= "somefield=" & Me.thetextbox
>
> -- 
> Marsh
> MVP [MS Access]

Thanks Marsh,

The form gives me a value to use in the  main report's query...something 
like  IIf([Forms]![boxModules]![PW]=4,PrintSubReport,PrintMainReport)

The main form uses a different query than the subreport.  The main form has 
2113 records (as pointed out earlier), and the subreport only has 154 
records.

The subreport is using the same fields as the main report, but if I'm 
suppose to link the two, I wouldn't know how to do that.  The fields 
containted in both reports are [Mnemonic] and [Description], but if I link 
these fields, I get duplicate records.

It makes sense to me NOT to link, but what do I know...

Thanks again for your help.


0
bw
10/23/2007 6:17:57 PM
bw wrote:
>"Marshall Barton" wrote
>> bw wrote:
>>>"Marshall Barton" wrote
>>>> bw wrote:
>>>>
>>>>>I have a sub-report that prints "exactly" as it should, but then repeats
>>>>>many, many times.
>>>>>
>>>>>The stand alone sub-report prints two pages.
>>>>>The sub-report in the main report prints 4226 pages (duplicates of two
>>>>>pages, 2113 times).
>>>>>The reports are NOT linked.
>>>>>The sub-report is located in the Detail section of the main report.
>>>>>I select to print this sub-report from a form, and then make the
>>>>>appropriate
>>>>>controls visible/not visible depending on the type of report I want.  It
>>>>>works great, just too many pages on the sub-report.  All other main
>>>>>reports (as selected from the Form, work fine).
>>>>
>>>>
>>>> Because the subreport is in the detail section, it  should
>>>> repeat as many times as there are records in the main
>>>> report's record source table/query.
>>>>
>>>
>>>That makes sense, now that you mention it.
>>>So I used the value I get from the Form to select a single item from the
>>>query that has unique values, and it works!
>>>
>>>However, this seems like kind of a hokey way of doing this.  I imagine 
>>>there
>>>is a more elegant solution to this, but if I move the subreport anywhere
>>>else, it just doesn't work right.  For example, other sections/headers 
>>>where
>>>I move it will only show one column of a two column report, and other
>>>sections/headers don't show anything at all, etc.
>>
>>
>> I think you're losing me now.  Which query did you use the
>> form taxt box as a criteria?  Normally, you only need to
>> filter the main report and let the Link Master/Child
>> properties take care of selecting the appropriate subreport
>> records.  But, you said the the subreport "is not linked".
>> This means that the subreport sould be the same for every
>> record in the main report.  The only way this would make
>> much sense is if the subreport contains some kind of boiler
>> plate or independently calculated values.
>>
>> Given your original qurstion, I guess you are probably
>> talking about filtering the main report's record source
>> query.  If that's the case, then you should remove the
>> criteria from the query and open the report using the
>> OpenReport method's WhereCondition argument (see VBA Help
>> for details).  The code would be something like:
>>
>> DoCmd.OpenReport "reportname", acViewPreview, _
>> WhereCondition:= "somefield=" & Me.thetextbox
>>
>
>The form gives me a value to use in the  main report's query...something 
>like  IIf([Forms]![boxModules]![PW]=4,PrintSubReport,PrintMainReport)
>
>The main form uses a different query than the subreport.  The main form has 
>2113 records (as pointed out earlier), and the subreport only has 154 
>records.
>
>The subreport is using the same fields as the main report, but if I'm 
>suppose to link the two, I wouldn't know how to do that.  The fields 
>containted in both reports are [Mnemonic] and [Description], but if I link 
>these fields, I get duplicate records.


I guess I just haven't figured out what this report is doing
or how that value is used in the main report's record source
query.  

The point of linking the subreport is to narrow the
subreport down from 2113 records to 154 records based on
some information either by a value in the main report
record(s?) or by some calculated value.  This avoids having
criteria in the subreport's record source query to do the
filtering.  Since you are not using the Link Maaster/Child
properties, how are you filtering the subreport's records?

That expression just doesn't look like something that's used
to filter the data in a query.  In any case, if you have a
value in some text box on the form and the query has some
field with a criteria like:
	=Forms!theform.sometextbox
then you can remove that criteria from the main report's
query and use the OpenReport approach I outlined earlier.  I
thought this was the question we were trying to address.

-- 
Marsh
MVP [MS Access]
0
Marshall
10/23/2007 8:40:23 PM
Reply:

Similar Artilces:

Report that display percentage below certain level
I got a spreadsheet contain inventory number from remote site. I need to run a report to display when inventory below a certain percentage. I try to use privot table to display the infomration. But it doesn't display everything below 40%. Is there anyway you can enter an percentage and display all remote site that below certain percentage (Eample: like 35%) on a report? Thanks. TSS, A pivot table groups like items, and makes a calculation on each group -- not what you need. You need a filter (Excel's way of saying "query" in database parlance). If your table...

Problem of viewing the text in Visio 2003
hi all, I've a visio 2003 doc with most of the texts in font size of 6pt or 5pt. I setup to 150% zoom to view it. It has no viewing problem so far. However, when I open up the doc today, all the texts are displayed like a rectangular block. I can't see each character at all. Only the text in 10pt or higher can be display correctly. Or if I increase the zoom to 300%, then I can see the texts. And I found that all of my docs have the same display problem. Can anyone help? Many thx. With me it works. Has the font changed, or the display settings in the control panel ? Changing ...

printing transparency problems still
In a previous post, said publisher has problems with transp. Would installing a new graphics card cause a problem. I tried using the transp. on pictures, and it still gives me the problem. It is alright till I go to print. In print preview looks fine, I haave the printer set up to have a separate print pre view, when it does that, you can see the picture is going to be blotchy. I have unstalled and reinstalled the drivers, without any luck. Would it be the printer or the program? Trying to figure this out where I did not have the problem before. Could their be a setting in publisher I have...

Inventory Turnover Rate reporting?
Greetings -- I'm looking to get my stores Turn Rate for various departments and am doing this by using Annual COGS / Avg value of Inventory (same 12-month period). The COGS figures are easy to get from w/in RMS using several of the sales reports w/ some filters applied, however the avg inventory value seems like a bit of a challenge...I can see my current inventory value based on the # of items currently in stock and their cummulative cost -- but don't see any way to get this as a historical figure...any ideas? Anyone else have a better way of calculating Turn Rate w/in RMS? ...

Sheet name problem
HI while i was renameing a sheet as n1 it was coming as N1. ANYONE KNOWS THE REASON FOR IT THANKS --- Message posted from http://www.ExcelForum.com/ Did you have the caps lock on or hit the shift key by mistake. Can you reproduce it in another worksheet? "gmangad <" wrote: > > HI > > while i was renameing a sheet as n1 it was coming as N1. ANYONE KNOWS > THE REASON FOR IT > > THANKS > > --- > Message posted from http://www.ExcelForum.com/ -- Dave Peterson ec35720@msn.com ...

Working directory problems
Hello, My application runs from C:\Program Files\Superna\ConfigNet\Bin (a.k.a Bin Directory) directory. My shared dll are in C:\Program Files\Superna\Common Files (a.k.a CommonFiles Directory) directory. My application dllz are in the Bin Directory. My application uses dll files both from my Bin Directory and my CommonFiles Directory. My application shortcut exists both in Start->Programs->Superna and on the user's desktop. User can doubleclick an nfg file so the application will run and load the nfg file (my network file). In order to support this behaviour I did the following...

Printer problem
I am running a Brother HL-1230 printer from a computer under Windows XP Professional. Nearly every time I send something to print, the lights flash as it receives data and then the lights go out and nothing happens. The second attempt is always successful. I have re-installed the Brother printer driver. Any ideas what is going on and how to get over the problem please? Bill R Bill R wrote: > I am running a Brother HL-1230 printer from a computer under > Windows XP Professional. Nearly every time I send something to > print, the lights flash as it receives d...

Calender consolidated report
Hi, There is requirment from one of our user. He wants to calculate how many hours he is spending only attending the meetings. He wants to get consolidated monthly or weekly reports like that. Is there is any way or tools available??? Thanks It's possible to export free busy using getfreebusy method, but even with this it cannot distinguish whether the user was not available due to meeting, vac etc. James Chong (MVP) MCSE | M+, S+, MCTS, Security+ msexchangetips.blogspot.com On Apr 12, 7:34 am, "RANG" <hair...@gmail.com> wrote: > Hi, > > There is requir...

VBA problem to open a file which is allready opened
Hey guys After a horrible nights sleep, caused by spooky VBA macro-gosts that are harassing me and chasing me through the bright and flowering fields of Visual Basic and into the dark woods of soul-searching lack of VBA-qualifications, I have no other choice than ask You to conjur one of these small devils in mind, by helping me solve my VBA-problem: I try - and manage (hurray!!) - to create a macro that copy a specified range from one workbook, then open a specified new one and finaly paste the data (pluss - of course - do some other stuff) into this predefinied workbook/sheet. My problem i...

Reporting services update automatic data?
Hi, i need to know if a report wrote in VisualStudio.NET for CRM automatic updated the information? In Crystal i remember, have 2 options when save a report and one of them, not updated data. And one more question? How add my report to reports menu in opportunity form? Thanks for any help. ------=_NextPart_0001_0A094AF3 Content-Type: text/plain Content-Transfer-Encoding: 7bit <Miguel@discussions.microsoft.com> wrote: > i need to know if a report wrote in VisualStudio.NET for CRM automatic > updated the information? In Crystal i remember, have 2 options when save a >...

Hyperlink to child report is LOCALHOST when exported to Excel
We have a report that has a link to a child report for each row. We used the "Go to report" for the field's Action. Everything works fine through the browser. However, when we export this to Excel, the links turn into http://localhost.. which does not work for the user. How can we fix this? Thanks, Bernado ...

Purchase Advice Report with SOP exploded through BOM
Does anyone have info about a Purchase Advice Report that includes components of Bill of Materials that are generated by Sales Order Processing finished goods orders? Thanks, Dan Lapp ...

Worksheet().Range.Calculate Problem
I am having a major problem with these two lines of code in my otherwise great reporting tool: Worksheets("Goals Tracker").Select Worksheets("Goals Tracker").Range("G3:GA712").Calculate These 2 lines freezes up my computer to the point that I can't use any other application on my computer. It takes forever (over an hour, the last time I let it run complete) to calculate one sheet in the workbook. I need to find out if there is another way to get this done faster. Any help will be appreaciated. There's no need to select the worksheet in o...

Problems with Outlook 2000 after upgrading to Windows XP SP2
Hi everyone. We have been running Exchange 5.5 with Outlook 2000 clients on Windows 2000 and Windows XP machines for a few years. Everything has always worked perfectly. We recently upgraded a few machines to Windows XP SP2 and noticed problems with Outlook 2000. Outlook 2000 installed on a Windows XP SP2 hangs all the time when exiting. When I close Outlook 2000 on one of these machines there is a 50-50 chance that it will exit properly. When it does close properly you have to hot CTRL- ALT-DELETE, choose Microsoft Outlook (listed twice for some weird reason), and click END TASK. Cli...

connection problems after returning from screen saver
When I leave Outlook 2002 running in Windows XP, and return to the logon screen, Outlook times out while checking for new messages. I have 2 email accounts setup. One is Hotmail the other is Verizon.net. The hotmail account goes offline and the verizon account can't connect to the server. To solve this, I have to exit outlook and restart the program. Any ideas what is causing this and how to fix it? Thanks. ...

problem in opening excel 2003
hi, myself Debashis whenever I am opening excel 2003, it is opening having rows in right side and column number starting from right side of all sheets. but if I create the file first, as like new> New Microsoft Excel Worksheet, it is opening normally pls advice thanks in advance Tools>Options>International Set left to right. Gord Dibben MS Excel MVP On Fri, 26 Dec 2008 11:48:46 +0530, "Debashis" <debashis2004@gmail.com> wrote: >hi, myself Debashis > >whenever I am opening excel 2003, it is opening having rows in right side >and column number s...

Error in report pls help
Dear friends, I receive an error msg "Microsoft Access can't find the field 'unitweight' referred to in your expression" allthough the field is there and belongs to the record source. When I run the sql query which represents the record source I can find all the records and fields and nothing seems to be wrong? Inside the report I refer to that field 'unitweight' inside an expression from the detail 'on print' event. Can anyone help me pls? Thanks Catalin If you don't have the field used on the report then Access will not be aware of the fie...

Logon Problem
have microsoft windows xp professional version 2002 / network setup; my station is administrator; cannot open microsoft outlook; error is called "Personal Address Book Logon" which says, Your Personal Address Book could not be opened. The file is either not accessible or is not personal address book. Please advise. Thank you. ...

Exchange Categorizer Problem.
I've read many usenet posts and websites regarding this problem, and from what i can figure out, its a problem, and there are many solutions for it, but no one solution. I have tried almost everything to figure out this, so I'm posting here in hopes someone will know whats causing it. First, the problem: Some emails are getting stuck in the categorizer (see below), particularly for one user, but it is happening for other users. Not all emails for this one user have the problem, are there is no rhyme nor reason that I can see. It is not: a) Any particular destination (multiple emails s...

Automating a report
Can you do a .bat file to schedule a report to run at specific time? Owner wants to come in in the morning and have several reports already ran and waiting for him. Thanks Amy, I cannot speak to a batch file, but take a look at a program called "Quick Macros" (http://www.quickmacros.com). I have a customer that uses this program to run a macro that will run reports on a schedule and print them, so they are waiting on him when he arrives in his office. Program license is 40.00 and updates are free. Kinnard "Amy" wrote: > Can you do a .bat file to schedule a rep...

Mailbox Move Problem
I have two Exchange 2000 servers in a Windows 2000 domain. I used Active Directory Exchange Tasks to move a mailbox from one server to the other. I archived the mailbox so that it would be as small as possible to move. The wizard said it moved the mailbox, no problem - seemed simple enough. However, when I try to access the mailbox by setting up a new profile to the new server, I get an "Outlook cannot open the folder" message and it does not open up the mail store. If I use OWA as a quick way to check the mailbox (\\server\exchange\username) I get a "Page Cannot Be Displ...

How to add field to report from the bound table?
Hi, All I have report (data source is table) when i built report i selected specific fields as usual,now i need to update report and add one field which is in a bound table, but it wasn't selected initially, I set in the property window Control Ssource to a field in the table, but when i try to print report it shows "Enter Parameter Value" and show Table!FieldName. Please, help since report fields doesn't have this particular field How can i add field from the table (data source for the report)? First, the field you want to add must be in the table or query that is t...

copy&paste problem
good lord, after 7 years of using MS Pub 2000, today it will not copy and paste between pub documents. i can open a new pub blank, draw a text box, type some text, copy that text, open another new pub blank, draw a text box, and it will not paste the copied text into the new doc. it won't copy objects, pictures, old stuff, new stuff, nada. i was going to restore with my office 2000 cd, but it said it was service pack 1, and i knew we've been on service pack 2 for quite a while. any suggestions would be appreciated. thanks, pam What happens when you try? -- JoAnn Paules MVP ...

WORKAROUND
Problem description: A full install of Microsoft Office 2003 with Business Contact Manager will fail when opening Outlook 2003 for the first time with an error dialog showing: Object Reference not set to an instance of an object. Contact Business Contact Manager Support. When this error-dialog is dismissed, a "Serious Error" dialog will be seen indicating a problem with the Business Contact Manager. When that dialog is dismissed, another dialog will be seen indicating a problem with Microsoft Outlook itself. If Norton AntiVirus is installed, another dialog will ap...

Outlook 2002 Coonection Problem
I used outlook 2002 from the day it was available in the market. It worked like a charm! However, about a week or so ago, my Outlook fail to connect to my Exchange Server. So, I deleted the account added with the hope of fixing the broken links or to refresh the connection. So I added the Name of Exchange Server and the Username and clicked on Check Name, then it came up with cannot connect to Exchange Server. To date, I have tried several things but no luck. Two of my collegues are having the same problem. But for others Outlook works just fine, so its definitely not the problem wi...