473,326 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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.

Thanks

A
Dec 5 '08 #1
9 6310
puppydogbuddy
1,923 Expert 1GB
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
apank
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
puppydogbuddy
1,923 Expert 1GB
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
apank
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
puppydogbuddy
1,923 Expert 1GB
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
apank
31
I will give that a whirl and let you know. Thanks.
Dec 10 '08 #7
apank
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
puppydogbuddy
1,923 Expert 1GB
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.....to 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
apank
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

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

Similar topics

6
by: MX1 | last post by:
Please help if you can. I have a form with an address field. There are 6 fields: Name1 Name2 Address1 Address2 CityStateZip Country
5
by: ChadDiesel | last post by:
My basic question is why does my print report button on my subform print a blank report when my cursor is on a blank entry line? Here is a more detailed explanation of my problem. I have a...
1
by: mstery | last post by:
I have a report generated via an ID selection made in a dropdown on a form. The report filters by an on click event in a preview report button on the form. Everything in the report, including...
2
by: Heather | last post by:
I want to create a report that shows the percent of safety audits complete by each individual shift, based on the safety audit date. The user has to put in the dates they want the report to pull...
1
by: Mark | last post by:
I'm using dropdownlist in a datagrid to allow a column in a table to be edited. The dropdownlist contains several the from "dictionary/ code-decode" table. These columns are NOT required in the...
0
by: Kalyan | last post by:
I already created report not with crystal report but through .net. Now I want to add new column in my database to crystal report. Field viewer is disabled. If i open crystal report in .net. My...
1
by: gregoryenelson | last post by:
Hi all. I have created a Report with a graph also on it. The users, working through a form only (not the DB window) will want to view that Report run numbers of times against different criteria,...
1
by: dan.cawthorne | last post by:
Kinda Confused to why i cant get a single text field to add up all the Projects that are filtered Via a Query. I Have a Query Based on a Projects Table. The Query Have a Criteria To Select a...
1
by: Donna Richelle | last post by:
I am using Access 2003 and am having some trouble printing unbound fields on a report. When I do a Preview of the report, the data in the unbound field shows perfectly, but when I print a hardcopy,...
7
by: Shari Gottheim | last post by:
creating a school directory. how do i get the report views to not show blank lines?
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.