Missing data in Access sub-report

[Leif]

I have a report that contains 7 sub-reports. If the last sub-report has no data, and the 6th sub-report does have data, then Access is only printing out the first page of the 6th sub-report. I can force all the 6th sub-report to print if I add data to the 7th sub-report. But, of course, I don't want to see this sub-report since there should be no data using the report criteria I'm providing.

If I run the 6th sub-report on its own then then all the data is displayed properly.

Any ideas on how to force all the data to display without requiring a blank 7th sub-report to display?

I have attached an Access 2003 MDB that shows the bug. If you run PriorityRpt, which is the container for the sub-reports you will see that the report "Closed to Charges Notification Received" will only have one page. However, if you run the sub-report PriorityRptCloseToCharges, you will see that sub-report should be 4 pages long.

Thanks,
Leif

 

[vbaInet]

PriorityRpt has no record source and the other report displays two pages. If you strip your db, please ensure you can replicate the problem on the stripped down db before posting.

 

[Leif]

I did test it. Priority report does not have a record source since it is only a container. The sub-reports have a record source. So when you double click the PriorityRpt what do you see?

 

[vbaInet]

I get a No Data message which you coded in the Open event of the report.

 

[Leif]

Sorry, my fault. It worked for me since it was tied to my NT ID. I missed that as I went throught the code to comment out parts that would make it fail for other users. I've updated the MDB.

 

[vbaInet]

PriorityRpt is still one blank page and CloseToCharges is 2 blank pages.

Before we continue, can I get you to rename your reports, especially the ones that have the exact same name as one of your tables. They cause conflicts.

Prefix them with rpt, so PriorityRpt becomes rptPriority. Update your attachment with the changes.

 

[Leif]

PriorityRpt is still one blank page and CloseToCharges is 2 blank pages.

Before we continue, can I get you to rename your reports, especially the ones that have the exact same name as one of your tables. They cause conflicts.

Prefix them with rpt, so PriorityRpt becomes rptPriority. Update your attachment with the changes.

I renamed PriorityRpt to rptPriority. Also also removed the NT ID selection code from the record source, which is the reason you were not seeing data.

I tested it using a different NT ID.

 

[vbaInet]

The problem was to do with the page breaks. You were making them visible incorrectly. In the attached, look at the code in the On Open event of the report.

I noticed that you created a subreport per RptID. This is not the way to go about it. Look at rptNew which doesn't have any subreports. It simply Groups by RptID and Forces a new page for each of them.

 

[boblarson]

Leif:

I just saw your post but I'm glad that vbaInet has things under control. Hope it helps.

(@vbaInet - Leif had posted in the Microsoft Answers forums and I had him come over here so he could upload a copy of the database. Thanks for catching him. )

 

[vbaInet]

(@vbaInet - Leif had posted in the Microsoft Answers forums and I had him come over here so he could upload a copy of the database. Thanks for catching him. )

No probs Bob

 

[Leif]

The problem was to do with the page breaks. You were making them visible incorrectly. In the attached, look at the code in the On Open event of the report.

I noticed that you created a subreport per RptID. This is not the way to go about it. Look at rptNew which doesn't have any subreports. It simply Groups by RptID and Forces a new page for each of them.

Thanks very much vbaInet. That certainly resolves the problem.

I don't think my making the page break invisible if the report prior to the page break has no data is incorrect. However, your change, which is to do that plus make the page break invisible if the report before the page break has data and the report after does not, is an effective work around.

I cannot see any reason for Access to print the first page of a report and not the rest of the pages. Even if page breaks were not coded correctly I would expect either missing page breaks or extra page breaks, not missing data.

On rptNew I can see how that would work for the report heading. However, on the rptNew, since I have different fields, and different ordering in the detail section of my various reports, I don't understand how your recommendation works for that case?

 

[vbaInet]

I avoid page breaks when I can. They're known to cause havoc. Sometimes when the page break controls are tightly together with the subreport they can cause the report to be cut short of records. Why it does that, I don't know. Maybe that's one way to force one page per subreport (which could be an undisclosed secret of Microsoft).

Maybe if you space out the subreports from the page breaks it might solve the problem too.

As per rptNew, I didn't realise the different fields and sorting, so you can ignore that report. On quick glance, they all looked the same to me

 

[Leif]

It turns out the the logic to turn off page breaks was not complex (general) enough. I got a blank page at the beginning if the first sub-report was blank. I also got a missing page break if a sub-report in the middle was not printed, but another report following did have data.

So, I needed to generalize the checking. The logic is

1. Track when the first sub-report is printed. If it has not been yet been printed then suppress the page break.
2. When a report has data record that the first report has been printed.
3. Turn on the page break before the report if not the first report and the report has data.

Below is the event code. The old code is commented out to allow review. I hope this does it.

' Get a count of records in each subreport
rpt1Count = DCount("RptID", "PriorityRpt", "RptID=1")
rpt2Count = DCount("RptID", "PriorityRpt", "RptID=2")
rpt3Count = DCount("RptID", "PriorityRpt", "RptID=3")
rpt4Count = DCount("RptID", "PriorityRpt", "RptID=4")
rpt5Count = DCount("RptID", "PriorityRpt", "RptID=5")
rpt6Count = DCount("RptID", "PriorityRpt", "RptID=6")
rpt7Count = DCount("RptID", "PriorityRpt", "RptID=7")

' Supress page breaks
' If rpt1Count = 0 Or (rpt1Count <> 0 And rpt2Count = 0) Then PageBreak1.Visible = False
' If rpt2Count = 0 Or (rpt2Count <> 0 And rpt3Count = 0) Then PageBreak2.Visible = False
' If rpt3Count = 0 Or (rpt3Count <> 0 And rpt4Count = 0) Then PageBreak3.Visible = False
' If rpt4Count = 0 Or (rpt4Count <> 0 And rpt5Count = 0) Then PageBreak4.Visible = False
' If rpt5Count = 0 Or (rpt5Count <> 0 And rpt6Count = 0) Then PageBreak5.Visible = False
' If rpt6Count = 0 Or (rpt6Count <> 0 And rpt7Count = 0) Then PageBreak6.Visible = False

' Set all page breaks to false then turn them on if there is data in the report following
' the page break. Track when the first report is printed so a blank page is not shown
' prior to the first sub-report printing.

PageBreak1.Visible = False
PageBreak2.Visible = False
PageBreak3.Visible = False
PageBreak4.Visible = False
PageBreak5.Visible = False
PageBreak6.Visible = False
firstRpt = True

If rpt1Count > 0 Then firstRpt = False

If rpt2Count > 0 Then
If Not firstRpt Then PageBreak1.Visible = True
firstRpt = False
End If

If rpt3Count > 0 Then
If Not firstRpt Then PageBreak2.Visible = True
firstRpt = False
End If

If rpt4Count > 0 Then
If Not firstRpt Then PageBreak3.Visible = True
firstRpt = False
End If

If rpt5Count > 0 Then
If Not firstRpt Then PageBreak4.Visible = True
firstRpt = False
End If

If rpt6Count > 0 Then
If Not firstRpt Then PageBreak5.Visible = True
firstRpt = False
End If

If rpt7Count > 0 Then
If Not firstRpt Then PageBreak6.Visible = True
End If