"Best Fit" Column Width Through Code

[billschu]

Hi,

I'm wondering if there is a way to do a Best Fit on a column in a query through code, similar to what would happen if you went through the toolbar (Format -> Column Width -> Best Fit).

I know how to set it to a specific size through VB:

Set Prp = fld1.CreateProperty("ColumnWidth", dbInteger) Prp.Value = intColumn_Width
fld1.Properties.Append Prp

The problem is, I really don't know what to set the column width at unless I look at the length of data in each field which would be really time-consuming.

Any thoughts?

Thanks,
Bill

 

[dhulbert]

Bill

Columns("G:G").EntireColumn.AutoFit

obviously change the columns to suit.

 

[billschu]

What object does the Columns property apply to? For a querydef it didn't seem to work (it has Fields property). Are you sure this is for an Access query, and not Excel?

Thanks,
Bill

 

[RoyVidar]

That's Excel.

Just wondering, what is the purpose of the exercise? You shouldn't allow the users anywhere near tables and queries (erm - that is in my very humble opinion;-))

Now, datasheet subforms (which I rarely use), have some capasities in that direction, but I don't think it has any "autofit". But at least you can alter the columnwidth of it. So lets say you retrieve the length of the "longest" field, then you can do some calculation in a relevant event, involving the unit of measure for the columnwidth (points - whatever that is). Perhaps something like this (you'd probably need to work a little on the number there):

[tt]dim rs as dao.recordset
dim strsql as string
strsql = "select max(len(yourfield)) from yourtable " & _
"where <same criterion as the query>"
set rs = currentdb.openrecordset(strsql,dbopenforwardonly)
if not rs.bof and not rs.eof then
me!txtColumn.ColumnWidth = rs.fields(0).value * 100
end if
rs.close
set rs = nothing[/tt]

- typed not tested...

Roy-Vidar