If subreport has no data on report... 1

[uscctechnician]

Hello everyone,

I have checked and found some posts relating to this but I am unclear on the content and therfore am hoping someone could point me in the right direction.

I am in Access 2003, and have a report set up. In this report, there are multiple subreports, each having a page break so each subreport starts on a new page. Everything works fine.

What I am running into, is if a subreport has no data at all, I still get the blank page due to the pagebreak. I saw some other post that referred to the following code:

Private Sub Report_NoData(Cancel As Integer)
Cancel = -1
End Sub

But I do not see a "NoData" option under the subreport properties.

I would just like if the subreport has no data, to just not print the blank page and just go and print the next subreport.

thanks in advance!!

 

[dhookom]

Use code to set the page break control to invisible if the subreport returns no records:

  Me![YourPageBreakControl].Visible = Me!YourSubReportControl.Report.HasData

Duane
Hook'D on Access
MS Access MVP

 

[uscctechnician]

Thank you Duane!!

 

[uscctechnician]

Duane,

Stupid question - where exactly do I place this code?

 

[dhookom]

Not a stupid question. I should have provided the information in the reply.

I would place the code in the On Format event of the section of the main report that contains the pagebreak control.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me![YourPageBreakControl].Visible = Me!YourSubReportControl.Report.HasData 
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[uscctechnician]

Duane,

Thank you again. That all makes sense now. Now I have to go through and redo some of my report because I don;t know how I did it, but I have subforms in there as well, and the hasdata will not work with subforms.

Thanks again!

 

[uscctechnician]

Ok, I am having an issue with this again.

Here's the code i am using in the report's Load properties:

If Me.[Repairs subform].Form.RecordsetClone.RecordCount = 0 Then
Me.PageBreak260.Visible = False
End If

On Access 2007 this works fine - if there is no data in this subform, I do not get a blank page. If I run the very same report on Access 2003, I get the blank page even with no records on the subform.

Is there a problem with Access 2003??

 

[dhookom]

I wouldn't use subforms on reports and I wouldn't use the report's load properties.

I would use subreports and try the On Format event of the section of the report containing the subreport.

Duane
Hook'D on Access
MS Access MVP

 

[uscctechnician]

Thanks Duane. I figured that the subreports will probably be the best way to redo this report when I have time. But for now, I actually got it to work by moving that code to the report's "Print" event section. Not sure why but it will get me buy until I can redo each subreport since there's only 7 or 8 of them

thanks again for all your great help. I really do appreciate it all!