Set Subreport Control Properties With VBA

[gr8dane]

I have placed three unlinked subreports in the Report Header of an unbound main report and I want the records in each subreport to be listed in two equal-height columns (down, then across). Setting CanGrow to "Yes" in the subreports will cause the records to appear in a page-long column on the left (in Print Preview) before starting to populate the right column. But when I manually set the subreport control (i.e., the subreport's container on the main report) to a short enough height and set CanGrow to "No", the records in the subreport (or as many of them as will fit) will populate both columns.

So, in the ReportHeader_Format event of the main report, I calculate the height needed for each subreport. Then I try to set each subreport control to its desired height with this:

Me.Section(acHeader).Controls(strSubreport).Height = intControlHt

Not only does this not work, but Access stops responding until I end the task. The reference to the control in my code works to GET the height, but not when I try to SET the height. What am I doing wrong?

 

[DHookom]

How do you get the value for intControlHt and is it an integer or long? What event are running this code in? I don't believe you need the section reference. Me.Controls(strSubreport).Height should work fine.

 

[gr8dane]

How do you get the value for intControlHt and is it an integer or long? What event are running this code in? I don't believe you need the section reference. Me.Controls(strSubreport).Height should work fine.

In the ReportHeader_Format event of the main report I divide the number of records by 2 and multiply that by the row height in the subreport detail (in Twips). The resulting value of intControlHt is an integer. I've tried with and without the section reference; both cause Access to stop responding.

It just occurred to me: Do I have specify somewhere in the code that I'm using Twips?

 

[DHookom]

Twips is the default unless your code changes it. I expect I will need to create a report and test the code. If I was you, I would create a dumbed down version of a report for troubleshooting.

 

[gr8dane]

Twips is the default unless your code changes it. I expect I will need to create a report and test the code. If I was you, I would create a dumbed down version of a report for troubleshooting.

Not sure what you mean. As I said, I have created a report with subreports and tested the code (on only one of them, so far).

 

[gr8dane]

If I was you, I would create a dumbed down version of a report for troubleshooting.

OK, I've attached a simple version of my DB. The four subreports in rptMainReport show what happens when I manually set the Height and CanGrow properties of each subreport container (#4 is what I want to see happen). This is with the last line of the ReportHeader_Format event commented out. If I uncomment it, Access becomes unresponsive as before. But, unlike before, it now shows a "No current record" error before it stops responding. Very mysterious.

 

[DHookom]

When I need a report to seem like it is rendering down then across but it's a subreport, I change the record source to add a calculated column that creates a value when sorted across then down, looks like down then across. The Record Source of subreport is:

SELECT tblItems.ItemID, tblItems.Item,
(([ItemID]-1) Mod DCount("*","tblItems")/2)+Int(([itemID]-1)/(DCount("*","tblItems")/2))*0.001 AS SortColumn
FROM tblItems;

Then change the subreport to render across then down and set it to can grow.

 

[gr8dane]

(([ItemID]-1) Mod DCount("*","tblItems")/2)+Int(([itemID]-1)/(DCount("*","tblItems")/2))*0.001 AS SortColumn

I don't like using code I don't understand. Could you explain this, please?

 

[DHookom]

That expression is used to number your records in your subreport in a way that when rendering across then down, it actually seems like you are rendering down then across. You want the sequence of numbers to be generated so the first record is always given the lowest number. The next lowest number will be 1 record past the middle. The third record would actually be the second in the original sort.

I built a spreadsheet in Excel to determine what the expression would be to create new sequence numbers for each record in the record source. I have done this several times in the past but I always forget the calculations. It took some trial and error which is why my post was at 1:33 AM .

 

[gr8dane]

That expression is used to number your records in your subreport in a way that when rendering across then down, it actually seems like you are rendering down then across. You want the sequence of numbers to be generated so the first record is always given the lowest number. The next lowest number will be 1 record past the middle. The third record would actually be the second in the original sort.

I built a spreadsheet in Excel to determine what the expression would be to create new sequence numbers for each record in the record source. I have done this several times in the past but I always forget the calculations. It took some trial and error which is why my post was at 1:33 AM .

Thanks for putting so much effort into this, but it still doesn't make sense to me. First of all, there's a "ORDER BY SortColumn" clause missing from the SQL. And then I think the syntax of the Mod part is incorrect and "*0.001" probably belongs inside the last parenthesis:

(([ItemID] - 1) * (MOD(DCount("*","tblItems"), 2)))+ Int(([itemID] - 1) / (DCount("*","tblItems") / 2) * 0.001)

But even so, assuming the record count is 5 and the first two ItemIDs are 1 and 2, I think the value for SortColumn would work out like this:

((1 - 1) * (MOD(5, 2))) + INT((1 - 1) / (5 / 2) * 0.001)
(0 * 1) + INT(0 / 2.5 * 0.001)
0 + 0 = 0

((2 - 1) * (MOD(5, 2))) + INT((2 - 1) / (5 / 2) * 0.001)
(1 * 1) + INT(1 / 2.5 * 0.001)
1 + 0 = 1

I'm making a few assumptions about what you meant, but this is how I interpreted it and it wouldn't change the sort order.

The values in ItemID and SortColumn should line up like this...
1 1
2 3
3 5
4 2
5 4
... in order for the ItemID to sort like this (across, then down):
1 4
2 5
3

Please let me know if I'm misunderstanding something.

 

[DHookom]

You seem to understand the numbering scheme. I believe the file I provided had the correct results with data provided. If there is an odd number records or the ItemIDs aren’t consecutive you will need to test and modify.

 

[gr8dane]

You seem to understand the numbering scheme. I believe the file I provided had the correct results with data provided. If there is an odd number records or the ItemIDs aren’t consecutive you will need to test and modify.

Sorry, I didn't realize you had made changes to the DB I sent. I do see that your code works, at least with my sample data. But I still don't understand how it works. As I explained above, it seems to me that it shouldn't work. It is more likely than not that the actual data will include non-consecutive ItemIDs and have an odd number of records, and I can't modify something I don't understand.

In any case, even though I know there are alternatives, I would still like to know why my approach doesn't work. Why can't I set the height of the subreport containers?

 

[DHookom]

I think your approach doesn't work (not for me either) might be caused by trying to calculate heights while setting heights. Maybe if the code was another, earlier event.

I don't believe your original subreport had a sort order which is typically a mistake if you expect a specific order. If you don't have a sequential number to use for the solution I provided, you will need to create one. Once the sequential numbers are created, you need to assign an order which you already described in your example of from 1-5 records.

 

[gr8dane]

I think your approach doesn't work (not for me either) might be caused by trying to calculate heights while setting heights. Maybe if the code was another, earlier event.

I was hoping you were right, so I moved everything except the line that sets the height into the Report_Activate event. The results were unfortunately exactly the same as I described in post #6. Any other suggestions?

 

[DHookom]

I could get a rectangle height to change as expected but never a subreport with records. My solution has worked well for me for decades so I'm not sure I have the bandwidth to do more testing.

The one suggestion I have (not tested) is to open the report in design view and set the height of the subreport. Then Save and reopen.