Adjust column width to best fit using vba

[ramanan1986]

Hi,

Does anyone know how to adjust the column width of the fields to best fit in a Query using vba codes?
Manually it can be done by selecting the Query - Click on More dropdown of the Reports section in the ribbon - Click on Column width - and select best fit as the option.

I want this to happen by a vba code and not manually doing it everytime because the field length changes as it is a dynamically created Query.

Any suggestions!!

 

[CJ_London]

You can't do this in a query,but you can in a datasheet view of a form using the columnwidth property for each column - see this link, use -2 to set to autowidth

http://msdn.microsoft.com/en-us/library/office/aa224081(v=office.11).aspx

Manually, you can also do this by selecting all data by clciking on the square box top left, then double clicking on one of the column separators in the heading row

 

[ramanan1986]

Hi CJ,

How I was trying to sort this out is using the code below.
The qdf is the Query Definition for my Query on which I am trying to do adjust the column size to the best fit.

Dim i As Integer
Dim fld As DAO.Field

For i = 0 To qdf.Fields.Count - 1

Set fld = qdf.Fields(i)
fld.CreateProperty "ColumnWidth", dbInteger
fld.Properties("ColumnWidth") = -2 ' ----> Throws me an error on this line saying the property is not found
Set fld = Nothing

Next i

Even in the link that you have quoted, -2 refers to the property that helps it size the column to fit the size of the visible text.

If you can help me out here, I may be able to sort this out.

 

[spikepl]

Now read CJ's comment again:

You can't do this in a query,but you can in a datasheet view of a form

You do not normally show tables or queries to the user, only forms and reports.