About Hide Blank Columns on Report

[Rhino999]

My original contact to the Forum was at
http://www.access-programmers.co.uk/forums/showthread.php?t=291003&highlight=HideBlankColumns

I initially contacted the forum regarding a Sample Database to Hide Columns on a Report
Located at http://www.access-programmers.co.uk/forums/showthread.php?t=232405

CJ_London and pbaldy pointed out some issues I was having with the original Sample Database.
That resolved, I was able to implement the code into my Report.

Now that the report appears with all the Blank Columns excluded properly, in Print Preview, the Paper has significant white space on the right and I would like to change the Paper Size from Legal to Letter. Getting the Report to Print on the smaller Letter size paper with empty columns removed, was the entire purpose of implementing the Code and subReport into the Main Report Header.

Unfortunately, I am unable to figure out a way of changing the paper size once the Main Report starts executing the code in the OnFormat Event of the Report Header. I tried everything I could think of.
While I can change the Page with the code below, it actually doesn’t get changed until I close the Report and reopen it. At that point the Printer Paper may be the wrong size for the next set of Data being printed.

I guess I could open a new Private Sub or Macro before the Main Report opens and reset the Paper to acPRPSLegal on the Main Report to process the next set of data, but haven’t attempted to do that because I’m getting an Error when the Main Report is Printed out of the Print Preview view. It only happened when I have removed Blank Columns on the Report. It does not happen when all the columns are filled in and the Report is going to be printed on the Legal Size paper.

The Error I’m getting is:
‘ A custom macro in this report failed to run and is preventing the report from rendering’

This the Code I tried to use to change the Page Size, it doesn’t work on the current iteration of the Report.

Private Sub Report_Page()
If Me.TestLetterSize = "yes" Then
Reports!RptCustJobDetails.Printer.PaperSize = acPRPSLetter
Reports!MainReport.Printer.ItemSizeWidth = 15120 ‘ 10.5 inches, in Twips, for Letter, minus Margins
DoCmd.Save acReport, "MainReport" ‘ this ends up saving it for the next Report, but not the current
Else
Reports!RptCustJobDetails.Printer.PaperSize = acPRPSLegal
Reports!RptCustJobDetails.Printer.ItemSizeWidth = 19440 ’13.5 inches, in Twips for Legal, minus Margins
End If

End Sub

My research has now let me to believe that there no easy way of doing this.
If anyone has any good ideas about how to resolve this please let me know.

Thank you in advance for your help.

 

[static]

Private Sub Command1_Click()
    MyPrint "report1", acPRPSLegal
End Sub

Public Sub MyPrint(ReportName As String, sz As AcPrintPaperSize)
    Dim rpt As Report
    DoCmd.OpenReport ReportName, acViewPreview
    Set rpt = Reports(ReportName)
    rpt.Printer.PaperSize = sz
    rpt.Print
    DoCmd.Close acReport, ReportName
End Sub

 

[Rhino999]

static,
thanks for responding.

Just to make sure that I understand, your solution is to Click a button on a Form and run code before the Report runs to reset the Report back to Legal size Paper, right! But that doesn't solve my problem of print the Report on the appropriate size paper in the first place, if I have correctly understood your response, right.

