Prevent Subreport from printing if no data (1 Viewer)

[cclark9589]

Believe me, I browsed through this forum until my fingers started bleeding!

I have a timesheet report with 3 subreports; billable hours, non-charge hours and expenses. Timesheet has a page header, there is no report header. Billable hours and non-charge hours invariably with have data however, many times the expense report will have nothing. The subreports lay out like this:

Billable Hours
Non-Chargeable Hours
Expenses

By necessity, there is a page break between non-chargeable and expenses. If there are no expenses for the pay period there is no need to print that report and therein lies my problem.

What happens is even if there is no data in the expense query for a pay period a subsequent page where the expense report would normally be will print anyway with just the page header information from the main report showing.

I have tried many of the suggested solutions but none have worked. I've put the HasData statements in timesheet report On No Data property as well as the detail section On Format and On Print properties. I've also tried putting the statement in the subreport itself in the same sections. I've played with the CanShrink/Grow properties too. All to no avail. The name of the subreport is JobExpsRpt.

Following are samples of the code I've tried using:

JobExpsRpt:
Private Sub Report_NoData(Cancel As Integer)
Me!JobExpsRpt.Visible = Me!JobExpsRpt.Report.HasData
End Sub

Timesheet:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If FormatCount = 1 Then
Me![JobExpsRpt].Visible = Me![ JobExpsRpt].HasData
End If
End Sub

As mentioned, I've tried placing these in different locations on the two reports but no luck.

Can anyone point out to me where the code is wrong, how it should read and precisely where it should be placed? Is it possible the page header has something to do with it?

I've spent the better part of 5 hours today in addition to several hours yesterday trying to resolve this issue. Specific help for an Access Dummy would be greatly appreciated.

 

[Mr. B]

cclark9589,

Try placing your line of code:
Me!JobExpsRpt.Visible = Me!JobExpsRpt.Report.HasData
in the On Format of the section where the sub report is located.

You would, of course, need to have a statement like this in the On Format of each section where each sub report is located.

HTH

 

[cclark9589]

Ok, gave it a try and still no go on it.

I put the code into the On Format property of Detail section of my main form and all 3 subforms. When I went to preview the timesheet I got error messages about can't find the field referred to in your expression. I attached the screen shots on these messages.

Commented them out of the billable hours and non-chargeable hours subreports and the timesheet loaded fine but once again, the second page (which would be the expenses) prints even though there is no data to print.

Could it have anything to do with the page header?

 

[Mr. B]

I have again tested the statement:
Me.JobExpsRpt.Visible = Me.JobExpsRpt.Report.HasData

in a report here and it works as expected.

Is "JobExpsRpt" the name of the sub Report control? The name of the control after the sub report is inserted into the report can be different. This code only needs to be in the On Format section where the sub report has placed. The error messge that you are seeing indicates that it it attempting to find a field, not the report.

HTH

 

[cclark9589]

Mr. B;

I'm pretty sure I have the name right. I've attached two screenshots.

The pic1 is the timesheet in design view with the expense report selected. In the property window it says JobExpsRpt. Pic2 is a view of the code for the Timesheet Detail section On Format property.

I know I'm going to feel pretty dumb if it is something easy and obvious but I sure as heck can spot what that might be.

One thing I do notice is that the name of the subreport 'container' (JobExpsRpt) is different from the subreport name, which is TimeExpsRpt. Could that have anything to do with it? Two different names and maybe it's confusing Access. I know I'm confused but that is nothing new.

I'll try and pare down the database and upload that if you'd like to take a look at it. You have to promise not to laugh too hard though as this is my first attempt at something such as this

 

[Mr. B]

If you can let me look at your database I will see what we can come up with.

If it is small enough after you zip it, you can post it here, if not, if you will go to my website: www.askdoctoraccess.com, you can send me an email from there and I can provide you with FTP info for uploading it.

 

[cclark9589]

Mr. B;

Attached is a pared down version of my database. It's late and I'm bushed from umpiring a baseball game tonight so if I chopped too much and broke it, let me know and I'll take another whack at it.

It is a split database so I figure you will have to re-link the tables. haven't figured out how to put it back together after splitting so sorry for any inconvenience.

Thanks for taking the time to take a look.

 

[Mr. B]

