Cannot hide empty subreport

[RicelandIT]

I've reviewed the posts on www.bytes.com.
My problem is the same as that reported by others:
a report with an empty subreport cannot suppress the display of the empty subreport. I've set the "can shrink" and "can grow" properties to "yes" on both the footer and the subreport's control object. I've also minimized the physical size of the subreport in the footer. These all seem to make no difference. So, I added code to the "format footer" event of the report, attempting to hide the subreport if the .HasData property is false. I've also tried to add code (suggested in other related threads on this web site), which seems to compile without problem, but is unable to make the subreport non-visible. Please see attached JPG for example of empty "Additions (Subtractions)" subreports. I'm desperate! If you help me I will be your friend for life. Seriously.

[Mihail]

I'm afraid that no one become your friend.

That because a report is not interactive (since 2007 at least).
So, when you design a report you reserve spaces for all you need to show in before you run it (at design time).

I'll be happy for you even if other one will become your friend for a life :) , but I have serious doubt that will happen.

Good luck !

[NeoPa]

Unfortunately the picture is too small to see anything helpful, but I sort of get the gist of what you're after. Which version of Access are you using by the way?

Is there anything else in the Footer Section that should display if the Subreport doesn't?
Gett ing the Subreport not to show should be easy. I'm guessing your problem is with getting the Footer Section not to show when there's no data in it. This is more involved than you've mentioned. The Subreport control won't shrink related to the lack of data. It has a designed size and that won't be effected by any data or lack thereof.

I would expect, as long as there is nothing else to print in the Footer Section, that the approach would be to set its visibility to False when there is no Subreport data. Not forgetting to set it to True when there is of course (IE. Set it each time regardless of the data, but to a value that depends on the data).

[RicelandIT]

I'm using Access 2007. There is nothing else in the footer. There are 2 footers: one for the day of the week and another for the employee. (Report lists data by employee, then for each employee data appears for each day of the week.) The employee footer always has data, but the day of the week footer may not, and the subreport contained in the day of the week footer is the one I'm trying to hide. I've tried to set its visibility to FALSE regardless of whether there is data, and it still appears. Here is the code I placed into the footer's "On Format" event:

Me.TotalEmpItemsDaysubreport2.Visible = False

[NeoPa]

Your single line of code doesn't include anything that indicates that this should be dependent on the presence or absence of records in a subreport anywhere. By the time of the Format event I would expect it is too late anyway. At that point setting Cancel = True would be required to ensure the section isn't printed for that particular instance.

[RicelandIT]

As I said before, I set its visibility to FALSE because it didn't appear that the if-else-endif block was functioning properly. The entire block of code is the following:

    If Me.TotalEmpItemsDaysubreport2.Report.HasData Then
        Me.TotalEmpItemsDaysubreport2.Visible = True
    Else
        Me.TotalEmpItemsDaysubreport2.Visible = False
    End If

If the "on format" event is too late, then what difference does it make whether or not I check the .HasData property? The answer to that question is irrelevant.

I've seen other similar threads on this web site that suggested putting the code into the "on format" event.

NeoPa posted the following on a similar thread on this web site:
"If you know the report will only ever run as a subreport then you can handle the event with code that hides the SubReport control on the main report. "

What is this code to which you are referring above? In what event should it be placed?

[NeoPa]

RicelandIT:
If the "on format" event is too late, then what difference does it make whether or not I check the .HasData property? The answer to that question is irrelevant.

This sounds confused. Of course it's not irrelevant. The answer determines what is required, and has no bearing on the approach used. The approach used at that point in the code (Setting Cancel = True.) is different from the approach you've used, which may work at an earlier stage, but not there. Whichever approach is used, the logic must still be implemented.

I was rather hoping to see the whole procedure posted, but at least we know it's in a Format event procedure, so it should now look something like :

Private Sub XXX_Format(Cancel As Integer, FormatCount As Integer)
    If Not Me.TotalEmpItemsDaysubreport2!HasData Then Cancel = True
End Sub

RicelandIT:
What is this code to which you are referring above? In what event should it be placed?

Hard for me to say with just this to work from. A link may help, but let's see if this works adequately first, shall we.

[RicelandIT]

Here's the code:

Private Sub GroupFooter3_Format(Cancel As Integer, FormatCount As Integer) 
    If !Me.TotalEmpItemsDaysubreport2.Report.HasData Then
        Cancel = True
    End If 
End Sub

It doesn't matter if we use "!Me" or "Me.": the empty subreports continue to show up.

[Mihail]

Hi again !
As I see you really need that.

