<

Right-sizing Access Query Columns with VBA

Published on
3,820 Points
720 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
1 Comment
 
LVL 66

Expert Comment

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month