Resize an Access DataSheet Form to Fit all Columns

I just had the need to resize a DataSheet form in Microsoft Access programmatically to exactly fit all its columns. At first this seemed like a nearly trivial task. However, it quickly grew into a small challenge that justifies a short text on the topic.

The basic approach seemed to be straightforward. You just need a reference to the form. Then you can easily loop though to the controls collection and add up the values of the ColumnWidth property of all the controls, add the RecordSelector width, and finally resize the form width to the total calculated from all the widths. – Simple, isn’t it.

The problem is the default column width. If a form was just opened, every column will have the default width. This is represented by the value -1 in the ColumnWidth property. Of course, this will totally disrupt our calculation of the total width of all columns.

Research revealed, the Default Column Width is a configurable setting defined in the Access options.

This option value can be retrieved using the GetOption method of the Access.Application object. Unfortunately, this value is stored in either centimeters or inches, depending on your regional settings. To use this value to resize an Access form, it must be converted to the unit Twips used by Access.

Function GetDefaultColumnWidthInTwips() As Long Const TWIPSPERCM = 567 Const TWIPSPERINCH = 1440 Dim twipsValue As Long Dim defaultSetting As String defaultSetting = Application.GetOption("Default Column Width") If InStr(defaultSetting, "cm") Then twipsValue = TWIPSPERCM * CDbl(Replace(defaultSetting, "cm", "")) ElseIf InStr(defaultSetting, "in") Then twipsValue = TWIPSPERINCH * CDbl(Replace(defaultSetting, "in", "")) Else Err.Raise vbObjectError + 1, "GetDefaultColumnWidthInTwips", "Unknown measure unit" End If GetDefaultColumnWidthInTwips = twipsValue End Function

Now writing code to sum up all the ColumnWidths of the form’s controls is possible. Here is my implementation:

Private Function SumOfColumnWidths(ByVal DataSheetForm As Form) Dim defaultColumnWidth As Long defaultColumnWidth = GetDefaultColumnWidthInTwips Dim sumOfWidths As Long Dim ctl As Control For Each ctl In DataSheetForm If ctl.ColumnWidth < 0 Then sumOfWidths = sumOfWidths + defaultColumnWidth Else sumOfWidths = sumOfWidths + ctl.ColumnWidth End If Next SumOfColumnWidths = sumOfWidths End Function

Public Sub AdjustFormWidthToColumnWidths(ByRef DataSheetForm As Form) Const RECORDSELECTOR_WIDTH As Long = 316 Dim formWidth As Long formWidth = SumOfColumnWidths(DataSheetForm) + RECORDSELECTOR_WIDTH DataSheetForm.Move DataSheetForm.WindowLeft, , formWidth End Sub

So, once you see the final implementation, it is actually not too complicated. That is very different from where I started, totally perplexed by the sum of column widths being a completely surreal value.

I will never share your email with anyone. You can unsubscribe any time.
This email list is hosted at Mailchimp in the United States. See our privacy policy for further details.

Benefits of the newsletter subscription

Resize a DataSheet Form to Fit all Columns

Subscribe to my newsletter