In Access 2007 datasheet view, how do I use VBA to programmatically access the column width and Best Fit function?

0
votes

I use Access 2007 for a lot of data conversion & normalization. I do a lot of data imports and create a lot of tables and queries on the fly. Many of the resultant tables and queries have dozens of columns, and adjusting the column widths and activating the "Best Fit" function (aka autofit or autosize) is very time consuming.

I'd like to whip up some VBA to loop through the columns to set them as I see fit. I've searched extensively on Google, but I cannot find any information on the collections, properties, and/or methods I need to use to do this.

How can I use Access VBA or a macro to programmatically access the following elements of the datasheet view of tables and queries..?

  1. read the column width property
  2. set the column width property
  3. activate the "Best Fit" function

EDIT: Any takers on this..? It's been a couple of weeks, and no responses. Must be a way. Of course it might be done through Windows API calls to the Access GUI controls, but I'd like to avoid that complexity if possible...

2
ms-accessvba

2 Answers

1
votes

That is the ColumnWidth property and the SizeToFit method:

  1. ColumnWidth = Me!YourControl.ColumnWidth
  2. Me!YourControl.ColumnWidth = ColumnWidth ' Integer value.
  3. Me!YourControl.SizeToFit

For tables and queries:

  1. CurrentDb.TableDefs("Table1").Fields(n).Properties("ColumnWidth").Value = ColumnWidth

  2. Not possible, as far as I know

0
votes

This worked for me to resize all the columns of a subform displayed as a datasheet

   Private Sub Form_Load()
        For Each oControl In Me.Detail.Controls
         If oControl.ControlType = 109 Then
           oControl.ColumnWidth = -2
         End If
       Next oControl
    End Sub