Can't save column position changes if .AllowEdits/.AllowDeletions/.AllowAdditions is set

Question

I have a table Tbl:

+----+---+---+
| ID | x | y |
+----+---+---+
|  1 | 3 | 2 |
+----+---+---+

and a form TblForm based on Tbl, in Datasheet view.

With TblForm open, I want to move the column ID to the right of column x, which can be easily done by selecting ID and dragging it to the right. But when I include the following in my OnLoad event:

Private Sub Form_Load()   
   Me.AllowDeletions = True
End Sub

then if I move ID to the right of x, then close and re-open TblForm, ID is back in its original position on the far left. It seems that setting AllowDeletions disables saving of column position changes.

This is true regardless of whether I set AllowDeletions to True or False. It also occurs with AllowEdits and AllowAdditions.

This is a problem, because I want to set these properties based on the user's permissions, but I don't want to prevent the user from moving columns around.

So you are trying to permanently change a userform table layout? — enderland
@enderland: Yes. The changes are normally permanent when these properties aren't set programmatically. Regardless of whether or not they're set, changing the column order does not result in a "Do you want to save changes..." warning. — sigil

HK1 HK1 · Accepted Answer · 2013-07-27T15:47:41

Using Access 2007 and 2010, I have found that Access does not reliably save user's preferences for column order in Datasheet view. I have never been able to determine which settings, features, or code prevents the column order from being saved. I did test this just today and I was able to duplicate the behavior you described, although there were times my column order was saved and reloaded (without my solution below) while other times it did not save/load. I still don't know what caused the difference.

Here's a lengthy but potentially helpful thread at UtterAccess on this problem. One user by the screen name of "mandrews" says he thinks it's a bug in Access 2010 and offers this solution: If you open the list, move the columns into the correct order, right click a column heading and click unhide fields, then click close and save the form it should keep the order. I tried this recently on an Access 2013 application and it didn't seem to work. But I also tried it today on an Access 2010 test application and it did appear to work. The main thing that's different is that after hiding or unhiding columns you will be prompted to save the form. I think this solution is not a very good one because I'm guessing it wouldn't work when using Access Runtime, which is how I usually deploy my solutions.

My own solution is to save their settings when the form unloads. First, I have functions and tables designed to store user specific settings in the main database for each user. When the form unloads I iterate through all the controls and save their name and their ColumnOrder property. Then when the form loads I retrieve those settings from the database, parse them out, and apply them to the datasheet form (usually a subform). I also save the OrderBy as well as ColumnHidden, ColumnWidth, BackColor, AlternateBackColor, ForeColor, FontName, FontHeight, FontWeight, and RowHeight. I know it seems unnecessary since users can actually save these modifications, but I wanted these user preference to persist when I released my frequent database updates.

This all works fine with two exceptions. The first one is that the Sort Order (OrderBy) seems to have some effect on their column order. In some cases it results in my code having no effect. The second exception is that an error occurs any time you remove a control from the form. In this case the error actually occurs when I apply the Sort Order (OrderBy). If I remember correctly it's actually not an error, it's a message about needing to enter a parameter value.

Here's the code to get the ColumnOrder:

Dim frm as Form
Set frm = Me.Form
Dim ctl As control
Dim ictl As Integer
Dim sValues As String

For ictl = 0 To frm.Controls.Count - 1
        Set ctl = frm.Controls(ictl)
        Select Case ctl.ControlType
            Case Access.acTextBox, Access.acComboBox, Access.acCheckBox
                sValues = sValues & ctl.Name & "=" & ctl.ColumnOrder & ":"
        End Select
Next ictl
Debug.Print Left(sValues, Len(sValues)-1)
'Save sValues to your Database somewhere for retrieval later

Here's how you apply the ColumnOrder:

Dim frm as Form
Set frm = Me.Form
Dim sSettings As String
Dim aryRead1() As String
Dim aryRead2() As String
Dim iCount As Integer

'You need to retrieve you're user settings somehow
'sSettings = GetSettingUser(frm.Name & "ColumnOrder")
If sSettings <> "" Then
    sSettings = sSettings & " "
    aryRead1 = Split(sSettings, ":")
    Do Until iCount = UBound(aryRead1)
        If Trim(aryRead1(iCount)) <> "" Then
            aryRead2 = Split(aryRead1(iCount), "=")
            frm.Controls(aryRead2(0)).ColumnOrder = aryRead2(1)
        End If
        iCount = iCount + 1
    Loop
End If

Can't save column position changes if .AllowEdits/.AllowDeletions/.AllowAdditions is set

2 Answers