Saturday, August 28, 2010

Resize query columns

My MS-Access application has several buttons that display a query in datasheet view, so users can sort, filter or export the results. I'm frequently disappointed in the appearance when the columns are either too narrow or wide; of course you can easily resize columns with a double-click, but Access doesn't work the same as Excel, i.e. Access considers only the visible rows when it resizes.

I Google'd for a solution and found the article:
You Can Do That with Datasheets?
and the same code is presented here:
Resizing column to best fit vba
...and the answer relies on two important concepts:

  • When you open a form in datasheet view, if you set ColumnWidth = -2 then Access will resize the columns to their proper widths.
  • You can open a table or query in datasheet view, and then Dim and Set a Form object to it.

The code was a little wordy so I distilled it into the following:

Sub resize_query(query_name As String)
Dim frm As Form, ctl As Control
DoCmd.OpenQuery query_name, acViewNormal
Set frm = Screen.ActiveDatasheet
For Each ctl In frm.Controls
ctl.ColumnWidth = -2
Next ctl
DoCmd.Close acQuery, query_name, acSaveYes
End Sub

Now, if you have a Forms parameter in the query, you will need to have the corresponding form open when you run this code. Otherwise it will prompt you for the parameter(s).

EDIT on August 29:

I have been testing this code on a variety of queries and found that it does not work 100% of the time. Thinking that I might have left out something important, I returned to the original article mentioned above, and it also does not work 100% of the time. Will continue to test this out & hopefully post the final solution.


grovelli said...

Hi Bill, I've tried using your resize_query Sub but when, in the Immediate window, I type
? resize_query "query1"
I get the Compile error: Argument not optional

Bill Mitchell said...

it is a Sub, not a Function, so you don't need the ? (question mark). just type
resize_query "query1"

grovelli said...

Thanks Bill :-)

Ben said...

Thanks. This is a concept I didn't know was possible. It worked great for me!