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....

Answer 1

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 2

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 3

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