Tired of manually adjusting the width of query columns so you can see all the data? This quick solution will ensure your query columns are the right size every time.
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.PropertySet Db = CurrentDbSet Qry = Db.QueryDefs("MyQueryName")Set Field = Qry.Fields!MyFirstFieldName Set Property = Field.Properties!ColumnWidth Property = 1440Set Field = Qry.Fields!MySecondFieldName Set Property = Field.Properties!ColumnWidth Property = 2880Set Field = Qry.Fields!MyThirdField Set Property = Field.Properties!ColumnWidth Property = 4320Set Property = NothingSet Field = NothingSet Db = NothingDoCmd.OpenQuery "MyQueryName"
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/dataDim intColumNumber As Integer, ctl As Control10 Set frm = Screen.ActiveDatasheet20 For intColumNumber = 0 To frm.Controls.Count - 130 Set ctl = frm.Controls(intColumNumber)40 ctl.ColumnWidth = -250 Next intColumNumber60 DoCmd.SaveEnd Sub
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.