Freeze Columns

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.

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
    Next
   
End Sub

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?

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.

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

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.

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?

Great help Thank you

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.