Cannot hide empty subreport

**Mihail** · Feb 13 '12, 10:38 AM

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** · Feb 13 '12, 02:53 PM

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** · Feb 13 '12, 08:57 PM

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:

Code:

Me.TotalEmpItemsDaysubreport2.Visible = False

**NeoPa** · Feb 13 '12, 11:12 PM

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** · Feb 14 '12, 03:25 AM

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:

Code:

    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** · Feb 14 '12, 02:21 PM

Originally posted by RicelandIT

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 :

Code:

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

Originally posted by RicelandIT

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** · Feb 15 '12, 04:15 AM

Here's the code:

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** · Feb 15 '12, 06:03 AM

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/answers/931887-how-text-file-items-listbox#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(New Row 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** · Feb 15 '12, 02:46 PM

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** · Feb 15 '12, 02:50 PM

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** · Feb 15 '12, 03:11 PM

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** · Feb 15 '12, 04:42 PM

Please read [CODE] Tags Must be Used.

Originally posted by RicelandIT

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** · Feb 15 '12, 04:42 PM

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** · Feb 15 '12, 05:16 PM

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).

Cannot hide empty subreport

Cannot hide empty subreport

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment