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

K VHoof 41 Reputation points


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

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

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

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
799 questions
0 comments No comments
{count} votes

Accepted answer
  1. Gustav 711 Reputation points MVP

    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
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Viorel 95,156 Reputation points

    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.

    0 comments No comments

  2. K VHoof 41 Reputation points

    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

    0 comments No comments