Changing the column order and width in datasheet view of form (1 Viewer)


Registered User.
Local time
Today, 00:27
Mar 2, 2010
I came across this thread Changing the column order in the datasheet portion of a split form (Access 2010), but it didn't seem to address how to change the column order programmatically. And while this thread showed how to get the column number, it didn't show how to change the order. I took a chance and added an equal sign after it and it worked. Here is how:

After someone has dragged a field or fields around on the datasheet, this code is set up to restore the col order to it's original layout by clicking on a button. I imagine that it could be taken a step further and put into a table with multiple col orders based on user preferences and then via recordset could reorder the cols.

It's essentially using fieldname.ColumnOrder = x

Private Sub btnResetCols_Click()
'Put subform cols in order that user wanted as default

    Forms!frmHVR!frmHVRFilter.Form.txtULTIMATE.ColumnOrder = 3
    Forms!frmHVR!frmHVRFilter.Form.txtImmediate.ColumnOrder = 4
    Forms!frmHVR!frmHVRFilter.Form.txtDBA.ColumnOrder = 5
    Forms!frmHVR!frmHVRFilter.Form.txtSales.ColumnOrder = 6
End Sub
And here is some code on load that will adjust the col widths of the datasheet. Did some calculations to estimate number of characters for a given font and point size.

Private Sub Form_Load()
    'Resize col widths in datasheet view
    Const TWIPSTOINCHES = 1440
    'TWIPS times (number of inches for 1 character at 8 point Calibri)
    Me.txtFamilyTree.ColumnWidth = TWIPSTOCHARWIDTH * 5 'desired character width of 5 characters
    Me.txtULTIMATE.ColumnWidth = TWIPSTOCHARWIDTH * 25
    Me.txtImmediatet.ColumnWidth = TWIPSTOCHARWIDTH * 25
    Me.txtWebSite.ColumnWidth = TWIPSTOCHARWIDTH * 20
    Me.txtDBA.ColumnWidth = TWIPSTOCHARWIDTH * 20
    Me.txtSales.ColumnWidth = TWIPSTOCHARWIDTH * 8
End Sub

Users who are viewing this thread

Top Bottom