By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

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

P: 31
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.


Dec 5 '08 #1
Share this Question
Share on Google+
9 Replies

Expert 100+
P: 1,923
Try this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)      'place code in subreport
  2. If nz([customerNo],0) = 0 Then
  3.         'can display a message here, if desired 
  4.        Cancel = True
  5. End If
  6. 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.
Dec 7 '08 #2

P: 31
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?
Dec 8 '08 #3

Expert 100+
P: 1,923
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.
Dec 9 '08 #4

P: 31
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(Cancel 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.
Dec 9 '08 #5

Expert 100+
P: 1,923
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
Dec 10 '08 #6

P: 31
I will give that a whirl and let you know. Thanks.
Dec 10 '08 #7

P: 31
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.
Dec 10 '08 #8

Expert 100+
P: 1,923
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 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.
Dec 10 '08 #9

P: 31
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.
Dec 11 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.