"Freeze Panes" in Form view (1 Viewer)

JamesMcS

Keyboard-Chair Interface
Local time
Today, 18:15
Joined
Sep 7, 2009
Messages
1,819
Morning campers! Hope the sun is shining where you are...

So - I've done a bit of googling and it seems this isn't possible, but there are some clever types on here so I thought I'd see what we could come up with.

I'm trying to get a subform to show a list of products down the left side, and 24 months of sales figures. Obviously this is quite a wide subform so it would be great if, when the user scrolls off to the right, the product list remains at the left.

Is this possible? Someone opposite me just suggested having a subform within a subform, and something about scroll locking - anyone know about this?
 
A datagrid control would do the job nicely. The caveat being:
  • They are ActiveX controls
  • They generally require quite a bit of coding
  • I think MS seems to have got its knickers in a knot about supporting them in Access (2007 onwards?). I believe there is a resolution to this though.

I use a third party grid control from ComponentOne which I really like. Huge class methods.

As to the other idea, I guess you could create two subforms side by side and somehow ensure they are synchronised. It would be an interesting exercise. I think I have some code that might help. I'll have to dig around though.

Chris
 
Nice one stopher - don't bust a gut though, it's one of those nice-to-have-but-not-necessary-really things, plenty of other stuff to get on with before release day :)
 
I just checked becasue I have an app that does this

the left pane has a number of vehicles vehicles

the right pane has a calendar for those vehicles - so you can move left to right on the calendar, without affecting the vehicle list.

However, on checking there are only a limited number of vehicles, so I didn't have to manage synching the two forms for vertical movement, as all rows are visible at the same time.
 
Ah thanks anyway Dave - I expect there's something I can do with absolute positions to synchronise the two subforms, still... bit clunky though, I'm sure it's in the pipeline for Access 2055 or something :)
 
maybe what you need is a separate up/down button in the container form - rather than say, scroll bars in the sub forms.

then you can hard code the buttons to coordinate the movement. Still likely to look and work a bit unnaturally.
 
In terms of keeping two forms in sync, there are several elements as i see it.

Simple navigation using the up/down keys is easy to sync. In the On Current event for the moving form just put:

Code:
Forms.myMainFrom.mySyncSubForm.Form.SelTop = Me.SelTop

This actually works quite nicely and naturally and with a bit of tailoring can look good.

However, moving the scroll bar or the mouse wheel doesn't fire On Current. I think the scroll bar is easy overcome by implementing a scroll bar control and have it set both forms or doing what Dave said. Although I see Lebans has some code to get the scroll bar position which could be used (api calls).

The mouse wheel could be disabled (?) or trapped (?).

As an aside, tables and queries have a property FrozenColumns (at least in 2007 on). It's possible to set this with code. However, I can only seem to get it to work in datasheet view for the query or table. I can't seem to set this property for the datasheet view in a form. Bizarre.

Chris
 
In terms of keeping two forms in sync, there are several elements as i see it.

Simple navigation using the up/down keys is easy to sync. In the On Current event for the moving form just put:

Code:
Forms.myMainFrom.mySyncSubForm.Form.SelTop = Me.SelTop

This actually works quite nicely and naturally and with a bit of tailoring can look good.

However, moving the scroll bar or the mouse wheel doesn't fire On Current. I think the scroll bar is easy overcome by implementing a scroll bar control and have it set both forms or doing what Dave said. Although I see Lebans has some code to get the scroll bar position which could be used (api calls).

The mouse wheel could be disabled (?) or trapped (?).

As an aside, tables and queries have a property FrozenColumns (at least in 2007 on). It's possible to set this with code. However, I can only seem to get it to work in datasheet view for the query or table. I can't seem to set this property for the datasheet view in a form. Bizarre.

Chris

seltop, eh?

easier when you know its there, and know what it does.

nice one.
 
I would never have guessed the meaning of SelTop either :rolleyes:

Anyway, here's a sample using two subforms incorporating the SelTop code.

So in the right hand form you can scroll right while the left form remains fixed. And if you scroll up/down in the right hand pane, both forms remain synced.

I've also added Stephen Leban's code so that scrolling using the mouse wheel also synchronises. Note that

PgUp/PgDn needs a bit a little more work as they seem to go out of sync for some reason.

Also I haven't done the scroll bar (vertical). I have no idea how to trap the scroll bar. But an overlayed custom scroll bar should work a treat.

Unfortunately I can't get this saved in 2003 yet. Not really sure why.

Also I had to change the constant string "Scrollbar" in Leban's code to "NUIScrollbar". I think this is to do with Windows 7 api's so try "Scrollbar" if it doesn't work.

Chris
 

Attachments

The attachment works in both Access 97 and Access 2003 (can’t test it in the others.)

It uses the RecordsetClone Bookmark to synchronize based on a scroll bar on the right.

