Is it possible to freeze the Page Header and/or first column of a Report used as a sub-form?
I have created a small database that imports data from an accounting package and displays results in a specific format. I display the data using a sub-report, rather than sub-form because it easily allows for grouping and sub-totals. I have placed a combo box on the form to enable preliminary data filtering. I am using Access 365, which supports double-click events on a report cell, which I use to facilitate drill downs on the data. I plan on inserting some check boxes on the parent form to allow the user to select which columns to print so the desired result will fit on a single sheet of paper. i can also use the same check boxes to limit the columns displayed on screen if the user has a narrow monitor (e.g. a laptop)
The only drawback is that I haven't figured out how to display the column headers on the sub-report. Once I scroll down the sub-report headers disappear and don't readily come back. I could put them on the form but then I run into issues syncing the form and sub-report if the user scrolls across the page using a narrow screen size. Ideally I would like to lock both the header and a couple of the sub-report columns. Is any of that possible, and if so, how?
The only drawback is that I haven't figured out how to display the column headers on the sub-report. Once I scroll down the sub-report headers disappear and don't readily come back. I could put them on the form but then I run into issues syncing the form and sub-report if the user scrolls across the page using a narrow screen size. Ideally I would like to lock both the header and a couple of the sub-report columns. Is any of that possible, and if so, how?
Asked:
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
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
Hi Jeffrey,
Thanks very much for your comments. You've confirmed what I thought but Experts always amaze me with what you're able to accomplish so I thought it wise to ask. I also hoped that MS may have added some functionality to freeze panes and columns of a report when they introduced the expanded features and I just didn't realise it.
This application is actually best suited to a pivot table but the user is used to an access program I wrote for him 10+ years ago that generated half a dozen reports based on data extracted from his accounting system via an ODBC Link. A recent upgrade of that software meant the link would no longer work so he asked me to find another way of providing the data. This new functionality in MS Access made it very easy to give him a report that provides the perfect overview - everything he wants to see on one, simple report. And he now has the ability to double-click on a column to open a second report that gives a list of the detailed data that went into the summary overview. So it was too easy and too tempting not to use the report as a quasi-form.
I will do exactly as you suggested - put the headings on the form and give him some pre-formatted, condensed report options to print.
Thanks again for your time and support.
Rob
Thanks very much for your comments. You've confirmed what I thought but Experts always amaze me with what you're able to accomplish so I thought it wise to ask. I also hoped that MS may have added some functionality to freeze panes and columns of a report when they introduced the expanded features and I just didn't realise it.
This application is actually best suited to a pivot table but the user is used to an access program I wrote for him 10+ years ago that generated half a dozen reports based on data extracted from his accounting system via an ODBC Link. A recent upgrade of that software meant the link would no longer work so he asked me to find another way of providing the data. This new functionality in MS Access made it very easy to give him a report that provides the perfect overview - everything he wants to see on one, simple report. And he now has the ability to double-click on a column to open a second report that gives a list of the detailed data that went into the summary overview. So it was too easy and too tempting not to use the report as a quasi-form.
I will do exactly as you suggested - put the headings on the form and give him some pre-formatted, condensed report options to print.
Thanks again for your time and support.
Rob
but Experts always amaze me with what you're able to accomplishThe old saying is that; anything is possible, ...it all depends on how much time and energy you are willing to spend.
;-)
FWIW here is my link of hiding/showing report fields based on check box selections
;-)
Jeff
https://filedb.experts-exchange.com/incoming/2012/07_w28/588894/Access--Sample--SelectHideShowDi.mdb
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
This is just the nature of how reports work.
A report is a on-screen representation of your printed report.
On a printed hardcopy of a report, the report headers don't "follow your eyes" down the page (and are always visible)
(You have to "look" back to the top of the page to see the headers)
Even with a standard main/Report with a sub/report, ...about all you can do is set the headers to repeat on each page.
Even if there was a "hack" to get this to work, ....it is impossible to predict how you would control this once you add your "Select a column" functionality in...?
In a very broad sense, you may be trying to do a bit too much with this report.
...Always display Headers, select Columns, Drill-down, Filtering, ...etc.
I think MS gave in to a lot of pressure and gave user some level of "interaction" with reports.
This was good and bad IMHO.
It was good in the sense that you could now Filter, double click objects, ...etc.
It was bad in the sense that now some users will "dream up" functionality, ...where the distinction between Reports and Forms is blurred even more.
The end result is that reports are now being asked to do things they were not designed to do.
I looked at your question history, ...and I see you have a pretty solid background of asking well formed questions.
I also see you have a very broad knowledge set.
That being said, ...
If it were me, ...I would keep things simple and put the headers on the form (where the can remain visible)
...and worry about things like this later:
"I plan on inserting some check boxes on the parent form to allow the user to select which columns to print"
Dynamically selecting columns to print is something that seems like a great option, ...but is tricky to implement.
I did it once, ...but in the end it was far easier to create one "wide" report, ...and one "thin" report.
Hope this helps some, ...lets see what others may suggest (in case I misunderstood something here)
;-)
JeffCoachman