In the past 2 day’s messages I have been wrestling with calculating the height of a report. In particular the detail section of a report inside a subreport control on a form in which the report is shown in report view (not print preview).

This situation creates unique challenges. Yesterday I had gotten to the point where I could calculate the height of each detail section in the report by calculating the height of the text given a certain width for the control I had set to use the property CanGrow = Yes.

The main reason for the challenges? That you cannot retrieve the modified height of the control via normal Access events and properties when using Report View.

I was able to utilize code from a free project for an Enhanced Message Box for VBA which uses the Windows APIs to get the height of the text. It’s pretty complicated compared to the alternate procedure to get the height in Print Preview mode.

So after creating the function to get the height of the text based on passing a control and the text, I was able to put this into a function. BUT, then trying to use this function within the ControlSource property of a control and referencing that control from my routine that is looking at the control to get the report height, caused an ERROR!!! Oh No!!!!!

But finally, today, I was able to generate the height the detail sections would be by iterating over the recordset in the Report Open event. Whew!

So ultimately, I created a function to iterate the report recordset. You can’t do this directly like you can with a form. A form has a recordsetClone property to get a copy of the form’s recordset. A report does not have this. However, I was able to use CurrentDb.OpenRecordset(Me.RecordSource).

Me.RecordSource contains the SQL or query or table that the report is based on. This can also be affected by the Report.Filter and Report.OrderBy properties, but in my case I wasn’t using those.

This technique allowed me to open a recordset that paralleled what would be on the sub report and get the text for each detail record. I was able to pass each text value to the function to calculate the height for each row and add them together to get the final height of the detail section.

All right! On to the next adventure!