CODEWITHSUNDEEP
ms-accesslistboxms-access-2007
Bryan
Bryan

Reputation: 1893

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

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.

Upvotes: 0

Views: 6833

Answers (4)

Dan McSweeney
Dan McSweeney

Reputation: 141

This is a quick solution that should help when you want to set up listview columns of different widths, and you know in advance the widths you want (eg, you know that column X will always be a 2-character State abbreviation, and column Y will always be a city name).

Just supply all the widths as a single semi colon-delimited string. Code each width as a number and a unit, such as 'in' or 'cm'. This worked well for me: Me.lsvPayHist.ColumnWidths = "1.0 in;0.8 in;1.0 in;1.0 in;2.0 in"

Upvotes: 0

Mark3308
Mark3308

Reputation: 1333

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.

Upvotes: 2

David-W-Fenton
David-W-Fenton

Reputation: 23067

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.

Upvotes: 1

Robert Harvey
Robert Harvey

Reputation: 180808

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

Upvotes: 0

Related Questions