Autofit columns on query result

[sumdumgai]

How can I auto fit the column widths on a datasheet view that results from a query? Thanks.

 

[arnelgp]

you can't with query alone.
you need to create a datasheet form and your query as the recordsource.

add code to the Open event of the datasheet form, to autofit the columns:

Private Sub Form_Open(Cancel As Integer)
    Dim ctl As Access.Control
    For Each ctl In Me.Controls
        If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
            ctl.ColumnWidth = -2
        End If
    Next
End Sub

 

[sumdumgai]

Just to be clear, I do not want to autofit the fields on the form. When a query is executed and it returns a result table, I want to autofit its columns. Thanks.

 

[Cronk]

No. When a query is opened it does not show a table - there is no table. It displays the records on the screen returned by the query. Arnel has provided one way. Another is to export the records to Excel and autofit the sheet columns.

 

[arnelgp]

if you execute the query, you need to manually adjust the columns.

on my suggestion, create a datasheet form from the query. then Open that form.

DoCmd.OpenForm "yourDataSheetForm"

when you open the form, of course your query got executed.

 

[CJ_London]

or if you don't want a separate form and query, put the sql from the query as recordsource to the form (rather than using the query name)

 

[The_Doc_Man]

I do not want to autofit the fields on the form. When a query is executed and it returns a result table, I want to autofit its columns.

You misunderstand the mechanics of what you are doing. The column width that you want to adjust DOES NOT EXIST.

When you open a query or table underneath or otherwise bound to a form, there is (obviously) a form. That form can have columns if it is tabular, and you can adjust the columns.

When you open a query OR table in datasheet view, there is an implied tabular form involved and so you ARE opening a form, a default form supplied by Access. Arnel told you how to adjust those columns.

When you open a query or table using recordsets or ANY non-displayed other method that you might imagine, there is no form and therefore no column to adjust. If it is not being displayed, column width isn't a property that even exists to be adjusted. Width implies printed or displayed output.

 

[MajP]

This gives me about an 80% solution to autofit any query. However, the datasheet solution would be a better approach
You can set the column width of a query def, but I do not think there is an autofit property. To set the column width I think you need to read all the records and find the longest record.

Public Sub AutoFitQuery(qryName As String)
  On Error Resume Next
  Const TwipsToInches = 1440
  Const TWIPSTOCHARWIDTH = TwipsToInches * 0.08
  Dim I As Integer
  Dim fld As Field
  Dim qdf As QueryDef
  Dim db As DAO.Database
  Dim colChars As Collection
  Set colChars = GetMaxChars(qryName)
  Set db = CurrentDb
  Set qdf = db.QueryDefs(qryName)
  I = 1
  For Each fld In qdf.Fields
    fld.Properties("ColumnWidth") = colChars(I) * TWIPSTOCHARWIDTH + 200
    I = I + 1
  Next fld
  DoCmd.OpenQuery qryName
End Sub

Public Function GetMaxChars(qryName As String) As Collection
  Const ConMaxChars = 255
  Dim I As Integer
  Dim fld As DAO.Field
  Dim RS As DAO.Recordset
  Dim maxChars As Integer
  Dim curChars As Integer
  Dim NumberOfFields As Integer
  Set GetMaxChars = New Collection
  Set RS = CurrentDb.OpenRecordset(qryName)
  NumberOfFields = RS.Fields.Count
  For I = 0 To NumberOfFields - 1
    RS.MoveFirst
    maxChars = Len(RS.Fields(I).Name)
        If RS.Fields(I).type = dbMemo Then 'Do not want to autofit a memo
          GetMaxChars.Add (ConMaxChars)
        Else
          Do While Not RS.EOF
            curChars = Len(CStr(Nz((RS.Fields(I)), 0)))
            If curChars > maxChars Then maxChars = curChars
               'Debug.Print maxChars & " " & I
          RS.MoveNext
          If maxChars > ConMaxChars Then maxChars = ConMaxChars
           Loop
           Debug.Print maxChars & " " & I
          GetMaxChars.Add (maxChars)
        End If
   Next I
End Function
Public Sub TestAutoFit()
  AutoFitQuery "qry3"
End Sub

To refine this you would need to look at other datatypes and the format property on those datatypes to make a determination

 

[RogerCooper]

Here is code I use to autofit. The query must be open in the workspace and have focus.

Public Function FixColumnWidthsOfQuery _
               (stName As String) As Boolean
    Dim db As Database
    Dim qdf As QueryDef
    Dim frm As Form
    Dim ictl As Integer
    Dim ctl As Control
    Set db = CurrentDb
On Error GoTo STANDARD_ERR
    Set qdf = db.QueryDefs(stName)
    DoCmd.OpenQuery stName, acViewNormal
    Set frm = Screen.ActiveDatasheet
    For ictl = 0 To frm.Controls.Count - 1
     Set ctl = frm.Controls(ictl)
     ctl.ColumnWidth = -2
     Call SetDAOFieldProperty(qdf.Fields(ictl), "ColumnWidth", ctl.ColumnWidth, dbInteger)
    Next ictl
    DoCmd.Save acQuery, stName
    FixColumnWidthsOfQuery = True
STANDARD_ERR:
End Function

 

[RogerCooper]

This is also needed

Public Sub SetDAOFieldProperty _
               (fld As DAO.Field, _
 stName As String, vValue As Variant, _
               lType As Long)
    Dim prp As DAO.Property
        For Each prp In fld.Properties
        If StrComp(prp.Name, stName, _
         vbBinaryCompare) = 0 Then
            prp.Value = vValue
            Exit For
        End If
        Set prp = Nothing
    Next prp
    
    If prp Is Nothing Then
        Set prp = fld.CreateProperty(stName, _
         lType, vValue)
        fld.Properties.Append prp
    End If
On Error GoTo STANDARD_ERR
STANDARD_ERR:
End Sub

 

[The_Doc_Man]

To amplify what Roger Cooper just posted, note specifically this line from post 9:


Set frm = Screen.ActiveDatasheet


This is because in datasheet view, the form was generated automagically by Access after looking at the the underlying data source. You can't find it until it has been generated and opened. Only then does it exist. And it vanishes when you close the view. Worse, you can't save it as a view because as a system-generated temporary entity, it is nameless. Worst of all, if you try to open the table or query a second time in datasheet view after having given the previous view a name and saved it, you won't use the one you saved. Access will make a new one for you from scratch.

 

[Cronk]

Doc, while the generated form is only in memory and temporal, the querydef is saved with the changes ie the changed column width in the query is maintained.

 

[The_Doc_Man]

I had not run across that before, Cronk. I must have not been saving it correctly.

Wouldn't be the first time I fat-fingered something.

 

[Cronk]

It was only through trying the code supplied that I found out. I normally don't provide for users to open queries, preferring using forms to display data.