List box question, can you sort by column?? 2

[BubbaJean]

I have a list box "List74" (based on a query with 4 columms) located on a form. Is it possible to allow users to click onto the listbox, and then double click on any column header for the purpose of re-sorting the list based on the column they've selected??

 

[chrissie1]

no i dont think so because you cant select a colum or a cell in a listbox, only rows, so there is no way of telling on wich column the user clicked "What a wonderfull world" - Louis armstrong

 

[SgtJarrow]

Not exactly what you want, but you could provide a option group to allow them to resort the list box....and possibly you could use the double click of the list box and just cycle through different sort orders....

But I don't think you can do what you are asking... Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[BubbaJean]

So if I create a option group, would I use a case statement or a If statement? Do I need to pass the value to a invisible text box? When refering to the listbox do I use the column text name or the column number?
i.e.,
Private Sub optSortBy_Afterdate
Select Case optSortBy
Case Is = 1 Then
Me.List74.acCmdSortAscending = Column(0)
Case Is = 2 Then
Me.List74.acCmdSortAscending = tableName.fieldName
Case Is =3
Me.List74.acCmdSortAscending = PartNumber
End Select
etc...

 

[SgtJarrow]

I guess it depends on how you have the listbox set up....you can simply update the SQL for the listbox with each of the options and then requery the listbox....

In this case, I would use the OnClick event for each of the option buttons to change the SQL for the listbox, and then use the AfterUpdate event of the option group to reuqery the listbox.... Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[jordanh]

I'm trying to do a similar thing. I have a three column list box (that uses an SQL query to pull data) and a combo box (with manually entered options) to select the names of the three columns.

When you select a name, eg "year" I need the list to requery and order by the selected column.

I've tried to do this by using

ORDER BY "[query].[" & Forms!form!combo.Value & "]"

in the List boxes SQL query (where 'query', 'form' and 'combo' are replaced by the names) and it doesn't work. Is there a way of getting this to work using the List Box's SQL query?

Thanks in advance, and sorry for butting in on your thread BubbaJean

Jordan

 

[jordanh]

P.S. there's a list box requery in the AfterUpdate event of the combo box.

 

[BubbaJean]

Please butt in, all that matters is getting it to work, good luck

 

[SgtJarrow]

Using Access 2000, I did the following:

Created a table with:

Date
Name
Number

Created a form with a listbox (List0) and a combobox (Combo2). The combobox was populated with manually entered data of Date, Name, and Number. The listbox was populated from the table.

Using the afterupdate event for the combobox, I entered the following code:

' *************** Start Code ****************
Select Case Combo2.Value
Case "Date"
List0.RowSource = "SELECT Table1.Id, Table1.Date, Table1.Name, Table1.Number FROM Table1 ORDER BY Table1.Date;"
Case "Name"
List0.RowSource = "SELECT Table1.Id, Table1.Date, Table1.Name, Table1.Number FROM Table1 ORDER BY Table1.Name;"
Case "Number"
List0.RowSource = "SELECT Table1.Id, Table1.Date, Table1.Name, Table1.Number FROM Table1 ORDER BY Table1.Number;"
End Select

List0.Requery
' *************** End Code *****************

Then I tested it. By selecting the appropriate field from the combobox, the listbox was resorted...

I can send an example db if you need it. Just send me an e-mail and I will forward you.

Hope this gets you where you want to be... Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[jordanh]

superb. Works a treat. Many thanks.

Jordan

 

[chrissie1]

you learn something new here everyday

great find "What a wonderfull world" - Louis armstrong

 

[BubbaJean]

Thanks All, my list box is finally behaving itself..