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

[sxschech]

I came across this thread Changing the column order in the datasheet portion of a split form (Access 2010) http://www.access-programmers.co.uk/forums/showthread.php?t=231625, but it didn't seem to address how to change the column order programmatically. And while this thread http://www.access-programmers.co.uk/forums/showthread.php?t=195357 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)
    Const TWIPSTOCHARWIDTH = TWIPSTOINCHES * 0.06
    
    Me.txtFamilyTree.ColumnWidth = TWIPSTOCHARWIDTH * 5 'desired character width of 5 characters
    Me.txtULTIMATE.ColumnWidth = TWIPSTOCHARWIDTH * 25
    Me.txtImmediatet.ColumnWidth = TWIPSTOCHARWIDTH * 25
    Me.txtCOMPANYNAME.ColumnWidth = TWIPSTOCHARWIDTH * 25
    Me.txtWebSite.ColumnWidth = TWIPSTOCHARWIDTH * 20
    Me.txtDBA.ColumnWidth = TWIPSTOCHARWIDTH * 20
    Me.txtSales.ColumnWidth = TWIPSTOCHARWIDTH * 8
...
...
End Sub