Change page orientation in the middle of an Access report

I have a large application that sends print lines to a table along with non-printed sorting and control  fields. After the app runs, users can use turnkey form choices to push all or part of the table to several Access reports. As this is a conversion of a legacy application, I am preserving the form and logic of the original.

It is also important that I control page breaks. There is a Boolean field in the table (fPageBreak) which if true, causes a conditional field to become visible and punches out a break:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  If Me![fPageBreak] Then
    Me![CondPgBreak].Visible = True
  End If
End Sub

It works great. However, all the reports are in portrait orientation, and I'd like to spin a few pages 90 degrees for length.  Is there a way, for example, VBA code I can use behind the report, say, with a new field fLandscapeOn, which, if TRUE at the time of a page break, can change the report orientation until directed otherwise or finished, to do this?

I know I can create separate reports and direct the prints as appropriate, but that will get unwieldy in a hurry and this, if possible, is a better fit.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
because orientation is a printer property with Access, I do not think it is possibly to dynamically change the orientation in the middle of a report.  You can make multiple reports and change the numbering so it looks like one report though.  This would require coding behind each report and tracking page numbers.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
It is not possible. One report -> one orientation.

If you have to move to using several reports printed in sequence to appear as one report, here is how to control page numbering:

How combine report with new page numbering

marcchevalierAuthor Commented:
Thanks Crystal & Gustav for saving me from wasting time trying to do this directly from Access reports. I will have to chain portrait and landscape orientations with multiple reports. So now I am looking to preserve my easy-to-use print mechanism, if I can. Here is a little more information:

1. I am in Access 2007 (oldest common version among my users).

2. Page numbering is not an issue (controlled within headers of internal reports).

3. I create reports into a single packet for each of several user groups based upon checkbox selections on my form:
  a. Paper or preview
  b. select any or all reports you want in the batch

Right now, that batch is a single Access report of the query filtered by the checkboxes which I can pop up as a preview or throw directly to the printer. Since the batch will now be multiple reports with mixed orientations, is there a way to combine them into a single, one pop-up preview either through Access directly or saving the mixture into a PDF and previewing that?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Gustav BrockCIOCommented:
Then you would have to print them to a PDF file, number two and three appending the first. It may not be that easy as it can be difficult to know when the first is saved and ready for an appendix.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I've never thought of trying it, but I'm wondering if sub-reports would let you do this.   Probably not, but it might be worth a quick test.

 Sounds like gustav has the best idea though.

marcchevalierAuthor Commented:
Tried the sub-report route. Regardless of its saved orientation, it defers to the main form's layout.

I am going to try the PDF-appending tomorrow.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Didn't think so, but worth a quick check anyway; sometimes you do get a surprise<g> and I don't remember the question ever coming up before.

Thanks for posting back on that.

Jeffrey CoachmanMIS LiasonCommented:
This question has been asked a few times before...

My thoughts are that if certain pages need to have a different orientation, ...then they, perhaps, need to be separate reports...

For example, if you have a report grouped by customer, why would a certain customer's data need to be rotated?
The design of a Portrait report cannot automatically change and "stretch" the controls to fit Landscape. (and vice versa)
So you would basically end up with a Portrait report printed on a landscape page...

You can do these types of things (rotate orientation) in Word because there is no real structured/rigid "design" framework behind the report document.

I think we are also missing a clear, graphical example of a situation where this would be needed...

marcchevalierAuthor Commented:
Yes, Jeff, I think we have established swapping orientations mid-report is outside Access's skill set. That is what my review of Access docs seemed to reveal and I was looking to see if someone had a useful workaround.

This app is atypical and not a standard customer data outputting from tables. It is a large VBA managed simulation where Access is used to hold results, variables, parameters, etc. and manage a local server reached by students and executives over the web.

There are legacy reports, both administrative and participant, and there is much to be gained in the transition process by keeping familiar formats, the "look & feel" of output used over 30 years. Imagine some, not all, reports were built around 5 teams and columns of financial and other information just barely fit horizontally on a portrait mode page. Now imagine the game is changing to accommodate 2 to 8 teams and you wish to stay in the familiar portrait mode for 2-5, but flip to landscape so that 6-8 will fit. One example.

We have sent Access output to Word, Excel, PDFs before and will go that route, if necessary.

Just testing Access's limits.
marcchevalierAuthor Commented:
As I mentioned, I was looking for confirmation I could NOT change orientation and/or a work-around. Crystal first confirmed I could not as did Gustav shortly after who also provided the page-numbering and PDF tips.

I am temporarily using multiple reports while remaining in Access reporting, and moving to Word objects when it becomes necessary.

Thanks to all!

Gustav BrockCIOCommented:
You are welcome!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.