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.

5 comments:

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!

Ben said...

I have turned this into an AutoKeys macro that I add to most of my databases. I can then press CTRL+R to resize the columns of any datasheet. This is super handy when opening up tables you are unfamiliar with, like when you inherit someone else's database.

I have worked out the bugs so it now works with any table or query that is opened as a datasheet, as well as a datasheet that is open on a subform. If a column has text wider than 8000 twips it will be resized to 8000 twips so it fits better on the screen. It supports Memo fields with Rich Text.

I'm willing to share if there is a good place to put it.