Freeze Columns

Hi All

I have a subform displayed in datasheet view. I want to lock the column headings so that users cannot re order the columns. Can't see how it's done in Access 2002
Derek BrownMDAsked:
Who is Participating?
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.

Try this in datasheet view.

Select all of your columns, right-click and select "Freeze Columns".  That setting will persist when you close the form, and you can deploy your database like that.
Dale FyeCommented:
If you want to do it via code, you could use:

Private Sub Form_Load()

    Dim intLoop As Integer
    For intLoop = 1 To Me.Controls.Count / 2
        With Me
            DoCmd.RunCommand acCmdFreezeColumn
        End With
End Sub
Derek BrownMDAuthor Commented:
Hi All

Tried both ways both work. However the columns run off the page so when they are Frozen they stay put and I cannot see the off page columns. So to be more precise what I actualy need is a devise for maintaining the column order. Can you help with this? I do not know if this is possible?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
Can I ask, why is it important to maintain the column order?  One of the only reasons I even consider using a datasheet view is to allow my clients to adjust the way they view the data.  Some users of a page may be concerned with columns A, B, and C.  Others may want them ordered by A, C, D.

Derek BrownMDAuthor Commented:
Hi Fyed

The subform allows data to be pasted from excel which it can only do if the text and number fields are in correct order. The company regularily paste 70 columns of data so the order is critical
Dale FyeCommented:
Here is what I would do.

1.  Set the AllowAdditions property of the datasheet to False

2.  Add a button "Allow Additions" to your main form.

In the code behind that button, add some code that restores the column order to the proper order.  To use the columnorder property, you need to understand that all you can do is move columns so that they become the first column.  To get them in the proper order, you need to start with the column furthest to the right and do something like the following:

me.[Column5].columnorder = 0
me.[column4].columnorder = 0
me.[column3].columnorder = 0
me.[column2].columnorder = 0
me.[column1].columnorder = 0
me.AllowAdditions = true

Then, in the Form_AfterInsert event, set AllowAdditions = False and enable the "Allow Additions" button again.
Derek BrownMDAuthor Commented:
Hi fyed

Sorry to be a Dork but do I actually use [Coumn5] or the name of the column's field? or the underlying form's object name. Perhaps I should have explained that I originally wanted a regular continuous form but as i could not get excel data to paste I altered the form view to datasheet. but the form still exists as a form underneath. Am I making any sense?
Dale FyeCommented:
Yes, you are making sense.

You should use the name of the controls.  Most people don't bother to change the name of the controls on a datasheet (although I generally do), so I didn't think to mention that.

me.txt_Column5.columnOrder = 0

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
Derek BrownMDAuthor Commented:
Great help Thank you
Dale FyeCommented:
I found with one of my clients that they were a little disappointed that every time I fielded a new version of their software, the column shifting that they had done previously was overwritten by the sort order in the datasheet I fielded.

They asked me if I could make it so that their individual column sorting could be retained, so I had to write a couple of routines that read from/write to a text file every time the form is opened/closed, and sets the proper colum sequence for each user.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.