cclark9589;

Well, first, I did of course have to relink the tables. Not a problem, we can discuss this later.

Second, I never got any error. I tried the database using Access 2002 and Access 2007 and did not get an error. However, the TimeSheet report seemed to open very slowly.

I made a change to the line of code:
Me.JobExpsRpt.Visible = Me.JobExpsRpt.Report.HasData

Notice that the ! is now a "." Now the report opens quickly.

I still cannot tell you why you are getting an error, but try making this change and see if we get lucky.

Sorry, but if I cannot recreate the error, I can't fix it.

Post back!

 

[cclark9589]

Mr. B;

Made the change and no error which is a start.

I think I had it right all along but the issue I was hoping to solve is still present. If during any given pay period there are expenses, I want the report to print out but if there aren't any expenses in that given pay period, then I don't want it to print.

The report is designed to be run from the Time Card form and will display only records matching the TimecardID, Period End and AudID (Employee). What happens is that if there are no expenses, the page that would display the expense report had there been expenses still displays and will print out.

If you were to look at timecardID 5 & 6, you will see what I'm talking about. #5 has no expenses so I don't want that second page to print. #6 does have expenses so it should, and does, show the expenses and will print.

I do have a page header on Timesheet because I want that information to print on every printed page. Just before the Expenses subreport there is a page break because I need that to be on a separate page because we have to turn that in to get reimbursed for any out of pocket expenses. If there is no data in either one of those reports, then I'd like to save one tree without having the user go through the steps of printing selected pages. Does that make sense?

Is this glitch related to the page header, the page break or possibly both?

Thanks for the help and patience.

 

[Mr. B]

cclark9589;

Well, I finally got this to work as you said you wanted it to. Using Time Card ID 5 and 6 to work with, I have now found a way to cause the report to only print the two sub reporst if there is data in the "JobExpsRpt" sub report.

Here is what I did:

First, set the Can Shirnk property of the "NonChgSQsRpt" sub report to "Yes". Because this was set to no, it was consistantly causing a second page to be created, even when there was no data in the other sub reports.

Next, move both of the sub reports and your page break into the Report Footer section, as far up in this section as you can place them.

Next, replace all of your code in the Detail Format event to the following:
If Me.JobExpsRpt.Report.HasData = True Then
Me.ReportFooter.Visible = True
Else
Me.ReportFooter.Visible = False
End If

I have also attached a copy of the front-end file for you to look at. You will have to relink to the backend file.

This one took some thinking and I must be getting to old for that. LOL

 

[cclark9589]

Mr. B:

As Harry Carey used to say, "Holy Cow!" Wouldn't have guessed the footer to be the answer. I did discover another little glitch but using your solution with appropriate modifications, I wasn't able to solve the problem. What I discovered is that some timesheets will print a blank second page in some cases which I can't figure what the case may be.

If you take a look at the timecardID 1 you'll notice only two pages display and/or print. However if you were to look at timecardID 5, you'll see that a second page, which is blank, displays before the expense report. The only differences I note between the two is that #1 has fewer billable and non-chargeable hours line detail than #5.

I tried the HasData method, placing the non-chargeable in the page footer and other things but nothing works or just totally fouls things up.

I've attached a fresh copy of my sample database for your amusement.

 

[Mr. B]

cclark9589,

You must have made some changes in the data. I do not get any additional pages in the report for either the 1 or the 5 timesheet.

You may also need to post the backend file so I am working with exactly the same data you are.

 

[cclark9589]

Both the front and back ends are in the zip file I uploaded with my previous post.

TimecardID 5 is actually record 4 of 15 so you might have been looking at the next timesheet which does display correctly.

 

[Mr. B]

Yeah, I finally figured out that just as I thought I was saving the attachment, something happened and it did not save it.

I will look at it.

 

[Mr. B]

cclark9589,

Give this a try.

First, remove your Page Break. Yes, that's right remove it.

Next, select the Details section of the main report. Display the properties dialog box. Locate the "Force New Page" property on the "Format" tab and select the "After Section" option.

Try your report and see if this is what you want.

HTH

 

[cclark9589]

Like a freaking charm!!!!!

You have provided two fixes for something that I never would have guessed or thought of.

Words are not enough.

Thank you so much, Mr. B!