But my primary objective is to change the Paper Size on the Fly (meaning the Report is already Open, so I can't Open it again to change the size and close it in Design View, right) when the Report is looping through all of the Detail Controls to figured out which Columns are Blank and which Columns are to be moved to the Left to be printed. Then continues on to figure out if all the Columns to be printed will fit the Report on a Letter size Paper, if not, then Print the Report on the Legal size of Paper.

I hope I have explained my self correctly!
Thanks again for you Time and help.

 

[sxschech]

What about adding some additional code (query, dlookup, etc) to count the number of cols in the recordsource query and then use an if statement to change the paper size. Not knowing if cols are being excluded due to criteria or blank, here is an example with blank cols. Something like if col1="",1,0. Add up the 1's and you will have the col count, then you could say if no_of_cols=10 then legal else letter. If the blank cols are defined by criteria, you would need to use similar criteria in the if statement to derive whether should be 1 or 0. All this would have to be done before you open the report.

Private Sub Command1_Click()
No_of_Cols='Put formula, dlookup, if statements here'
If No_of_Cols=10 THEN
    MyPrint "report1", acPRPSLegal
Else
   MyPrint "report1", acPRPSLetter
Endif
End Sub

 

[Rhino999]

sxschech, thank you for responding.

Your suggestion is a good one!

I have thought that if I can't get a resolution of modifying the Print Setup on the Fly, I would have to create something on the order of what you said, measuring all the Columns in Twips and then when the Blank Columns add to 3 inches or 4,320 Twips, open the Report in Design View and set the the Printer Setup to either Letter.

The one thing I have in the back of my mind is that when the Print Setup is set to Letter, it will not match the Report Layout which is Legal, 13.5 inches wide plus .5 inches of Margins. When I currently use the Report with data that is appropriate for the Letter Size paper and having previously set the Printer Setup to Letter before running the Report, I get the Macro Error when trying to print from the Print Preview screen.

I don't know what that Error is or how to find the problem.

Thanks again for you help.

 

[static]

that doesn't solve my problem of print the Report on the appropriate size paper in the first place, if I have correctly understood your response, right.

As far as I know, Access should use whatever setting was last saved. Open the report in Print Preview, select the page size, save.

Otherwise an example file to illustrate the problem would help.

 

[Rhino999]

static, thanks for responding.

I'm not sure I understand what you mean. It may be related to the way I have explained it.

The Report was Designed for Legal Size. It's only when data from a Form that has not been fill in completely, that I evaluate the empty Columns in the On Format Event and if enough columns add up to 3 inches, I attempt to change the Paper Size to Letter, at that point the Report is Printed to the screen in Print Preview mode.

What I think you are saying is after the User see's the Print Preview Screen, they can manually change the Page Size to Letter and then Print, do I have that right?

I actually have just done that and it changes the Paper Size properly, but I was trying to not have the User get involved in changing the paper Size.

Additionally, after I changed the Size in Print Preview, I'm still getting the rendering Error. I'm pretty sure that the rendering Error is coming from the On Format Event because it doesn't happen when the Columns have not been made invisible and shifted left in the On Format Sub.

I'll have to go through the On Format Event to see if I can find where the problem is occurring, because I've tested the original Sample Database HideBlankColumns.mdb that inspired me at
http://www.access-programmers.co.uk/...deBlankColumns
and it doesn't NOT have a rendering problem.

Do you know how I can trap the Error that is most likely occurring in the On Format Event because I'm not seeing the Error when I step through the Code? The way I'm going to try to find the Error, is to edit out the Code one statement at a time, until the error message goes away when the Report is Printed out of the Print Preview screen.

Thank you for your Time

 

[sxschech]

My suggestion was to evaluate the number of cols prior to opening/preview the report, you shouldn't need to look at twips etc for this particular evaluation, so that when you open the report, you already know which size to choose. If I'm understanding what you have tried, it seems that you are doing this after the report is already in preview, rather than before the report is even opened. Are the number of fields to be used on the report coming from the form or from a query? Are hidden cols determined by the fields of the query/form being null or empty or are there other criteria used to determine if the col on the report should be visible or hidden?

 

[Rhino999]

sxschech, thank you for looking at this for me.

I'm doing it the same way it was proposed my vbaInet at
http://www.access-programmers.co.uk/...d.php?t=232405
The Sample File is HideBlankColumns.mdb , please take a look at what he did.

There is a Query and a Report for that Query. That Report is inside the Report Header of the Main Report and executes the Code in a On Format Event to hide and Shift the dot Left positions of all COORDINATED Controls and Labels. The word Coordinated is key to having it work, because if the Names of the Controls on the Sub Report and the Main report are different, nothing works.

Calculating the Columns for Size or Twips is not a real issue, I'll be able to handle that. I just make sure I have 3 inches of Columns that are Blank.

As per the Sample Database above, determination of the Detail columns that are Blank are coming from the result of the Query Report inside the Main Report Header. That is how the On Format Event Code knows to do the work of shifting Left. When finished the Report is Printed to Print Preview screen.

One thing I did different than the Sample database was that I have 14 Controls in the Detail Line and I only had 8 of them in the Query and Sub Report, because I knew the other controls will never be empty.
This could be an issue, I'm not sure. I'm in the process of adding all of the detail Controls to the Query and Sub Report to make sure that they match my Main Report, doing it exactly as it was done in the HideBlankColumns.mdb Sample Database.

The last thing is, since I found out that Access has no way or modifying the Setup Print on the Fly, I'll have to run a Query and sub Report before I open the Main Report. That way I'll have the information of which Columns are empty and can set the Report to Letter or Legal before the Main Report is run.

I hope I have answered your questions correctly.

Thanks again for your help.

 

[Rhino999]

I would like to thank everyone for helping me.

I finally found the problem with the Print Preview rendering Error.
'A custom macro in this report failed to run and is preventing the report from rendering'
It was because I was Opening a Template Report in Design View hidden mode in the On Format Event. Microsoft Access did not like that!

I was using a Dummy 'Letter' Size Report as a Template to determine the .Left positions all of the non Detail Section Controls when the Report is changed from Legal to Letter Size.
I removed the Open from that Event and placed it in the beginning of the Macro that also invokes the Main Report via a Command Buttons on the Form, that worked.

Thank you again for all of your help!