Report Footer printing on page 2 when using columns in detail

I have a report that I use columns in the detail so that there are 2 columns on the page going up to down and then left to right.  The detail is also set to Keep together because it contains several lines.  I also have a report footer on the report.  The report is working fine for the most part except for one the detail goes into the second column but doesn't use the entire second column.  When this happens the report footer gets printed on the next page, however if the bottom detail in the first row appeared on top of the second row there would be plenty of room for everything to be one page.  This is what it looks like:

Page 1:
----------------------------
      Approval Report
App1       App6
App2       App7
App3
App3
App4
App5
---------------------------

Page 2:
---------------------------
Report Footer
Total of Approvals: $100



---------------------------

I would really like for the report footer to print directly under App7 (it is the same width as the details).  But if that can't be done then I would at least like the report to move App5 to the top of the second column so that there is room for the footer at the bottom of the page.  Thanks.

Ben
valencehealthAsked:
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.

Rey Obrero (Capricorn1)Commented:

have you tried to make adjustment to the detail, header/footer to only accomodate a certain number of rows to be printed.

valencehealthAuthor Commented:
How do you do that?

What I have tried is using subreports, but that gave me the same exact results.
Rey Obrero (Capricorn1)Commented:
try forcing a  page break using codes
place a textbox {txtRows }, you can hide this
set the following properties of the textbox
control source =1
running sum: over group

then place this in the reports module

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If txtRows Mod 5 = 0 Then   ' << change 5 to any number
        Detail.ForceNewPage = 2  
    Else
        Detail.ForceNewPage = 0  
    End If
End Sub


see if that will work for you


CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Rey Obrero (Capricorn1)Commented:
sorry, i  think that will not work..
you need to force the next record to the second column not on the next page
Rey Obrero (Capricorn1)Commented:

can you post your db here       http://www.ee-stuff.com/
i think i have an idea on how to do this
valencehealthAuthor Commented:
OK, I just uploaded it, I hope it worked.

Just so you know what you have to do.  Open up the form frmAppPrint.  Select a Client and it should bring up a bunch of approvals (if there aren't any or there only  afew choose a different client).  Select about 10 approvals to be printed by using the check boxes, they print out in different sizes but 10 should make it so that it uses the second column but still has room left.  Click the print button and you will see the report I am talking about.

You will see a lot of numbers on the report, those are probably names and addresses that I cut out to make this database for you to look at.  I did test it however and it should be working fine.

Let me know if this doesn't make sense.

Ben
valencehealthAuthor Commented:
I don't think my first upload worked so I uploaded it again.  Here is the url for the file:

https://filedb.experts-exchange.com/incoming/ee-stuff/749-Approval-database.zip
Rey Obrero (Capricorn1)Commented:
valencehealth,

how about  setting the column layout
        Across, then Down

now you can force a  page break using codes
place a textbox {txtRows }, you can hide this
set the following properties of the textbox
control source =1
running sum: over group

then place this in the reports module

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If txtRows Mod 12 = 0 Then   ' << change 12 to any number
        Detail.ForceNewPage = 2  
    Else
        Detail.ForceNewPage = 0  
    End If
End Sub





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
valencehealthAuthor Commented:
Yes, going accross then down does solve the problem of the footer on page 2, there is actually no need for the page break code because it will automatically break at the bottom of the page.  However I was hoping to get the report to go down then accross, that is how our clients are used to seeing this report look.  If you don't think its possible to it this way then going accross will be fine.

Also, counting rows is problematic becasue I have the can grow/can shrink properties set up on the detail section and some records can probably be twice as big as others.
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.