help fix teh function plz!

these two functions adjust column widths of a table to "fit to"... i use quote marks because it seems to take the width of the widest cell ON THE SCREEN and set the column width to that in twips.

is it possible to adjust the functions so that it finds the WIDEST value of the WHOLE column? or will that take too long?

thanks for your input.

Public Function FixColumnWidthsOfTable(stName As String)
   
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As DAO.Field
    Dim frm As Form
    Dim ictl As Integer
    Dim ctl As Control
   
    Set db = CurrentDb
    Set tdf = db.TableDefs(stName)
    DoCmd.OpenTable stName, acViewNormal
    Set frm = Screen.ActiveDatasheet
    For ictl = 0 To frm.Controls.Count - 1
     Set ctl = frm.Controls(ictl)
     ctl.ColumnWidth = -2
     Call SetDAOFieldProperty(tdf.Fields(ictl), "ColumnWidth", ctl.ColumnWidth, dbInteger)
    Next ictl
    DoCmd.Save acTable, stName
End Function

Private 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
            Debug.Print fld.name
        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
End SubStart Free Trial