<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Right-sizing Access Query Columns with VBA

Published on
4,048 Points
948 Views
1 Endorsement
Last Modified:
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.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"

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

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.

 
1
Comment
2 Comments
LVL 67

Expert Comment

by:Jim Horn
Some supporting images would really help here, as the article speaks to cosmetics.
1

Expert Comment

by:Bill McKay
I needed this exact solution.  I used your code Paul and in 5 minutes it was humming along nicely!  Thank you sir!
1

Featured Post

Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Join & Write a Comment

Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month