Vba – Access subform, how to resize columns to best fit

ms-accessvba

I have a form with a subform. This subform displays the results of a query that is created dynamically (user enters criteria, I build the SQL, then update the querydef and display). Problem is since the columns are dynamic the width of the columns isn't working out, some are cutting off text.

Is there a way to programmatically loop through the columns (or do the same without loop) and set them all to bestfit width after the query is refreshed?

EDIT: Here is what my code looks like now:

CurrentDb.QueryDefs("SearchResults").sql = sql
CurrentDb.QueryDefs.Refresh   

Dim qdf1 As DAO.QueryDef
Dim fld1 As DAO.Field

Set qdf1 = CurrentDb.QueryDefs("SearchResults")

For i = 0 To qdf1.Fields.Count - 1
    Set fld1 = qdf1.Fields(i)
    fld1.CreateProperty "ColumnWidth", dbInteger

    fld1.Properties("ColumnWidth") = -2     'Throws error
    Set fld1 = Nothing
Next i

Me.Child20.SourceObject = "Query.SearchResults"

Best Answer

You can set column widths like so:

Sub SetColumnWidth()
Dim qdf1 As DAO.QueryDef
Dim fld1 As DAO.Field

Set qdf1 = CurrentDb.QueryDefs("query3")

For i = 0 To qdf1.Fields.Count - 1
    Set fld1 = qdf1.Fields(i)
    fld1.CreateProperty "ColumnWidth", dbInteger
    'very narrow indeed
    'fld1.Properties("ColumnWidth") = 200
    'Or -2 : Sizes the column to fit the visible text
    'but it is not quite as useful as it would seem
    fld1.Properties("ColumnWidth") = -2
    Set fld1 = Nothing
Next i

End Sub

Related Solutions

Vba – MS Access VBA – display dynamically built SQL results in datasheet subform

short and sweet. Here is the code for a button that creates dynamic sql string, closes the current object (just in case its open), deletes a temporary query definition (because we need one), creates a new query definition with the new sql, changes the recordsource and Bob's your uncle.

Private Sub btnRunSQL_Click()
  'my subform is called datasheet, i know -- dumb name.
  'a dynamic sql needs to be saved in a temporoary query. I called my qtemp
  Dim sql As String
  sql = "select * from client order by casename asc"
  'in case there is something kicking around, remove it first, otherwise we can't delete the temp query if it is still open
  Me!Datasheet.SourceObject = ""
  'delete our temporary query. Note, add some err checking in case it doesn't exist, you can do that on your own.
   DoCmd.DeleteObject acQuery, "qtemp"
  'lets create a new temporary query
  Dim qdf As QueryDef

  Set qdf = CurrentDb.CreateQueryDef("qtemp", sql)
  'set the subform source object
  Me!Datasheet.SourceObject = "query.qtemp"
  'and it should work.
End Sub

Best Answer

Related Solutions

Vba – MS Access VBA – display dynamically built SQL results in datasheet subform

Related Topic