Save position and lengths of columns in a datasheet view per user

Question

All

I have an application which is used by a dozen of users. The main form has a subform. That subform is a datasheet. Depending the user his needs he should be able to set the order/width of the columns like he wants.

Therefore I have created a linked table to our SQL-server with four fields:

User
Volgorde (Volgorde can be translated as "order")
Formulier (Name of the form)
DatumLaatstGewijzigd (Data last changed)

A short example of field Volgorde will contains something like
[ID]$$$$$4590€€€€€[REF]$$$$$7500€€€€€[SubID]$$$$$8200

This means: in the subform the datasheet must be shown as

The first field must be "ID" and its width is 4590.
The second field must be "REF" and its width is 7500
The third field is "SubID" and its width is 8500

To save the information I created a button with code below.

 Public Sub cmdBewaarVolgordeKolommen_Click()
        Dim strString As String
        Dim intTotalControls As Integer
        Dim intX As Integer
        Dim strSQL As String
        Dim db As Database
        intTotalControls = Me.Alles_subform.Controls.Count - 1
        strString = ""
        For intX = 0 To intTotalControls
            strString = strString & "[" & Me.Alles_subform.Controls(intX).Name & "]$$$$$" & Me.Alles_subform.Controls(intX).Width
            If intX < intTotalControls Then
                strString = strString & "€€€€€"
            End If
        Next

        Set db = CurrentDb
        strSQL = "update tblColumnOrderAlles_Form set Volgorde='" & SQLFixup(strString) & "',DatumLaatstGewijzigd='" & Now & "' where user='" & Environ("username") & "' and Formulier='PlanningProd'"
        db.Execute strSQL

    End Sub

When the user opens the application, the order/width of the datasheet view is retrieved from the database. The HasProperty just checks if the control has the property in the given parameter.

    Private Sub Form_Open(Cancel As Integer)
    strSQL = "select [volgorde] from tblColumnOrderAlles_Form where [user]='" & Environ("username") & "' and formulier='PlanningProd'"
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    If Not rs.BOF And Not rs.EOF Then
        strSplit = Split(rs!volgorde, "€€€€€")
        For intX = 0 To UBound(strSplit)
            strSplit2 = Split(strSplit(x), "$$$$$")
            strControl = strSplit2(0)
            strWidth = strSplit2(1)
            If HasProperty(Me(strControl), Me(strControl).Name) = True Then
                Me(strControl).ColumnOrder = intX
            End If
            If HasProperty(Me(strControl), Me(strControl).Width) = True Then
                Me(strControl).Width = intX
            End If
        Next
Else
    Set db = CurrentDb
    strSQL = "insert into tblColumnOrderAlles_Form(user,DatumLaatstGewijzigd)values('" & Environ("username") & "','PlanningProd','" & Now & "')"
    db.Execute strSQL
End If
    end sub

For some reason, the correct order/width as specified by the user is not set. So there must be something wrong but I have no idea what's going wrong....

Accepted Answer

Using property ColumnWidth worked for me:

' Do unhide a column before setting the column order.
ThisControl.ColumnHidden = False
ThisControl.ColumnWidth = -1
ThisControl.ColumnOrder = rst!ColumnOrder.Value
' Set width and visibility of the column.
ThisControl.ColumnWidth = rst!ColumnWidth.Value
ThisControl.ColumnHidden = rst!ColumnHidden.Value

Answer

Try replacing Me(strControl).Width = intX with Me(strControl).Width = CDbl(strWidth).

To keep the column order, probably you must extend the code to include the ColumnOrder value into your special string.

Answer

I found a solution. First of all I made a mistake by using variable x instead if intX.
And I saved/set the property Width instead of ColumnWidth

Save position and lengths of columns in a datasheet view per user

2 additional answers