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
Column Width
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Column Width
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Column Width
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.
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
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
0 = hide column
-1 = use default width
-2 = fit to visible contents
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Column Width
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.
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.
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Column Width
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.
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Column Width
And I thought it was the click event of the listbox control.
Many Regards Hans .........
Many Regards Hans .........
Cheers ...
Dave.
Dave.