Last Update: 2021 - 03 - 21
Resize a DataSheet Form to Fit all Columns
by Philipp Stiefel, originally published 2021-01-11
last revision on 2020-12-28
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.
In theory, yes, but in practice not so much.
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.
(The documentation of the ColumnWidth property currently incorrectly states that a value of (+)1 indicates the default width. – I submitted a correction for that.)
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.
Here is the function I wrote to get the Default Column Width in Twips.
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
The final step is to adjust the width of the form.
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.
© 1999 - 2021 by Philipp Stiefel - Privacy Policiy