Sometimes sub report shows twice in detail section

[moondaddy]

I have a report which uses several sub reports. The main report is bound to
a temp table called tbLease. I created a header for each lease record. The
detail section has a subreport called contracts and another called wells.
each is bound to a temp table (tbContracts and tbWells). I'm not using any
queries and the sub reports use the child/master properties correctly.

For each lease record the report will show a list of contracts under that
lease, and then a list of wells under that lease. most of the time it works
as expected. however, on a few pages the contract sub report repeats itself
and another page both the contract and well sub reports repeat them selves.


Here's some sample data where the contract report shows itself twice:

Query2 Ls_ID LsD_ID Ct_ID CtD_ID
2826 4266 2717 2310
2826 4266 2718 2311


Ls_ID and LsD_ID are fields in the lease header of the report.

Ct_ID and CtD_ID represent data from the contract. each contract record
also include the LsD_ID field which is used in the master/child properties.

In this case the contract sub report has 2 rows of data as you would expect.
however, the contract sub report shows itself a second time with the
identical 2 rows of data. I've created access reports for 15 years and have
never seen this issue.

can someone please advise? I can send a small mdb file with the temp tables
and report sample if you want.

Thanks.

 

[Guest]

It looks like you have contract details in both the main report and the
contract subreport. If you are joining only on LS_ID then you need to put the
subreports in the LS_ID header or footer section, not the detail section
where there could be multiple LS_ID.

 

[Allen Browne]

This is an odd one. I don't have a clear answer for you, but hopefully the
suggestions below will trigger a useful idea to persue.

1. Since the report and subreports are based on a single table, it cannot be
due to the source query repeating rows.

2. I guess you have double- and triple-checked that the temp tables does not
have these rows repeated (i.e. that the error is with the code that
populates the temp table.)

3. If the report has more than one level of grouping, is the text box for
the MasterLinkFields in the same section as the subreport? Or is it in a
higher-level section where the value could be repeated?

4. If there is *any* code in the report's events, you might try temporarily
cutting it out (save as a text file.) Then set the report's HasModule
property to No, and compact the database. (This will eliminate the
possibility of a section being repeated by an event.)

5. Is there any clue about the case where the data gets repeated? E.g. is it
when the subreport appears at the bottom of one page and also at the top of
the next?

6. Does the section cease repeating if you change the Properties of the
report's sections (e.g. Repeat Section, Force New Page, Can Grow), or the
properties of the rows in the Sorting And Grouping dialog (e.g. Keep
Together)?

7. As always, make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
Then compact again. Explanation of why:
http://allenbrowne.com/bug-03.html

8. Just to check nothing has gone haywire with the report, it may be getting
Access to recreate it for you like this. Export to a text file with:
SaveAsText acReport, "Report1", "C:\MyFolder\Report1.txt"
Then delete the report, compact, and generate it again with:
LoadFromText acReport, "Report1", "C:\MyFolder\Report1.txt"

9. What version of Access? Does getting the latest service pack fix it:
http://support.microsoft.com/sp/