News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Thursday, March 21, 2013

Autosize columns in datasheet

I have always used Stephen Lebans AutoColumnWidth to resize datasheet columns, but from recent discussion in Access MVPs email list I found a native Access property to autosize columns. According to KB article ACC: How to Use the ColumnWidth Property it was there since Access 1.1! So you just need to loop through all controls and set ColumnWidth property to -2:

 Dim ctl As Control
For Each ctl In Me.CustomersDS.Form.Controls
   Select Case ctl.ControlType
      Case acTextBox, acComboBox
         ctl.ColumnWidth = –2
   End Select
Next ctl

Very easy! And set it back to default width you can use .ColumnWidth = -1.

You can also autosize table and query datasheet using same way and  Screen.ActiveDatasheet form object