How can I eliminate spacing between two instances of a sub report.

Hi Experts,

I have a report with a sub report, for some reason the sub report have extra spaces that I'm looking to get rid of them.
See attached pic of sub report..
Untitled.png
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Open the sub in design view and make sure that the bottom of the form is tight against the bottom of the lowest control.
0
Linens Range786Commented:
Unfasten the secondary in propose question paper and make clear in your mind that the foundation of the form is stretched against the foundation of the lowly be in command of.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also be sure you're not showing the Form Footer/Header in the subform.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

bfuchsAuthor Commented:
Ok Experts,

I removed all spaces also removed header/footer section and still getting spaces shown.

See design of my sub attached.

and here is the code in detail format event.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim i As Integer
    For i = 1 To 10
        If Len(Me.Controls("Patient_Progress_Notes_" & i)) > 0 Or Len(Me.Controls("Time_" & i)) > 0 Then
            Me.Controls("line" & i).Visible = True
        Else
            Me.Controls("line" & i).Visible = False
        End If
    Next
End Sub

Open in new window


Thanks,
Ben
Untitled.png
0
bfuchsAuthor Commented:
And here is the design of my main report.

Thanks,
Ben
Untitled.png
0
bfuchsAuthor Commented:
And this is how it looks like in preview..

Thanks,
Ben
Untitled.png
0
PatHartmanCommented:
your data is not normalized which is contributing to your problem.  Based on the pictures, it looks like only a couple of the numbered items actually have values.  This wouldn't be a problem at all in a continuous subreport.  to solve the problem here, you will need to adjust the properties so that all the controls are set to can grow and can shrink = yes.  You will probably also need to get rid of the separator line since unless you want to write code, there is no way to suppress that.
0
bfuchsAuthor Commented:
Hi Pat,

you will need to adjust the properties so that all the controls are set to can grow and can shrink = yes.  
This is how they're currently set.
You will probably also need to get rid of the separator line since unless you want to write code, there is no way to suppress that.
This is how I was advised to do, https://www.experts-exchange.com/questions/29072443/How-to-have-a-line-between-controls-that-can-grow-shrink.html perhaps you have another way to accomplish that?
besides, the spaces dont seem to come from within the sub itself, just rather from 2 instances of the report, as the first columns of sub report contain surrounding lines, and see where are the spaces located, (last attachment).


Thanks,
Ben
0
PatHartmanCommented:
Make sure that the controls do not overlap.  Controls that overlap will not shrink/grow.  You may have to paste the report with some data so we can play with it if the overlap isn't the problem.
0
bfuchsAuthor Commented:
@Pat,

No controls overlapping.

See attached app, look at report Patient_Progress_Notes2Rep

Thanks,
Ben
db58.zip
0
PatHartmanCommented:
The problem is all the space between the controls.  While the controls are eliminated, none of the other space is.  So, that's where the excess space is coming from.  I removed it all:Space.JPG
0
bfuchsAuthor Commented:
And how does it looks like now in preview?
See attached how it looks by me, not presentable..


Thanks,
Ben
Untitled.png
0
PatHartmanCommented:
There will be some happy medium.  You will have to find it by trial and error.  The fewer the number of items in a set, the more wasted space at the end.  Try using borders on the controls rather than lines between them. That will eliminate some of the wasted white space.

It is probably a lot of trouble at this point to normalize the table but that solves the problem without code.  Normalization is always the best method.  Do it now or do it later.
0
bfuchsAuthor Commented:
Hi Pat,

Try using borders on the controls rather than lines between them
This option is also not something that looks elegant, unless I can hide the side borders.

Really I would like to see a solution that would deal with the problem with code, similar to what Jim had suggested in the other post, meaning check if there is no data in control then beside of hiding it, it will also move them up and move up the following items as well..

Let me know if this is doable.

Thanks,
Ben
0
PatHartmanCommented:
Have you considered normalizing the table?
0
bfuchsAuthor Commented:
Hi,

Of Course, in fact I originally designed the table/report in a normalized state, and now users switched to Caspio web builder, and at the moment they're are in charge of the Caspio site of the program, I have to deal with importing data into Access and implementing whatever its missing in Caspio..
Resulting in poor database design++..

Thanks,
Ben
0
PatHartmanCommented:
You don't have to mimic the web site.  Web sites are notorious for their poor database design back ends.
1. If you normalize the schema, the problem goes away
2. Otherwise, you need to understand that the space effect is cumulative.  Each fraction of an inch of white space adds up to visible gaps in the printed product when the data is sparse.  I showed that you can control this.  I didn't attempt to fix the problem, only to show you what caused it.  Only you can decide how much blank space you are willing to put up with.   I'm not sure you can reduce the space programatically but you can increase the space by using an additional control that will provide the white space.  Make it the height you want for spacing and make it unbound so it has no value and will be ignored for printing.  Then if the actual control has a value and so you know you need space "above", put a value in the unbound control.  The trick is to make the font color the same color as the background so you can't see it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
thank you!
0
PatHartmanCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.