Question About Columns in a Subreport

[jt463]

As a follow-up to thread703-1347172, I wonder if anyone knows how to achieve columns with a subreport?

I set the page setup to be "down then across", and the actual subreport (by itself) works great - two columns. Then, when it is part of the main report, the data never spills over to a second column. It just keeps going down.

I can't figure out how to limit the size of the subreport and force it to start a new column.

I can get the two-columns to work if I change the setting to "across then down", but this makes my list difficult to follow since I have a header then associated data to the right and then below left, etc...

Any help would be greatly appreciated!!!

 

[dhookom]

You can create a value in your subreport's record source that can be used to sort for across then down but render as if its down then across. For instance if you had a table of months in a 2-column subreport and you wanted to display these Jan-Jun in the left column and Jul-Dec in the right column. You would need a column to calculate the Seq column:

[tt]
Mth Seq
Jan 1
Jul 2
Feb 3
Aug 4
Mar 5
Sep 6
Apr 7
Oct 8
May 9
Nov 10
Jun 11
Dec 12
[/tt]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jt463]

Thanks Duane.

I think I understand the concept, but I am having a hard time wrapping my mind around how to use your example and achieve what I am looking for.

My scenario is that I have a report that contains several items per "report category":

Category Item
Plumbing Toilet Upgrade
Plumbing Fixture Upgrade
Electrical Light Upgrade
Electrical Cable Upgrade

Based on this scenario, how would it fit in with what you have suggested?

I would want my report to look like this:

Plumbing Cable Upgrade
Toilet Upgrade
Fixture Upgrade
Electrical
Lught Upgrade

Thanks!

 

[dhookom]

You could just set the sorting and group in the subreport to Category Desc and then Itme Desc. Fix the height of your subreport to display only 5 lines and don't allow it to grow.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jt463]

How do you set the height of my subreport to display only 5 lines? I only see a 'width' setting.

I think you are on to something here. Thanks for your time.

 

[dhookom]

You size the subreport control like you would size any other control. Either type something into the Height property or grab and drag the bottom of the subreport control.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jt463]

Duane

Thanks so much for your help. I am getting closer. I tried your suggestion. It limits the height of my subreport and sorts/groups exactly how I want. However, with the control set to 'Can Grow = No', the second column never begins. The data just stops at the bottom. I know there is more information, but it just stops at the bottom of column 1. I have my width set to wide enough that there is enough room for two columns.

So, I am getting closer. I just don't know what to tweak to get it to spill over to a second column.

Thanks for any additional help you can provide.

 

[dhookom]

Make the subreport control on your main report wider. If you can't get that to work, figure out how you can use Across then Down but appear to be Down then Across.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jt463]

Thanks again, Duane.

Making the subreport wider did not do the trick. I ran out of ideas on how to tweak the 'across then down' format to make it look like 'down then across'. I will keep working on it.

If anyone comes across any additional suggestions on how to make a subreport spill over to a second column, I would be grateful!

Thanks.

 

[dhookom]

Come back with the page layout properties of your subreport.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jt463]

By page layout, do you mean open the supreport by itself and provide the 'page setup' details or the information from the 'format' tab of the subreport properties? The reason I ask is because the layout and properties are for the master report when I try to look at supreport settings within the large report.

Thanks.

 

[dhookom]

I would be interested in seeing the page layout properties of your subreport.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jt463]

Subreport (Page Setup):

1. Margins (all = 0)
2. Page = Portrait
3. Size = Letter
4. Source = Default Tray
5. Printer = Use Specific Printer (Document Image Writer)
6. Columns = Number of Columns = 2; Row Spacing = 0"; Column Spacing = 0.25"
7. Column Size = W = 2.65"; H = 0.166"; Same as Detail = "not selected"
8. Layout = "Down then across"

Let me know if this is not what you are looking for. Thanks for your help!

 

[dhookom]

If you have missing records from your subreport, make sure the subreport control is set to a width of at least 5.5" The width of the subreport should also be set to 2.65"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jt463]

Thanks for the input.

I changed the control width to 5.5" and then 6". Both resulted in the data not spilling over to a second column. I wonder if it has to do with the fact that my subreport has a Report Header and ReportCategory Header?

I think we are so close. It must be something small I am missing.

I continue to be grateful for your help.

 

[dhookom]

How about "The width of the subreport should also be set to 2.65"?

Group or report headers or footers shouldn't make any difference. I would however check to see if these have any "Force Page" property values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jt463]

FYI...

Group Properties:

1. Force New Page = NO
2. New Row or Column = No
3. Keep Together = Yes
4. Visible = Yes
5. Can Grow = Yes
6. Can Shrink = Yes
7. Repeat Section = No

And the width of the subreport (in Page Setup) is set to 2.65". The width on the control property of the subreport is 6".

 

[dhookom]

No idea why this isn't working for you. I would try recreate a simple report with a subreport that does work. Find out what the difference is between a solution that works and one that doesn't.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]