Down, then Across Multiple Column Report

[sierra467]

I am trying to make a multiple column report that reports the names of people present on a each day over the duration of a conference. The purpose for doing this is to save paper. I want the report to have 3 columns per date section and I would like to have it display the names in the format Down, then Across. The look I want is something like this:

______________________________________________________________________________
01/02/08                                                (Total Attendants: 20)
______________________________________________________________________________
A---------------              C---------------               G---------------
A---------------              D---------------               H---------------
B---------------              E---------------               I---------------
C---------------              F---------------               J---------------
C---------------              G---------------
C---------------              G---------------
C---------------              G---------------
C---------------              G---------------

______________________________________________________________________________
01/06/08                                                (Total Attendants: 5)    
______________________________________________________________________________
A-----------                  J------------------            N--------------
B-----------------            L----------

______________________________________________________________________________
...and so on through out the report until all the dates are shown with attendents.

So far I have:
main report (rptRoster)

• Page Setup: Portrait, Number of Columns 1, Column Size (Width: 6.5"; height: 0.25")
• dtmAttend Header: date and attendant count
• Detail section: subreport (srptRoster)

The sub report (srptRoster) has:

• Page Setup: Portrait, Number of Columns 3, Column Size (Width: 2"; height: 0.25"), Column Layout (Down,then Across)
• Detail Section: txtName

Original problem was that I was getting only one column. I read that that was because I did not have the Can Grow and Can Shrink properties set correctly. The report did not think it had to go to the next column because it had enough room to display all the data in the one column. By changing the properties, it thinks that it must display the names in 3 columns.

Now that I have that understood, I am curious how to size the sub report to the correct size depending on the number of names are going to be displayed. I can not use a specified height because if there are more names than will fit in 3 columns in the height specified, some will be left out; and if there are not enough names to fill the specified height of the sub report there will be a lot of white space.

How do I calculate and enter size the sub report to get the view I want with 3 columns?

 

[GolferGuy]

I would try to set the height of the sub-report object within the OnFormat event of the dtmAttend Header. I would divide the number of attendees by 3, then multiple that with the height of the sub-report rows (0.25"). Then multiply that by 1440, the number of "twips" in an inch. Access uses twips of setting measurements within VBA.
I hope this works for you. I think that is a great looking report, and I like the idea that you want it to look good rather than taking the easy way out.

 

[sierra467]

Thanks GolferGuy - I am glad you like the look of the report! I liked it too and wanted it to come out the way I described but had no idea where to place code or how to approach this one. I will give you suggestion a try and let you know how it turns out. You have been a huge help on getting me started!

 

[sierra467]

I arbitrarily chose 20 as a fixed number of attendents to try out the suggestion and entered the following in the OnFormat Event of the dtmAttendHeader:

srptRoster.Report.Height = (20 / 3) * 0.25 * 1440

However, all I keep getting is a run-time error (2135) stating - "This property is read-only and can't be set." Am I doing something wrong or can you just no set the height property programmatically?

 

[GolferGuy]

It looks like it can not be set programmatically. I have tried several different ways to make this work without being able to change the height of the subreport control, but all to no avail. What I tried was to create 3 subreports, one to do the first 1/3 of the name, the second the next 1/3, and then the last 1/3 of the names, then put each subreport into it's own "column". To do this, the data needs to have a ranking number so the subreport can know which records it should print. I did do the ranking number in a query, but then to select on that ranking number is too much for Access to handle. to this this it would take a work table to be filled with the names to be printed along with a ranking number for each name. Then, the individual subreports could get their data from this work table and select the first 1/3 for the first column, etc. without Access getting upset with the query being too complex. JET does have some limitations!
Sorry I could not think of anything that would work easier, but maybe you or someone else might get an idea from this brain-storming and that idea will work.

 

[sierra467]

GolferGuy,

Thank you so much for your "above and beyond the call" help! I can not believe that I am the only one who might like to do a report like this, I am surprised it is so difficult. I really appreciate all your help. I am stumped right now too, I hope someone else can lend a hand. Thanks again!!

 

[Mike Vytal]

Sierra, I too have tried to design a report very similar. First, go to the design of your subreport and choose 'page setup', then select the 'columns' tab. You can set the parameters there. But here's where the problem comes for me. If I choose the 'Across then down' option, I'll get the 3 desired columns in my subreport. If I choose the 'Down then across', I get 1 column.

I'm headed in the right direction, and hopefully this will trigger someone else's ideas and thoughts. I hope this helps you.


MV

 

[lgedeon]

Update: I got it to work! It does not show up right in report view but when I switch to print preview, it works!

 

[GolferGuy]

I arbitrarily chose 20 as a fixed number of attendents to try out the suggestion and entered the following in the OnFormat Event of the dtmAttendHeader:

srptRoster.Report.Height = (20 / 3) * 0.25 * 1440

However, all I keep getting is a run-time error (2135) stating - "This property is read-only and can't be set." Am I doing something wrong or can you just no set the height property programmatically?

I did not see this the time before, but the problem here is that the REPORT (the sub-report) is being addressed, rather than the sub-report object. If the code were stated like this:
srptRoster.Height = (20 / 3) * .25 * 1440
then the subreport object would be the object whose height was being set. Try that and see if it works.

 

[themurph2000]

I did not see this the time before, but the problem here is that the REPORT (the sub-report) is being addressed, rather than the sub-report object. If the code were stated like this:
srptRoster.Height = (20 / 3) * .25 * 1440
then the subreport object would be the object whose height was being set. Try that and see if it works.

So if I'm reading what everybody said correctly, you have to have an idea of how many rows (or attendees in this case) you would have so you can set the height of the subreport? I have a similar problem, only I don't know how many attendees would be in the individual classes. I do know I want it in two columns and for them to be balanced out as much as possible.

 

[GolferGuy]

You can run a query, separate from the one that is the record source for your report, that can give you the count of how many rows (attendees in your case) you will be dealing with. Then there will be no guessing needed.