Language: Deutsch English

Last Update: 2021 - 01 - 04

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.

Access Options dialog showing the Default Column Width setting

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.

Share this article: Share on Facebook Tweet Share on LinkedIn Share on XING

Subscribe to my newsletter


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.

© 1999 - 2021 by Philipp Stiefel - Privacy Policiy