Column Width

[D Willett]

Hi

Does anyone know of an API or similar to automatically adjust column width in a datasheet subform.
I know I can dbl click the right side of the header but its just a pain with many subforms. The length of the data in each field changes all the time but the width of the column has to be manually adjusted all the time.

Any help appreciated.

Regards

[HansV]

The following code will adjust the column widths when the form is loaded; you could also add the same code to the On Current event of the form.

Code: Select all

Private Sub Form_Load()
  Dim ctl As Control
  For Each ctl In Me.Controls
    Select Case ctl.ControlType
      Case acTextBox, acComboBox
        ctl.ColumnWidth = -2
    End Select
  Next ctl
  Set ctl = Nothing
End Sub

ColumnWidth can be a positive number to specify the actual width in twips, or one of the following:
0 = hide column
-1 = use default width
-2 = fit to visible contents

[D Willett]

Hi Hans
Thanks for the code, just had chance to try it out.
I have an error occuring though.
On the main form (Parent) there is a tab control which has a subform on each tab.
I have code on the tab:

Select Case TabCtl0
Case 0 'First Page
sbfqryU.Requery
Case 1 'Second page
sbfqryA.Requery
Case 2 'Third page
sbfqryC.Requery
Case 3 'Third page
sbfqryF.Requery
Case 4 'Third page
sbfqryX.Requery
End Select

Also on the main, I have a list:

Select Case lstDates
Case "Today"
Me.txtDateFrom = Date
Me.txtDateTo = Date
Case "Tomorrow"
Me.txtDateFrom = Date + 1
Me.txtDateTo = Date + 1
' etc etc...........
End Select
Me.Refresh

I'm not sure wheter the Requiry or the Refresh is conflicting with the ColumWidth code..

Any idea's on how to achieve the same result without conflict?

The error is: Method 'ColumWidth' of object '_TextBox' Failed.

[HansV]

Does it help if you change the line

For Each ctl In Me.Controls

to

For Each ctl In Me.Detail.Controls

where Detail is the name of the detail section of the form? The original code will fail if you have text boxes or combo boxes in the form header and/or footer.

[D Willett]

And I thought it was the click event of the listbox control.

Many Regards Hans .........