Question about Autocolumnwidth example from Lebans website

[Jerome]

Hello,

I have an Access database that has a datatable and A form. In this form is a
listbox, using an SQL command I populate data out of the table into the
listbox (using a textbox and a combobox).
Now I would like to use the autocolumnwidth code to automaticially resize
the columns in the listbox.

Here is the website:

http://www.lebans.com/autocolumnwidth.htm

But I have a problem:

in the function "Private Function SetWidthColumn(strFieldName As String) As
Integer" I have to give a fiieldname but the problem is the listbox is
unbound. I use this code to fill it.

Public Sub GetData()

' This is a public sub that collects the right data. It uses SQL statements
to get its data.

On Error Resume Next
Dim StrSql As String 'SQL statement for the record source

sText = Trim(Me.TextSearchString.Text)

StrSql = "SELECT * FROM TblParts "

'Is there any text to test?
'If Not sText = "" And IsDelOrBack = False Then

StrSql = StrSql & "WHERE " & Me.ComboCategory.Column(1) & " Like
'" & "*" & sText & "*" & "*' ORDER BY " & Me.ComboFilteredBy.Column(1) & ";"

'Refresh the rowsource with the new SQL
Me.ListParts.RowSource = StrSql

'End If
'Requery the list box to show results
Me.ListParts.Requery

End Sub

How can I apply this code on the listbox?

Thanks in advance.

 

[Dirk Goldgar]

Hello,

I have an Access database that has a datatable and A form. In this form is
a listbox, using an SQL command I populate data out of the table into the
listbox (using a textbox and a combobox).
Now I would like to use the autocolumnwidth code to automaticially resize
the columns in the listbox.

Here is the website:

http://www.lebans.com/autocolumnwidth.htm

But I have a problem:

in the function "Private Function SetWidthColumn(strFieldName As String)
As Integer" I have to give a fiieldname but the problem is the listbox is
unbound. I use this code to fill it.

Public Sub GetData()

' This is a public sub that collects the right data. It uses SQL
statements to get its data.

On Error Resume Next
Dim StrSql As String 'SQL statement for the record source

sText = Trim(Me.TextSearchString.Text)

StrSql = "SELECT * FROM TblParts "

'Is there any text to test?
'If Not sText = "" And IsDelOrBack = False Then

StrSql = StrSql & "WHERE " & Me.ComboCategory.Column(1) & "
Like '" & "*" & sText & "*" & "*' ORDER BY " &
Me.ComboFilteredBy.Column(1) & ";"

'Refresh the rowsource with the new SQL
Me.ListParts.RowSource = StrSql

'End If
'Requery the list box to show results
Me.ListParts.Requery

End Sub

How can I apply this code on the listbox?

While it's possible that you could adapt that code to work on a list box,
wouldn't it be easier to use the code that Stephen wrote to size listbox
columns?

http://www.lebans.com/listboxcolumnresize.htm
ListBoxColumnResize.zip is a Class that allows the user
to resize the ColumnWidths of a ListBox at Runtime.
Also supports an Autosizing method for the ColumnWidths.