How can I make the column widths of a list box in MS-Access2007 automatically size themselves correctly?

Question

Background info:
I was handed a "Tool", which was made using MS-Access 2007, and asked to add some things... The tool is basically a collection of options for querying a database. On a form titled CreatedReport there is a listbox that is bound to a table called analyzed which has all of resulting data from the query/queries that ran. The original creator of this tool set the column widths to specific values but with the new collection of possible results, those widths are very far off.

Desired Outcome:
The final result I want to achieve is, of course, to have the columns be the correct widths for the info that is in the columns. As long as that is achieved, I really don't care which route I have to take to get there.

Question:
How can I get the columns in a listbox in MS-Access 2007 to be sized appropriately for each use? Is there an auto-size feature I haven't stumbled across yet or do I need to hard code the set of column widths for each group? This wouldn't be too hard to do since there would only be about 4 or 5 different groups but I would prefer for the process to be automatic if at all possible.

Answer 1

Last time I checked, you still had to write code for this.

Your best bet is to use a resizer someone has already written. Here is a good one. It's old, but it shoul still work:

http://www.jamiessoftware.tk/resizeform/rf_jump.html

Answer 2

I don't think that Robert Harvey's answer is actually responsive to your question.

What you need to do is:

1. calculate the maximum length of the values in each column,

AND

1. figure out, based on the font in use, how wide the column should be.

Note that you may not actually want to set it to the maximum width if the value exceeds a certain threshold.

I don't know to do the second taks, but I suspect Stephen Lebans has already done the work on it. You might want to search his website for it.

Answer 3

Another approach would be to have the results returned in a sub form datasheet view, then the user can adjust the column widths also to set the widths automatically use code like this:

Example This example takes effect in Datasheet view of the open Customers form. It sets the column to fit the size of the visible text.

Forms![Customers]![Address].ColumnWidth = -2

You could put this code into the Current Event of the sub form.