Vertical Freeze Pane in Datasheet

[keirnus]

Hello,

There's a freeze pane function in datasheet but for horizontal scroll only.
How about for the vertical scroll?

I need to implement this to be able to view the total data of each column by scrolling up and down but maintaining the view of the total data.

Calling all the Access gurus for help.

-= kerinus =-

[Stewart Ross]

Hi kerinus. It is not possible to 'freeze' one row and still scroll others.

If what you are after is a set of totals remaining on screen whilst you scroll the data you can implement this functionality by using the header or footer area of a continuous form to contain unbound textboxes, one for each total you need. The control source for each unbound textbox would just be

=sum([field you are summing])

This is the same technique used to produce group totals and overall totals in a report.

An example of the use of the footer for this purpose is attached. The totals at the bottom are unbound controls which remain in view as the detail rows are scrolled.

Note that this cannot be done in datasheet view, as the form header/footer areas are not visible in that view.

-Stewart

[keirnus]

@Stewart Ross Inverness
Thanks Stewart for your reply.

How about in Listview control?
Is it possible?

[Stewart Ross]

Not in listview control either... -S

[Stewart Ross]

I suppose you could implement something where you select a row from a listbox, then apply a filter to a subform based on that row - but it is unlikely to look "clean" from a user perspective. You don't tell us what you envisage this being for - in post 1 you mention totals twice, but without explanation it is hard to visualise what you are trying to achieve.

-Stewart

[keirnus]

@Stewart Ross Inverness
oh yeah...that was two "total" words...sorry about that...they are just one and the same...those total data are total per column...so each column has its own total amount.

like what you have stated, remaining a set of totals while scrolling (up and down) the rest of the data.

what if due to lots of data (vertical and horizontal), there would be vertical and horizontal scroll bars...it would be great to maintain the total per column when scrolling either vertically or horizontally...the same "freeze pane" function in MS Excel.

can you suggest of a control in MS Access suitable for that implementation?

[DonRayner]

You could use a continuous form as a pseudo datasheet. If you put the Column names in the header, data in the detail section and sums in the footer it should give you what you are after. But you will loose the ability to freeze columns then.

[Stewart Ross]

Although Excel does have the ability to freeze panes (in multiple windows), I can think of few occasions when I find it either useful or easy on the eye.

As Don says, you can investigate how to use a continuous form to do something like what you are suggesting - but as you have lots of data (as you have said) I think the visual result will be a mess. If you look at examples of good, clean design you will see the embodiment of the maxim that less is more.

Just because in this case you may be able to implement something that mimics the freeze pane functionality it does not follow that it is a good idea to do so, unless your users would find it easy and intuitive.

There is a good reason that no there is no existing control available which does what you suggest...

-Stewart

[NeoPa]

There is no equivalent of the Freeze Pane facility in Access. Even if there were, it seems to me that Freeze Panes allows you to keep the titles in view, rather than any possible totals.

Be that as it may, Stewart's solution is a perfectly viable solution as far as I can see. You can use Continuous Forms view instead of Datasheet to see multiple records (I couldn't see a problem with Datasheet view in my testing, but I would never ignore anything Stewart has stated without much more thorough testing).

[NeoPa]

Having seen some of the other (quicker) replies, it occurs to me to ask whether you're talking about Freeze panes, or maybe whether Split Windows might be what you're referring to.

I never use Split Windows as I find that quite untidy, but Freeze Panes I use all the time to keep the column & row titles visible in large sheets.

[keirnus]

@Stewart Ross Inverness
Thanks Stewart for the advice.
I like the less is more perspective.

Also, thanks to DonRayner and NeoPa for the advices.
Continuous Form is a good start.

[keirnus]

@NeoPa
I am referring to Freeze Panes... =D

[Stewart Ross]

Apologies, NeoPa, it was Split I was thinking of - and very untidy it is too. Excel's freeze panes facility is indeed very useful, although I tend to use it only at the top of a list and not part way through (as it is easy to 'forget' that there are items above the frozen row).

The datasheet issue relates to the lack of headers and footers in a datasheet - as well as the loss of any custom controls (see the attached datasheet view of the same form shown in post # 2).

-Stewart