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
872 Views

Similar Articles

[PageSpeed] 47

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:

Fixed row and header report
Hi, I have a requirement to have a report - SQL 2005 Reporting services ... SQL output: ID DateId FiscalYear FiscalPeriod TotalBooksTaken ReturnedBooks Store 1 20101001 09/10 2010-07 989 756 Store 1 1 20101001 09/10 2010-07 456 343 Store 2 1 20101001 09/10 2010-07 1456 1343 Store 3 2 YTD 09/10 ...

Duplicating MainForm, SubForm and Sub-SubForm
Hello Everyone, I have an Order Entry Form composed of a MainForm, a SubForm and 2 Sub- Subforms. The SubForm is a continuous form. The two Sub-SubForms are children of a SubForm via a foreign key (which is the Primary Key of a SubForm). There are some instances when customers revised their order by changing just the amount of orders or by just removing one item and the rest are the same. To do this, I would like to keep the initial order (record) and create another record by duplicating the first order then change the amount (or remove an item whatever the case maybe) on the new record. The...

Interstore transfer problems
I have two store in an HQ environment. Last week all interstore transfers generated at either store stopped showing up at their destination. I can find them in HQ. They are listed as closed. I checked the HQ config and both boxes are checked. I searched the archives, but this appears to be a little different as it is all transfers that don't show up. Transfers that originate in HQ appear to fine. hi Jim, what version you are using ? that problem is a known bug in rms v1.2 and earlier version of rms v1.3. You need to apply a patch to your application. But it dosen't solve the o...

Cost basis problem
I am having a weird problem in Money. In my portfolio view, there is one mutual fund that shows a 0 cost basis if I display the data by investment type, but shows the correct cost basis if I display by account name. What's the story there? Any idea how I might fix this fun little problem? ...

Report Issue
I have a report that prints all of the Employees Names for a group on a preprinted paper form. I am trying to duplicate the paper form in an Access 2003 report. All works great with a subform report with a block set up in columns and there are an even number of records. When there is an odd number of records I need either a blank record input or a few lines drawn on the subreport. HOW CAN I MAKE THIS HAPPEN based on the number of records in the query. -- Hansford D. Cornett ...

Outlook 2000 Sub folder
Hi I am using outlook 2000 and I have a created a subfolder in my inbox however I am unable to view the folder My Shortcuts on the left hand side of the screen where all my other sub folders are located. In the navigation bar at the top of my pane there is an icon called "move to Folder" and the folder is located here. I am unable to find the location of the folder therefore unable to open the folder or move it to "My shortcuts" I am in need of your expertise. I am ok with computers and can follow instructions so please please please help. Thanking you in ...

X Report Printing
Here's my problem... I am unable to print a X, Z or ZZ report from my office printer. It will print to the receipt printer but I would like to be able to print it from the office on 8.5 x 11 paper. Does anyone have a template for this or know how I could go about setting this up so it will work. Any help would be most appreciated. Thanks! In SO Manager, Database/Registers/Receipt Formats, create a new format - call it something like "Full Page". Under "Receipt and Report templates", the last entry is labeled "Report" - click the magnifying glass to ...

Problems with Window explorer, Internet Explorer8, and, Outlook
Window Explorer is forcing me to close by freezing too often. Internet Explorer is freezing and not responding so often that I have to recourse to using Mozilla Firefox. Outlook has been frozen since September. I cannot send a message, archive or delete any file. Please show me the right way. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in t...

RichEdit formatting problems...
I want allow users to set the tab stops in CRichEditView using values from a dialog box. Any examples for setting the tab stops that work? The examples that I have found didn't work as stated. Thanks. ...

Active hyperlinks in Access 2007 Report
I am a long time Access user and I have a need to produce a report but within the report are hyuperlinks to PDF files that are on a web site. So, here is the scenario - a user has a database on their local machine and they can search the text of 1400+ different construction projects. They submit a search request and receive back a report of all the responses and within the answerset are hyperlinks to the actual PDF documents - but n the report output the hyperlinks are DEAD! Is there functionality to have LIVE hyperlinks?? Thanks in advance for any suggestions! ...

Problem Sharing a Workbook between Mac and PC
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hello and thanks in advance for your help. <br><br>I'm trying to set up an Excel spreadsheet that is going to be shared between a Mac and a PC over a Microsoft Server. This workbook is also password protected for read rights. <br><br>In doing some testing, I found that if I open the document on both a Mac and a PC and I make a change to the SAME row, SAME cell, and if I save using the PC first, when I go to save it using the Mac, I get a message explaining that a change was already made t...

Custom Report #4
Hi I am in the process of creating a custom report in Visual Studio. We are using CRM 4.0 and I chose not to use the report wizard as we require a more complex report. I have managed to create a simple report with our company logo and a table with details from the case entity. We need to create a service activity report which shows all the cases created for a specific customer during a specific time period. I have managed to make the report prefilterable and using the filter we can select the customer and the report will display a summary of each case logged for the customer. I now...

sub-process
What is the sub-process function in the workflow? How do you set one up? Why would you use it? A sub-process is a way to call a manual rule. If you create a manual rule in workflow called Rule A, in another rule you can insert a sub-process and select the manual rule to run. This is a helpful way to create loops and if/then rules that trigger other events. -- Matt Wittemann http://icu-mscrm.blogspot.com "David M" wrote: > What is the sub-process function in the workflow? > How do you set one up? > Why would you use it? > ...

E-Mail Report
I want to E-Mail a report. I know how to do that, but I don't want to send it as an attachement. How can I put the data of the report into the body of the E-Mail? Thanks DS ...

Spending by Category Report
I am using Money 2006 and trying to get a snapshot of my spending over the last 6 months by category. I am using the standard report for this, but for some reason I can't get it to show all my tranfers on the report. I have a lot of $ transferred each month to kids 529 accounts or IRAs and I want to see what my monthly "Grand Total" is including these transfers. I can't find any way to tell money to subtract these from the total in the reports. There is a "Transfer" category on the report, but the only thing it shows is a transfer for the principle paid o...

Reports Issues
> CRM and Reports are on the same box. SQL (named instance) is on a different > server. The following is true of our setup: > > -IE clients are setup to allow Kerberos Authentication > -We are not using Host Headers > -Everything is using the "Network Service" security account > -Our domain functional level is Windows 2003 > -CRM was installed by a domain admin > > When installing, I got an error message '...TrustForDelegationAction > Failed...' I was able to ignore the error and continue with the installation. > Now, when I am trying...

Problem with encoding date
Hi all My problem is an older programme, Next from SCM for Windor 60 to exact. Probably that doesnt tell you much. Originally it works on Win98, and can be reinstalled and work on Win98. =B4 On Win XP however, it will work the first time, but after that it will give this error: Invalid argument to date encode. Then an access violation will occur, and the programme will not work. I tried it on to Win XP computers, the result is the same. That was yesterday, 15.05.2010. It does not even read in the language or anything. All functions result in an access violation error. A...

Sub, Private Sub, Public Sub
I got a different lap top and when I moved my Personal.xls from one to the other some of my macros were not on the list of macros when you go to tools>macros. Although when I look in the code they are there. I figured out that the ones that were "Private Sub" macros were no working. I also figured that when I changed them to just "Sub" they worked. I do know that if "It aint broke don't fix it, cus to try is usually to fix it till it is broke." But what is the difference between "Sub", "Private Sub", & "Public ...

Exchange migration 5.5 to 2003 problem
OK I am converting Exchange 5.5 to 2003. So far the migration has gone well. I do have one wierd problem. I have a couple of users that there users accounts did not match up to there Mailbox account so know I have 2 accounts for the same perosn. One is just a mailbox in the AD reciepents container the other is a Login account in AD. How can I link the Login account back to the mailbox with out deleteing both and recreating everything? Thank you for any help Kyle Davison Here's what you have to do. 1. Disable ADC 2. Go into Exchange 5.5 raw mode (admin.exe /r) 3. Go into t...

Problem with user defined messages (WM_APP+n)
Hi, I have a dialog application that creates a modeless child dialog which remains open for the lifetime of the application. This is due to fact child dialog has an activex control that is constantly processing data and cant be shut down. When the user is not using the child dialog i make it invisible and make it visible when they want access to it. Now i found some code that allows the parent dialog to keep track of it's child dialog.This involves adding a modelessdialoghelper and modelessdialogtracker class to my project. (Code at bottom of page) It involves changing the child dialog...

Links Problem
I have multiple sheets in a workbook. I copy a cell from sheet1 to sheet2 and "paste as a link" it works fine. If I edit the pasted formula by changing the cell reference number the link breaks. Also, if I try to enter a linked formula directly it works (like ='sheet1'! $A$12), but if edit it after I enter it stops working. What am I doing wrong. I'm using excel 2000 sp 3 (if it matters) Thank you, Chris I don't understand your problem =sheet1!a1 is fine without spaces in the name. In fact, excel should correct if you use the ' for sheet names with space...

Problem arranging controls on form
I have a pretty complex form with lots of controls. The controls at the top of the form are positioned inside three rectangles. At the bottom of the form is a tab control. In design view, there is about an eight of an inch vertical space between each rectange and between the bottom rectangle and the top of the tab control. However in form view the tab control overlays the bottom quarter inch of the bottom rectangle. I've fiddled with this for ages and cannot get a separation between the bottom rectangle and the top of the tab control. This is driving me crazy. Any ideas what could c...

Cascade current view to sub-folders
Hi, Is there any way I can set a custom View setting to a set of folders (e.g. set a view setting for a high-level folder and then have it cascade to all sub-folders)? By a custom View I mean the way that items in a folder shoud look - i.e. font settings and the like. Cheers, Stu ...

date problem #7
Hi all,,,,,,,,,could someone help me with this one please. I have a column of dates, thus 20 june 2009 25 june 2009 30 june 2009 As you can see not all are consecutive. Is there any way I can insert the day, either by formating or some kind of code.? Thanks Len Look in the help index for TEXT or format as "ddd" -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Len Case" <len9.pat8@virgin.net> wrote in message news:4Pp6m.45009$FP6.3744...

Fiscal Reporting Periods
Clients are having trouble printing financial statements for historical fiscal periods. Currently in GP 9 and 10, when creating a historical report in Advanced Financial (ie. Balance Sheet 2006), they must select the historical year and periods. Next year, they will have to change the historical year and period settings and save the report as Balance Sheet 2007. They feel that they should be able to create a historical report once, and save it (ie. Historical Balance Sheet). Whenever they would like to print a historical report, they simply select the appropriate year and print. -- ...