MS Access SubReport - How do you remove the whitespace caused by a blank subreport

I have a subreport that based on a query.  If the query doesn't return a value, how do I remove the whitespace left behind.  There is another subreport under it that needs to be moved up.  I tried using
Me![Total Due-Total Due and Payable].Report.Top = Me![Total Due-OutstandingBalanceSubRpt].Report.Top
but I get an error The property is read-only and cannot be set.  How do I move the second subreport up
If Len(Nz(Me![Total Due-OutstandingBalanceSubRpt].Report!LineTxt)) = 0 Then
        Me![Total Due-Total Due and Payable].Report.Top = Me![Total Due-OutstandingBalanceSubRpt].Report.Top
        'Me![Total Due-OutstandingBalanceSubRpt].Report!LineTxt = "No Outstanding Balances"
        'Me![Total Due-OutstandingBalanceSubRpt].Report!outAmt = "0"
        'Me![Total Due-OutstandingBalanceSubRpt].Report!sumOutAmt = "0"
    End If

Open in new window

VibertHAsked:
Who is Participating?
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
On the Sub Report Control, do you have the Can Grow / Can Shrink properties set to Yes - they should be.

mx
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Also, check those two properties in the section on the main report where you have the sub report.

mx
0
VibertHAuthor Commented:
Checked all places.  The actually subreport was no and no.  but the main subreport on the main report was yes and yes.  i changed them all accordingly and there was no change.  Still a huge white space.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

VibertHAuthor Commented:
Let me try to print it
0
VibertHAuthor Commented:
Can't get my printer working.  Is there a way to see it on the preview?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" Is there a way to see it on the preview?"

DoCmd.OpenReport "YourReportName", acViewPreview

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Can you upload to http://www.ee-stuff.com/Expert/Upload/upload.php ... removing any sensitive data of course?  

Then, provide me the link EE-Stuff gives you to the file location ... back here in this thread.

Note: There is a  2 MB upload limit.

mx
0
VibertHAuthor Commented:
I think I uploaded it for u one other time and never got a reply.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Your user name does not look familiar ...

mx
0
VibertHAuthor Commented:
0
Jeffrey CoachmanMIS LiasonCommented:
VibertH,

I looked at the file:
https://filedb.experts-exchange.com/incoming/ee-stuff/6744-Copy-of-Invoicing-System.zip
... but it does not have any Reports?

JeffCoachman
0
VibertHAuthor Commented:
No, I had to clean it out.  I'll upload again.
0
VibertHAuthor Commented:
It's using 2000 do u want me to convert it?
0
Jeffrey CoachmanMIS LiasonCommented:
2000 is best, so it is compatable with 2000, 2002,2003, 2007
0
VibertHAuthor Commented:
0
Jeffrey CoachmanMIS LiasonCommented:
VibertH,

OK, which report has the issue?

I tried to open a few of them, but they needed parameters.
Also some forms could not be found.

I think that perhaps in you zest to delete as much as possible from the DB to reduce size, you may have delete a bit too much.
:-)

Can you try again please?
Remove any sensitive data, *but leave enough data so that we can see the issue*.
Along with the post, please explain, in detail, what you are trying to achieve.

Compact/Repair the DB
Use Winzip to compress it, then post to:
http://www.ee-stuff.com
...and post the link to that file here.

We'll be waiting
;-)

JeffCoachman
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"I tried to open a few of them, but they needed parameters."

Ditto.

mx
0
VibertHAuthor Commented:
Open the Print_Invoice Form and use invoice # 11 and leave it open.  Click submit or view the report Invoice.

On the invoice there is an outstanding balance subreport in the total due section.  I change the data to say that no invoices were outstanding ie all have paid checked.  I wanted to see what happened and there is whitespace that I am trying to get rid of.  

Try this upload.  https://filedb.experts-exchange.com/incoming/ee-stuff/6788-upload-Invoicing-System.zip
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok ... I printed # 11 - 3 pages total.
Where specifically is the white space you want to get rid of ?

mx
0
VibertHAuthor Commented:
In the total due.  Is there any white space between the 'Total time and Disbursement' and Total Due?  Remember I could get my printer to work.
0
VibertHAuthor Commented:
I just got it to print and yes there is white space between the two.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
There is a little WS between
Disbursements and Total Due

More WS between Cal of Value of Time and Disbursements.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
oh, and there are 5 pages, not 3 ... and page 5 is blank
Page 4 has Sumry of Client Acct on top ... then mostly blank.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
btw ... all of the sub report controls except 1 have the Can Shrink set to No.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I'm thinking the Page breaks my be causing some space issues also.  And, there is physically a lot of WS between several of the Sub report controls.  Why?

mx
0
VibertHAuthor Commented:
--There is a little WS between Disbursements and Total Due
This is fine
--More WS between Cal of Value of Time and Disbursements.
This is fine
--oh, and there are 5 pages, not 3 ... and page 5 is blank
I deleted this info so it's fine
--Page 4 has Sumry of Client Acct on top ... then mostly blank.
This is fine

Now look under Total Due.  There are three subreports.  Outstanding balance is not showing because it did not return any records.  This is what I am testing.  I need this whitespace removed.  I set the can grow and shrink to yes on the invoice and the actual subreport.  Wonder how it got undone.  Let me look at it again.
0
VibertHAuthor Commented:
I checked and they are yes for this particular subreport.  
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Now look under Total Due.  There are three subreports.  Outstanding balance is not showing because it did not return any records.  This is what I am testing.  I need this whitespace removed."

Well, I don't see that much WS, but I opened each subreport, made the text boxes smaller, set the Detail section  (in one of the) - Can Shrink = yes.  Then, I made this height of all those sub report controls 0.08 inches high, and stacked them right below each other ... and now, there is less WS.

Looks to me you just need to do some tweaking of controls.

mx

0
VibertHAuthor Commented:
Thanks.  That seems to be the problem.  Man...  Quick question.  How do you modify the spacing of the dots on the grid.  I need for them to be smaller.
0
VibertHAuthor Commented:
They are 9x9 I need them to be 23x23
0
VibertHAuthor Commented:
Never mind.  Turned off Snap to Grid.  Thanks for your assistance.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
You are welcome

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