Microsoft Access datasheet forms are very useful, but they suffer from an aesthetic drawback. That is, columns can open up bunched to the left, giving your application an unfinished look, and leaving the form unwieldy for the user.

There is a simple fix. The code snippet below can be called in the open event of any datasheet, and the 'FitDatasheetColumns' function will proportionally widen each visible column until the width of the parent container is reached.

If there are any columns that you want to hide, set .ColumnHidden=True for these columns before calling the function FitDatasheetColumns

          Private Sub Form_Open(Cancel As Integer)
              Me.CreateDate.ColumnHidden = True
              FitDatasheetColumns Me
          End Sub

Copy the code below and place it in any module.

Sub FitDatasheetColumns(parForm As Form, Optional parAllowScrollbar As Boolean = True)
' Usage:    Calling format is: FitDatasheetColumns Me
'           Usually called in the OnOpen event of a datasheet which appears as a subform in a parent page.
'           Optionally omit the space allowed for a vertical scrollbar (see Notes below)
' Function: This routine works out how wide your datasheet is going to be, and if wider than
'           the container control, makes it fit eactly without a horizontal scrollbar.
' Method:   Create a string of visible column names
'           put them into an array (arrCols)
'           calculate the total width of these columns
'           Iterate thru the columns, and multiply each width by [Width of the Container Control]/[Total Width]
' Result:   Each column is reduced in width proportionately, sufficient to fit it into the container control
'               (minus the width of a vertical scrollbar, estimated at 550 twips)
' Note:     A width of -1 indicates the default Spreadsheet columnn width, which is commonly 900. If the default
'           has been changed, this routine doesn't detect it.
'           It is too complex for this routine to detect if a vertical scrollbar will display, so it always assumes
'           that sufficent space must be left for one. If there are not enough records to fill the page, the space
'           for the vertical scrollbar will remain grey. Optionally supply parAllowScrollbar = False to omit the space.

Dim f As Form, ctl As Control
Dim nWidth As Integer, n As Integer, nInWidth As Integer, nPosWidth As Integer
Dim strCols As String, arrCols
Const boolRep As Boolean = False

    If boolRep Then Debug.Print "Form: " & parForm.Name
    
    For Each ctl In parForm.Controls                 ' Create string of visible column names
        On Error Resume Next
        strCols = strCols & IIf(ctl.ColumnWidth <> 0 And Not ctl.ColumnHidden, ctl.Name & ";", "")
    Next
    
    If boolRep Then
        Debug.Print "Visible Columns: " & strCols
    End If

On Error GoTo Proc_Err
    
    If Len(strCols) > 1 Then
        strCols = Left(strCols, Len(strCols) - 1)
        arrCols = Split(strCols, ";")
        For n = 0 To UBound(arrCols)            ' Width of visible columns
            nWidth = nWidth + IIf(parForm(arrCols(n)).ColumnWidth = -1, 900, parForm(arrCols(n)).ColumnWidth)
        Next
        
        If boolRep Then
            Debug.Print "Total Width: " & nWidth & " Inside width: " & parForm.InsideWidth
        End If
        
        nInWidth = parForm.InsideWidth - IIf(parAllowScrollbar, 550, 0)       ' Allow for scrollbar width
        If nWidth <> nInWidth Then
            For n = 0 To UBound(arrCols)
                If boolRep Then
                    Debug.Print "Original: " & parForm(arrCols(n)).ColumnWidth & " Adjusted: " & parForm(arrCols(n)).ColumnWidth * (nInWidth / nWidth)
                End If
                parForm(arrCols(n)).ColumnWidth = IIf(parForm(arrCols(n)).ColumnWidth = -1, 900, parForm(arrCols(n)).ColumnWidth) * (nInWidth / nWidth)
            Next
        End If
    End If
    
Proc_Exit:
    Exit Sub
Proc_Err:
    MsgBox Err.Description & vbCrLf & vbCrLf & "Cannot set columnwidths.", vbInformation, "Process Error"
    Resume Proc_Exit
    Resume
End Sub

And in case you were wondering, it is Windows job to determine whether or not a scrollbar is displayed, and that is not done until after Access is finished displaying the datasheet. So while the 'FitDatasheetColumns' function could conceivably calculate row counts and heights and forecast whether a scrollbar is likely to be displayed, it doesn't do so: instead, it leaves room for a scrollbar by default.

If the form is unlikely to display more records than will fit vertically on the form, you can set 'parAllowScrollbar' to False, and this will remove any whitespace that would otherwise have allowed for the width of a scrollbar.