Report - Subreports row heights to match

I have a report that has two sub-reports, side-by-side.  And there are lines as you can see in this example.  My issue is this..  The fields are can grow fields.  But if the depth of one field makes the detail grow vertically, I need the other report detail to grow to match.  Can this be accomplished?  If so, how?

Row Height Example
Who is Participating?

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

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.

Gustav BrockCIOCommented:
Not that I know of. No event to catch, and no individual setting for row height.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Interesting problem and I think it might be possible based on the testing I just did.

 I didn't work out the whole thing, but it turns out you can adjust the height of a section on the fly with Me.Detail.Height = xxxx

 where xxxx is in twips.

 Would you'd have to do is:

1. Execute subreport 1 in two pass mode (refer to Me.Pages in a control).
2. Add a dummy group so you can have a  footer after each detail section.  Height of the footer would be 0"
3. In the dummy group footer, record the place you are on the page.  Difference between this and the last group footer section would be the height of the last detail section.

  For the first section, you'd need either a known value, or record it  at the end of the page header (assuming no other groups), or in the first detail section on the page.

4. On the second pass, the reports would actually print and you could look at what you recorded and set the height of the detail accordingly.

 As I said, didn't work the whole thing out, but it seems like you could get there.  

  I'm really short on time right now or I'd give it a go.

Dale FyeOwner, Developing Solutions LLCCommented:
It looks like you are assuming that the two reports are returning the same number of records, because you only have the row descriptions on the left hand report.

If that is the case, join the recordsets of the two reports in a query and make it a single subreport.  Then the row heights will grow together.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SteveL13Author Commented:
Actually I think I found an easy way to handle this.  Both sub-reports have two identical fields, which is the very left-hand field in my screenshot. The right-hand subform has that field hidden.  I made those two fields very short vertically and made them can grow.  Because the height I made them is too short to display the text they have to grow and because they are identical, they grow to the same height.  Then I found that I had to un-hide the right-hand field and make the text white.

Seems to work and was easy to accomplish.
Gustav BrockCIOCommented:
That's a neat solution.
SteveL13Author Commented:
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Isn't that only going to work though until the data in SR1 is larger than the left hand field?

SteveL13Author Commented:
Because the two fields contain exactly the same information, they both grow the same amount.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
to extend  and combine your concept and Dale's, could you not add the fields from each subreport to the other, so that the height of the row will be driven by the tallest control?

 The only problem is how to not show them on the other sub-report.   Try leaving them hidden first as that would be easiest.  Not sure if that will cause them to be ignored though.

 The other way to try would be make the font white, with the background and boarder transparent.   Issue there though is if you lay it over the top of another control and move it to the back, I think that prevents the foreground control from growing or shrinking.  But give it a try.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Because the two fields contain exactly the same information, they both grow the same amount.>>

They don't in your image.  I'm talking about the 1st column (which is duplicated in your sub-reports) and the 2nd column, which is not, but is the one which is driving the row height increase.

the Field on the left in the second row has one row of data.    Column next to it has two; what if it had three or four?  

SteveL13Author Commented:
I may not be explain completely.  The two columns are the very left-hand column and the "hidden" column in the right-hand subreport,  It isn't really hidden but it is white text.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
SteveL13Author Commented:
Jim...  good catch!  Back to the drawing board.  If I can figure it out I'm going to try your suggestion next.
Dale FyeOwner, Developing Solutions LLCCommented:
like I said originally, if you have fields common to both sub-reports, join the queries used for each and create a new sub-report based on those.

However, if the two recordsets don't have exactly the same number of records, then make sure you use a union query that equates to a FULL OUTER JOIN

SELECT A.ID as ID, A.SomeField, B.SomeField2
FROM tblA as A LEFT JOIN tblB as B on A.ID = B.ID
SELECT B.ID, NULL as SomeField, B.SomeField2
FROM tblB as B LEFT JOIN tblA as A ON B.ID = A.ID

Open in new window

First part gets all records from A and matching records from B.  Second part gets all records from B which don't match records in A.

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
SteveL13Author Commented:
Dale, thank you for pointing out the most obvious solution.  Sometimes I can't see the forest for the trees.

Jim, thanks to you for the great catch.  My solution was never going to work.
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.