Sort order of listbox at runtime

[Wheelz24]

Hi,
I have a listbox which is populated from a qry. Can I set the list box so that i can click on a heading and it sort the data by that column? If so how do I do this.

Thanks

Wheelz

________________________
Kieran Briggs
NJS Solutions

http://www.njssolutions.com

 

[Dalain]

in the query that defines you list, edit the query and enter Accending in the field you want to sort by.

the List box Row Source, which is SQL code, would be :

SELECT Table1.Field1 FROM Table1 ORDER BY Table1.Field1;

Dalain

 

[scriverb]

No you cannot click on a listbox heading and have it resort the listbox contents. It is possible to create command btns. or a combo box to provide this capability but it requires some VBA coding so make sure it is worth the effort. How many records are in the listbox? I would only do this if there was a considerable number of records

Let me know what you decide.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Wheelz24]

Thanks sciverb,
I've got about 500 records in my listbox. Its originally ordered in alphabet on the address, but i also need to be able to order it by ID sometimes, but then go back to addy if need to.

________________________
Kieran Briggs
NJS Solutions

http://www.njssolutions.com

 

[scriverb]

I could create a command button right above the ListBox. Make it wide enough to have a description of the Current Sort order of the list box. (i.e. "NAME sort: Chng. To ID" or "ID sort: Chng.To Name" ) Set the .caption property of this command button initially to the default sort at the time that the form opens.

In the OnClick of this command button use the following:

If Left(Me.cmdChngSortOrder.caption,2) = "NA" THEN
   Me.[i][red]yourlistboxname[/red][/i].RowSource = "[blue]Select . . ORDER BY [i][red]yourtablename[/red][/i].ID;[/blue]" 
   Me.[i][red]cmdChngSortOrder[/red][/i].caption = "ID sort: Chng. to NAME"
else
   Me.[i][red]yourlistboxname[/red][/i].RowSource = "[blue]Select . . ORDER BY [i][red]yourtablename[/red][/i].NAME;[/blue]" 
   Me.[i][red]cmdChngSortOrder[/red][/i].caption = "NAME sort: Chng. to ID"
End If
Me.[i][red]yourlistboxname[/red][/i].Requery

This code should allow you to flipflop your sort order back and forth from NAME to ID and also change the .caption property. You can change the caption property to whatever you deem appropriate but make sure than the IF statement can identify propertly what being used to to determine what to change the sort order to. The SQL statements are just examples. Just use your own SQL with a different ORDER BY clause to change the sort. You can create two saved queries and just change the name of the Query back and forth also if that is easier for you.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Wheelz24]

Thanks for the help Scriver

________________________
Kieran Briggs
NJS Solutions

http://www.njssolutions.com

 

[scriverb]

Post back if you have any problems with this technique. I will help you resolve the situation.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Wheelz24]

The only problem I have with this is that the listbox already uses that method to show 2 different queries in the box depending on the criteria of what the user wants. When the package opens it shows all jobs which are in the database and then the user clicks a button and it shows jobs which are up for renewal in the next month. So i need to add another of the if statments into that to get it working don't I? I'm starting to confuse myself now

________________________
Kieran Briggs
NJS Solutions

http://www.njssolutions.com