Set and retain column widths of Split Form datasheet columns in MS-Access

Question

I'm using a Split Form in Access 2012. Basic datasheet view on top with individual record and form header in bottom pane.

I'm trying to figure if there's a way to keep my column widths to manually sized widths - and ideally, set column widths to BestFit via VBA code when the Form loads.

I have found this one hack that remembers the column widths the next time you start.

I had the same problem. What worked for me (Access 2010 Split Form) was to resize all the columns (by dragging the right edge of the column header) the way I wanted, then right click any column header and select "Hide Column." Hide any column, click "Ok" then right click again on any column, choose "Unhide Column" and unhide the one you hid in the first place. Click "Ok" and presto the form opens with the resized column widths every time.

But the problem happens again if I distribute the database.

If I could somehow reference the datasheet view columns object, I could probably apply the BestFit method

EDIT - UPDATE: I have tried using this function to no effect:

For Each ctl In Me.Controls
   With ctl 
      Select Case .ControlType
         Case acTextBox
            Debug.Print .Name
            If Not .ColumnHidden Then
               .ColumnWidth = -2
            End If
      End Select
  End With
Next

Answer 1

Unfortunately, "Me.[Field1].ColumnWidth = -2" does not work for split view.

This bizarre series of steps appears to be the only way to save column widths in an MS Access (2007-2013) "split form":

1. In split view, set your columns widths on the datasheet side of the form. Double-click on the right edge of any column if you want to set it to the current Best Fit (unfortunately even "Best Fit" will be saved as a static width).
2. Highlight the columns with widths you want to save. If you are doing all of them, do not use Select-All; select the first column header, then shift-click on the last column header.
3. Right-click on the center of any highlighted column header, then click Hide Fields. All of your columns should disappear. Don't worry, this is supposed to happen.
4. Under the Home menu tab, click on More > Unhide Fields, and select all of the columns.
5. Hit Save, then go into Design view, and hit Save again. Your column widths will be saved.

Answer 2

Haven't you tried this method in your form? Where -2 is best fit. Note: without the [] brackets it wont work.

Private Sub Form_Load()
    Me.[Field1].ColumnWidth = -2
    Me.[Field2].ColumnWidth = -2
End Sub

Answer 3

You can try calling this function which seems to work o a split form

Public Function AutofitDatasheetColumns()
    Dim ctl As Control
    For Each ctl In Screen.ActiveDatasheet.Controls
        On Error Resume Next
        ctl.ColumnWidth = -2
    Next ctl
End Function