Chris.
 

Attachments

I managed to get it into Access 2003 (attached).

I've fixed the PgUp/PgDn problem.

And I'm sure ChrisO's solution will deal with the scrollbar part.

Chris
 

Attachments

Hey guys..
I took your examples, FreezePanes2003 and was able to modify it to use tables and fields from my database.
Everything looks and works great, when the subforms are in datasheet mode.
I was wondering you had any sucess getting the mousewheel scroll to work when the subforms are changed to continuous forms. I need to do some additional formatting of the data and the datasheet form will not give me what I want, settingup the fields in a continous will, but now the mousewheel scrolling is not working correctly. seems on the first mousescroll the other subform does not sync, after that the scrolling in in sync for the most part... clicking on a record in the right hand subform will reset the left hand to the proper position as well, but would like to try and get the mousescroll to sync...

I hadto set scroll bars on the subforms when chaning them to continous as well..
 

Attachments

I've just discovered this thread from 2011 and have updated it to accdb (attached)

I'm posting to give it a 'bump' as I think others may find the idea useful and want to develop it further

Belated thanks to ChrisO and stopher for doing this
 

Attachments

Hello people
I searched the internet and found the example provided here.
So I'm attaching this same example a bit more
improved.
Hope you like it!

Sorry My English!

Bye
 

Attachments

Last edited:
Hello people
I searched the internet and found the example provided here.
So I'm attaching this same example a bit more
improved.
Hope you like it!

Sorry My English!

Bye

Welcome to the forum Balem.
Where are you from?

Thanks for uploading this.
How exactly is it an improvement?
Its certainly very large - far too wide for a standard monitor!!!
 
Last edited:
...I'm trying to get a subform to show a list of products down the left side, and 24 months of sales figures. Obviously this is quite a wide subform so it would be great if, when the user scrolls off to the right, the product list remains at the left...

So much good work has been done, by so many people, I almost hate to post this, but with a Datasheet View Form...this:
Code:
[B]Private Sub Form_Load()
 DoCmd.RunCommand acCmdFreezeColumn
End Sub[/B]
will freeze the left-most Column...the product list in the OP's case!

To freeze multiple Columns is a little bit more complicated...you need to
  1. Go to Query Design
  2. Select the Record Source for your Query
  3. Enter the Fields you want to freeze, in the order you want them to appear on the Form.
  4. In the next Field column...Create a Calculated Field...something like this: Field1 Field2: [Field1] & " " & [Field2]
  5. Enter the remaining Fields into the Query grid
  6. Now, untick the 'Show' checkbox for the individual Fields you want frozen, leaving the 'Show' checkbox for the calculated Field ticked
  7. Save the Query
Now, go to Form Design View and create your Form, using the Query as the Record Source, being sure to select the calculated Field first, then selecting the rest of the Fields to be shown.

Then, once again, use this, to freeze the left-most (calculated) Field:

Code:
Private Sub Form_Load()
 DoCmd.RunCommand acCmdFreezeColumn
End Sub
You're done.

Linq ;0)>
 
Last edited:
Hi Linq

You make a very good point here

AFAIA all the examples earlier were for continuous forms

However if a datasheet view provides the functionality needed then the freeze column solution is VERY easily achieved in VBA as you have described.

I rarely use datasheet view due to restrictions they impose compared to other forms ...but in this scenario they are definitely better

BTW I just hold down the SHIFT key to select multiple columns for freezing. Much easier than what you are suggesting ... :)

Thanks for your contribution
 
Well, I replaced the file in my previous post.
In the first column, pressing the keys, right, up and down, navigate the records and pressing the left arrow, go to the right columns, in the same record, this worksheet behaves like excel.

Once again, sorry, my English, I'm Brazilian.:)

Bye!
 
...I rarely use datasheet view due to restrictions they impose compared to other forms...

I only use Datasheet View when they are being used strictly for display of data... never if user manipulation is possible.

Linq ;0)>
 
Yes I know this thread is ancient but I recently found it again when writing a new web article


In the article, I demonstrate a completely different solution which works really well.

This is the second part of a two part article and shows how a specified number of columns can be frozen in a continuous form with no subforms.
In addition, the frozen columns can optionally be locked to prevent editing and all columns (locked or not) can be sorted and filtered.

It requires no APIs and will work in both 32-bit and 64-bit Access

The screenshot shows a continuous form with 3 frozen / locked columns and two filtered and one sorted column and which has been scroled both horizontally and vertically

3ColumnsFrozenLockSortFilter.png


DesignView_v22.png


The article includes the example app and all code together with a detailed explanation.

My thanks to Xevi Batlle who suggested this approach based on another article in my series on adding functionality to continuous forms.
Xevi also made a significant contribution to the code
 

Users who are viewing this thread

Back
Top Bottom