Multi-Column Sub Report

I need to create a subreport (the second of two in the detail section) that contains four columns.  I want to use the down/across option.  

So far, the subreport will not create four columns.  It creates only one UNLESS I change the CanGrow property of the subreport control to No to limit its size to what I have actually drawn on the detail section.  The problem with this is that Access limits the number of records that are displayed and will not produce additional pages.  So if I set the height to accommodate 6 rows by 4 columns, I see 24 records.  But if the client has 25, I'm SOL so that doesn't work.  I tried changing to the across then down which does produce the four columns and will paginate correctly but it confuses the user.  He has been using a FoxPro report for 15 years and doesn't want to change how it looks.

Does anyone have any ideas for another alternative?  I was thinking about trying to use Excel but my head started spinning.  I might give it a whirl if I had only the one subreport but I have two so getting it to populate correctly is well beyond my ability to automate Excel.
LVL 42
Who is Participating?
PatHartmanAuthor Commented:
Hi Jim,
That didn't work either.  So far the only thing that will make the down then across function the way I think it should is to change the can shrink/can grow properties of the subreport to no.  But, that isn't a viable solution since it truncates the data that won't fit in the window.

I wouldn't spend any more time on this unless you want to solve this for yourself.  I'm going to set the report to across then down and hope the users can learn to live with it.

Thanks for your help.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Does anyone have any ideas for another alternative? >>

I have an idea, but I want to understand the requirement first.

So the current report runs down the first column till it hits the bottom of the page, then goes to the second column, down to the bottom, then 3rd, 4th, and then flips to a new page if it needs more?

PatHartmanAuthor Commented:
What I want is down and over:
1  5  9  13
2  6 10  14
3  7  11 15
4  8 12 16
What I get is:

I can get down and over to work but ONLY if I set the CanGrow property of the subform to No.  But that has a nasty side effect in that once I choose a size, any records that don't fit are discarded.  The subform will NOT continue on the next page.
What I CAN get if I choose over and across
1 2 3 4
5 6 7 8
9 10 11 12
13 14 15 16
This last option at least gets me the four columns and it paginates correctly but it is confusing to the users since the FoxPro report they have been working with for 15 years works like example 1.

The above examples show 16 records.  I have clients with as many as 150 so the subform MUST grow and paginate correctly.
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 I don't have anything handy to check this with right now, but make the following changes and then see if the down and across setting works as it should:

1. Make the page footer visible and make very thin.
2. Un-check the "Same as detail" in the page setup.

 Not sure if that will work or not; sub-reports are such odd ducks.

 I've got a couple more things I want to try here, but give that a shot first.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I wouldn't spend any more time on this unless you want to solve this for yourself. >>

 I like the odd problems.  Let me see what I can come up with.

PatHartmanAuthor Commented:
This appears to be a bug in the reporting engine.  There isn't a suitable solution that I have found as yet.  I ended up using the across-then-down option of ordering the items in the subform.  This is awkward but the users will ultimately get used to it.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.