changing column widths of a subform's datasheet 1

[teach314]

hi to all - I have a subform that shows records in datasheet form. When a button on the parent form is clicked, the query underlying the subform's datasheet changes. Each of these datasheets has 9 columns, but the Field Names change depending on the underlying query. Only the 1st column keeps the same name ("Grp_ID").

I want to change the column width of the datasheet columns for 'best fit'. The usual way to do this is to add code to the subform's Load Event

[indent][/indent]Private Sub Form_Load()
    [indent][/indent]Me.Column1Name.ColumnWidth = -2
    [indent][/indent]Me.Column2Name.ColumnWidth = -2
    [indent][/indent]Me.Column3Name.ColumnWidth = -2
[indent][/indent]End Sub

My problem is that the column names CHANGE whenever I change the query. So... Is there a way to reference the columns of the datasheet BY POSITION, not by name?

Thanks in advance for any clues.
Teach314

 

[MajP]

Look here at the FixColumnWidth Procedure and the SetDAOFieldProperty

 

[MajP]

https://msdn.microsoft.com/en-us/library/office/aa217449(v=office.11).aspx

Sorry here is the link

 

[missinglinq]

For some reason I can't pull up the link MajP gave, but all you need to do is loop through the controls and do the same thing as you posted, like this:

Private Sub Form_Load()
 
  Dim ctl As Control
     
  For Each ctl In Me.Controls
    
   If TypeOf ctl Is TextBox Then
     
    ctl.ColumnWidth = -2
 
   End If
    
  Next ctl
    
End Sub

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[teach314]

Thanks MajP - excellent link. It uses some techniques I wasn't familiar with at all, but things are now working properly!

 

[baranbaran]

this is a very good post by you....well done for this great post man......good job

Graduated from Soran University with First Class Degree with Honours in Computer Science.