Right-sizing Access Query Columns with VBA

I've been an Access developer since Access 2.0, and having to manually widen my query columns has always annoyed me. This article traces the development of a Sub that saves a lot of clicking and dragging.

My first solution was manually dragging the columns to their correct width, and then saving the query. Next I figured out that I could click the Select All button (in the upper left corner of the query's data area), and hovering the pointer cursor on the line between column headers until it became the double-headed arrow cursor. Double-clicking will the dividing line expanded (or reduced) the columns’ width so that the entire header and all  data on the visible lines was displayed. I quickly learned the importance of short column names!
 
As I became more fluent in VBA, I built a Sub that set the widths for each column in each query before the query is opened. When VBA code is used to set measurements on the Access database window, a ‘twip’ is the unit of measurement, and there are 1440 twips in an inch.
 

Dim Db As DAO.Database, Qry As DAO.QueryDef, Field As DAO.Field, Property As DAO.Property
                       
                      Set Db = CurrentDb
                      Set Qry = Db.QueryDefs("MyQueryName")
                       
                      Set Field = Qry.Fields!MyFirstFieldName
                         Set Property = Field.Properties!ColumnWidth
                         Property = 1440
                       
                      Set Field = Qry.Fields!MySecondFieldName
                         Set Property = Field.Properties!ColumnWidth
                         Property = 2880
                       
                      Set Field = Qry.Fields!MyThirdField
                         Set Property = Field.Properties!ColumnWidth
                         Property = 4320
                        
                      Set Property = Nothing
                      Set Field = Nothing
                      Set Db = Nothing
                      DoCmd.OpenQuery "MyQueryName"

Select all Open in new window

As you can imagine, this got very tedious very quickly. When I learned that I could set the width of columns in a datasheet subform by looping through the Controls collection, I immediately wondered if I could do the same for my queries. Access regards query results as a form, and because of this, we can programmatically modify its controls… including column width!
 
There are three values that have specific meaning to the ColumnWidth property:
0   Column is Hidden
-1  ColumnWidth = Default (1 inch, or 1440 twips)
-2  ColumnWidth = BestFit
 
Any other positive integer is treated as the number of twips for the column. I call this code snippet with every DoCommand.OpenQuery, and my columns automatically resize so that no data is hidden.
 

Public Sub RightsizeQueryColumns()
                      'By Paul Cook-Giles, 11/2/2015
                      'This code assumes that a query is opened and has focus, and sets column widths to display complete title/data
                      Dim intColumNumber As Integer, ctl As Control
                      10    Set frm = Screen.ActiveDatasheet
                      20    For intColumNumber = 0 To frm.Controls.Count - 1
                      30    Set ctl = frm.Controls(intColumNumber)
                      40       ctl.ColumnWidth = -2
                      50       Next intColumNumber
                      60    DoCmd.Save
                      End Sub

Select all Open in new window

While the user can name columns, Access assigns a number to each column, starting at the first column on the left with 0, and the number for each column to the right is incremented by 1. Line 10 tells Access that we want to regard the query open and with focus as a Form. Line 20 uses the number of columns to give Access the numbers of the columns we want to work with: Column(0) through Column(Number of columns - 1), and how many times to loop. Line 30 identifies the specific column number we want to affect, and Line 40 sets ColumnWidth for that column to -2 (best fit). Line 50 tells Access to use the next integer for column number, and return to Line 20 repeat the process.