Why not consider to print directly to a file what you wish ?
It is a hard work but is doable.

Take a look here,
http://bytes.com/topic/access/answer...ox#post3702635
and see if that can be a way to accomplish your task.

I think I can help you with a sub-routine like that:
PrintInFile(NewRow As Boolean, Column As Integer, LengthOfColumn As Integer, Alignment As String)

I must warn you, again, that will be a hard work for you.
And the aspect of the report will not be as nice as Access can do.

[wire]

You might consider making the 'final product' in Crystal Reports. Crystal has a very strong feature in that it can overlay a section of a report -underneath- another section. I've also been able to tweak sub-reports to take a minimal amount of space.

I've had fun with sub-reports in both Access and Crystal and If it's important enough make the sub-reports not expand then you'd be better served by Crystal.

[RicelandIT]

I would prefer not to use a complex workaround. Such a solution will likely create more issues I cannot resolve in the future. I saw in another post that it is best to change the SQL so that the empty subreport is not retrieved, but I don't understand where that SQL is physically located so that I could try a different type of join between my report and subreport. (This might not even be making sense.)

Maybe there is something obvious I am not communicating. Is there a place I could post my application so a more knowledgable person could run this report and see what happens?

[RicelandIT]

I am not familiar with Crystal Reports. I'm already having to learn Access from scratch, and have invested about 50 hours into this application. (I was formerly a Visual Studio.NET developer, but no longer work at that company, and no longer have access to that development platform.)

Is it, or is it not possible to hide an empty subreport?

[NeoPa]

Please read [code] Tags Must be Used.

RicelandIT:
It doesn't matter if we use "!Me" or "Me.": the empty subreports continue to show up.

One of those (exactly as stated) will not work at all. Me! will behave in almost all situations (and certainly this one) just as Me.. I fail to see why this isn't working, assuming you used the code I gave rather than what you've posted (which wouldn't be expected to work).

If this isn't working for you then I think this is a good time to offer to look at your database. I have a strong suspicion (from what you've said and all the attempts that you've reported as failing when they probably shouldn't) that something off the radar is going on here. The only way I'll be able to help with that is to see the actual database for myself. To attach a database you must follow these instructions carefully (Attach Database (or other work)). They're not rocket-science, but close following of them is often important.

Let me say that I'm pretty confident that a solution can be found, once we know what the problem actually is. Changing your tools because of a problem, rather than determining and then fixing it, is not an approach that I would ever recommend, as it's an attitude that ensures one never reaches a good understanding the subject. I applaud your decision to stick with it for now :-)

[wire]

On Access, the best I've been able to do is shorten the height of the sub-report and set the 'can grow' property to "yes". It still take a few millimeters of space but is better than otherwise using up the entire height of the fields.

[NeoPa]

In that case I expect you'll be interested to see what we come up with. Even if it proves impossible it won't be uninteresting (but I'm pretty sure I've done something like this before successfully - though much of my more fiddly work has been in other people's projects so I don't have it to hand).

[RicelandIT]

Please accept my apology for having a negative attitude and failing to properly post the code. Trying to resolve this has taken a real toll on me. I will be very careful to comply going forward.

I am attempting to upload the database, per NeoPa's instructions (which are very clear). When I save the database as Access 2003 format, I notice that when I open the 2003 version, the report (and subreport) are missing. I guess that might be normal, since the database is separate from the report. How will you test my report if I upload a database (.mdb file) that does not include it? Should I just skip this "save as Access 2003" step?

Secondly, I am able to compile the application, but I don't see an option under the Tools menu to "Compact and Repair Database", as instructed in step #7. I'm using Access 2007 and see a "Database Tools" item on the ribbon, but it does not include the options described in Step #7.

[NeoPa]

Let me try to respond reasonably to those points :

