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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
As you have determined, ...there is no easy or direct way to do what you are asking.
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
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.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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial