Suppress Sub Report when a field in that sub report is blank

[apank]

I have a report that has 6 sub reports, each separated by a page break. I am looking for a way to suppress any of the sub reports from printing when a certain field, say customer#, is null. I do not want any blank pages either. Any help is much appreciated.

Thanks

A

[puppydogbuddy]

Try this:

Private Sub Report_Open(Cancel As Integer)      'place code in subreport
If nz([customerNo],0) = 0 Then
        'can display a message here, if desired 
       Cancel = True
End If
End Sub

Regarding the page breaks, they could be caused several ways:
1. can be set via the report property sheet (go to the report property sheet and check.
2. can be set by placing a page break object from the tool box (place report in design view, check for page break object on left side of report, select with cursor, and delete)
3. can be set via vba code; review your code and check for code forcing a page break.
4. can be caused by a page overflow; check your report in design view; check to make sure that report objects (e.g. textboxes) don't fall outside the boundaries of the report and also make sure that report boundary plus margins don't exceed physical paper size.

[apank]

Thank you very much for your response.

I am getting an error that states: " Run-time error '2465'

Microsoft Office Access can't find the field 'customer0' referred to in your expression. Is that because the report is not open yet? Is there a way around that?

[puppydogbuddy]

did you replace the illustrative object names I used in the code snippet with their actual names in your subreport? Where did customer0 come from? Did you place the code in the module for the subreport? Please post the code as you now have it in your subreport. Thanks.

[apank]

I did replace the illustrative names with my actual names (I used customer as a common example). I put the code in the "on open" event of the report that is being used as the subreport.

Below is the code. The actual fields name is [new broker 1]

Private Sub Report_Open(Can cel As Integer)
'does not show broker 1 letter if new broker 1 is null
If Nz([New Broker 1], 0) = 0 Then
Cancel = True
End If
End Sub


Again thanks for your work.

[puppydogbuddy]

It looks like [New Broker 1] is a text, not numeric data type. If that is the case, then change this:
If Nz([New Broker 1], 0) = 0 Then

to this:
If Nz([New Broker 1], "") = "" Then

[apank]

I will give that a whirl and let you know. Thanks.

[apank]

I still get the same Run Time Error

I am getting an error that states: " Run-time error '2465'

Microsoft Office Access can't find the field 'New Broker 1' referred to in your expression.

What I am trying to do is have a batch of letters print, and each of the letters has up to 6 Cc letters. Each of the 7 letters should be grouped together (that is why I did the sub reports). If there are only to be 2 cc letters, I only want the original and the 2 cc letters to print. Currently all 7 are printing. In this report there are countless # of letters, each with a potential of 6 Cc's. I am trying to save the paper that is being wasted by printing the blank letters. I hope that makes sense.

[puppydogbuddy]

The fact that you have spaces in the field names makes things more complicated. For example it looks to me like [New Broker 1] has one space between "New" and "Broker" and two spaces between "Broker" and "1". Does your field in the report have one or two spaces between Broker and 1? In Access, it matters. [New Broker 1] is not a match for [New Broker 1].
Is [New Broker 1] a field in the record sources of the sub and main reports? Is [New Broker 1] text or numeric?

Try some debugging.....t o find out whether the subreport or main report is opening first, place control breaks in the open events of both......when you run the report, see which code breaks first.and look at any values appering in your variables when you move your mouse cursor over the variable names.

[apank]

I believe my spelling was correct. I did find a way around my issue. I put "is not null" criteria in the queries behind the reports. So now the subreports do not generate, and are not linked to the parent report. Thank you for the time you put in on my problem. This site has been a great help to me.