ListBox columns' width

[simon03]

Hello,

I need a few suggestions about the visualisation of my queries. At the moment my DB is doing is the following:

1) the user makes some selections in a form and run a VBA code (activated by a bottom command) which essentially creates a query at the end of the process;

2) The query is displayed in a listbox within a new form which is opened by the VBA code.

I have the following problem. The VBA code creates a query with a number of columns that varies depending on the user's selection in the first form. Number of columns can range between 5 and 35 so you can imagine that as the number of columns increases, it's almost impossible to read the content of each column.

So here my two questions:

1) Is there a way to set the columns' width to Auto as well as the total width of the listbox?

2) Would you use another object instead of listbox to visualise the query's results?

Note that I need to display in the same window the query's results with some values contained in a table. For this reason the second form contains two listbox one uses the query as Row source and the second one the mentioned table.

Thank you for your help!
S.

 

[CJ_London]

activated by a bottom command

Love the idea of a bottom command

 

[simon03]

OPSSSSSSSSSSSSS LOL

Btw, I guess that the Autowidth option is not implemented in Access and this needs to be done programatically.

This is the first attempt that I have implemented:

1) With a RecordSet I count the number of columns in the query

2) Because I know the width of the columns (the first and the last 3 columns are 3cm, the other columns 1.5cm), I create a string that contains the width of my columns, something like:

str = "3cm;"

For varItem = 0 To Int(rs.Fields.Count) - 4
str = str & "1.5cm;"
Next

str = str & "3cm;3cm;3cm;"

3) I set the properties ColumnCount and Columnwidth of the listbox using rs.Fields.Count and the string str

This works for my use.

Still not sure if using form+listbox is the best solution!

 

[CJ_London]

Still not sure if using form+listbox is the best solution!

Just some comments...

You don't need to put the listbox on a separate form

You could look at using the ActiveX listview control as an alternative

Another alternative is to have a subform in datasheet view which gives more options - but you need to set up with a number of controls - call them C1, C2 etc and have a loop to assign each field to a control then hide the ones you don't use.

One of the problems with a listbox is you can't format doubles, currency etc and align right unless you do this in your rowsource - but doesn't matter if you aren't displaying these types