• Your attempts at posting a question may not be perfect, but as a new poster that's understandable (And let's face it, there was nothing so bad). Noticing where and how to change that is a good step forward. Furthermore, I believe all here understand how frustration within a project can lead us to show less patience than we normally manage ;-)
• I have little idea why the report and its subreport don't exist in the 2003 converted version. I could guess that it possibly uses facilities only available in 2007 and so is unable to be converted, but I would be guessing.
• It may be possible to try various attempts (on a copy - don't muck up your original) at stripping out bits that are not central to the problem. I can't help much with that, as I have all but no experience in 2007 (an abomination to my mind!).
• I'm not sure what the situation is with Compact and Repair and 2007. It may reflect a decision to run it automatically every time, or MS may have introduced some decent temp-space recovery after all this time. Either way, it's only about size and if it's not there, it's not there. Don't worry about that step unduly.
• If it's posted in 2007 I will only be able to look at it if I borrow my son's PC. Others will be able to more easily, I'm sure, but I will not have access to it easily. If that's all you can do though (and that may be an unavoidable situation) then I'll do what I can to borrow a 2010 install and look at it from there for you.

[Mihail]

Rice !
I think that the reports are there but you don't see it after you save in 2003 format.
Try different views in Shutter bar (left side of Access window where normally you see objects in 2007) and you will be able to see all objects in your (2003) database.

Of course, if you use a front end and a back end database and the report is (as usually) in FE you must save both parts in 2003 format, ZIP both files as once and attach to your next post.

The Compact and repair option is under the Office button (see pic).

[NeoPa]

Thanks for that Mihail. I'll get on to 2010 one day (soon), but till then I appreciate when people fill in my gaps ;-)

[Mihail]

You are welcome, NeoPa.
I know better than you, than Smiley, Rabbit,ADezii and a lot of others where are the buttons in 2007.
How to use this buttons to design a database... is another story :)))

[RicelandIT]

Sorry I have been out of town for 4 days, but have finally returned.

Well, you were correct. The reports WERE there, but I could not see them due to the view. And, the "Compact and Repair" was also where you indicated.

The attached database has been saved in 2003 format. I am attempting to run the report "Employee Totals By Day". Notice that employee "Guillory, Dexter" has no items in his subreport. Such subreports are the ones I want to hide.

[NeoPa]

The Subreport = "TotalEmpItemsDaysubreport2"?

As I was unfamiliar with what to look for, I added a label into the "weighin_day Footer" section. I noticed that this section was only visible, at all, for records :

1. Clinkscales, Jason.
2. Griffin, Andy.

But not for :

1. Guillory, Dexter J.
2. Rudd, Reba H.

That all seems to be working exactly as expected, as far as I can see.

[RicelandIT]

You are correct, there is no data for Guillory and Rudd, but the "Additions or (Subtractions)" still shows up for them and for every single day for every employee, regardless of whether or not there is any data below it. It's this "Additions or (Subtractions)" that I'm trying to hide when there is no data.

[NeoPa]

I can't see what you're referring to. Perhaps you could identify it by section and object names.

[RicelandIT]

The circled areas are the subreports. Guillory has no data, so I want his subreport to be hidden.

[NeoPa]

I don't see what you see. Much of you blue stuff is unrecognised by my system and comes out black, but the main point is that the subreports don't show when they shouldn't. See the picture for an illustration of what I see :

[RicelandIT]

In Access 2003, it DOES, indeed, appear to be working properly. However, as you can see from my earlier image, the empty footers show up for every employee in Access 2007. Could there be some kind of database setting or reports setting that is causing the difference? At this point, I just can't worry about it any more. I don't have any more time to spend on it, but I really appreciate all your replies. Many individuals were very helpful.

[NeoPa]

RicelandIT:
At this point, I just can't worry about it any more.

I hear you.

NB. For future reference (we can't worry about it this time) please note point #5 in the linked article (from post #13) where it says to make sure the problem is still evident if you've made any changes from points #1 to #4. It might have saved you some precious time, but hopefully will on other occasions.

[Mihail]

Good news for you Rice. And a big question for Mycrosoft: WHY ?!?!?

I find out that the On Format event not fire in Report View.
If you will try to run the report in Print Preview you will have a big surprise.

Can I have a little bit from your friendship ?
The rest is for NeoPa, of course.

Good luck !

[NeoPa]

Is there another, more appropriate, event that could be used in A2007 then Mihail?

[Mihail]

Oh, sorry, NeoPa.
I forget that you can't see, in 2003, what I see in 2007.

In 2007, if the report is opened in Report View mode (a long report with no delimited sheets - hope you understand what I mean) the On Format event is not triggered (fired). So the code inside that routine is not applied (running). I simple verify by placing a STOP statement in that routine. And nothing happen.

But, if the report is open in Print Preview mode the code is running and it do the job as you see in the attached pic (PrintPreview).

The second pic show you the available events for report section.
I try to move the code under On Paint but appear a error that say that the visible property can't be modified under this event.
I don't try under On Print and I don't know what is On Retreat event even after I read the associated help file.

Hope you understand me now even if my English is not much better than in preview post :) .

[NeoPa]

It might be worth playing with the Print event then. See if that yields the expected results.

[Mihail]

I can't try that: I have not access to a printer.
If I use a virtual printer I can't be sure about results.
So maybe Rice can try that for us.

[NeoPa]

I meant that for RicelandIT. I should have made that clearer.