Sorting the recordset in a list box (Access 2000)

Topic

New Reply

Hello All!

I’m trying to sort a recordset in a list box called RRAListing. The list box is populated using the following code:

[RRAListing].RowSource = “RRAReportsList1”

where RRAReportsList1 is a query. I’d like to create a button that takes the recordset that is being displayed in the list box and sorts it. For example, it would sort the recordset being displayed in RRAListing by RRANo ASC. The thing is, the RRAListing list box gets it’s RowSource from different queries, depending on what dropdown you used to populate it (the form that RRAListing is on also has several dropdowns. Depending on which dropdown you select data from will depend on what query is used to populate RRAListing). I thought about adding the sort right to the queries, but I want to be able to sort the recordset dynamically using a couple of buttons, each one representing a column in the RRAListing recordset (so you could sort by RRA No, RRA Name or RRA date, etc.) The column headers are shown in the RRAListing list box – there isn’t any way I can make those “clickable” so the user could click on the header to sort the recordset being displayed in the list box, is there? If so – how?? And if not, does anyone have any ideas on how I can get the buttons I create to sort the recordset shown in the list box?

Thanks in advance,

Amanda

Reply | Quote

Replies

Hi Amanda,
Just a thought, but couldn’t you use the AfterUpdate event of the “dropdowns” (I’m assuming combobox) along with the command buttons to dynamically set the RowSource for the list? For example you could have one dropdown set the RowSource of your listbox to an SQL statement and leave out any sort. Just make a “default” sort field the first field in the SQL. Then with each command button, you could add an “ORDER BY” and append it to the end of the existing SQL.

Reply | Quote

The appended ORDER BY should work nicely, so long as you don’t forget to do the “listboxname.REQUERY”. But, I am sure that you already knew that… (some newcomers might not know about REQUERY).

Reply | Quote

The sad part is that I meant to put the REQUERY in the post and just didn’t for some reason or other.

Reply | Quote

Ok, I am not sure I am following you. Right now, the form is set up with the list box (RRAListing) and 3 combo boxes. I am using the AfterUpdate event on the combo boxes to set the RowSource of RRAListing. Like this:

RRAListing.RowSource = “RRAReportsList1”

where RRAReportsList is a query set up in access.

Each combo box has it’s own query so each AfterUpdate event of each combo box sets RRAListing.RowSource to a different query (ex. RRAReportsList1, RRAReportsList2, etc.) I want to create some buttons that will allow me to sort the query results show in RRAListing list box. You are saying I can append “ORDER BY” to the end of the existing SQL? Can you show me some code so I can see what you are talking about?

Thanks,

Amanda

Reply | Quote

Sure.
Say you have a combo box (cboComboBox) with a list of numbers. You could set the AfterUpdate event to be something like:

List1.RowSource=”Select Field1, field2,field3 from YourTable where field1=” & cboComboBox & “;”
List1.Requery

Then, for a command button (cmdOrderBy2), you could set the OnClick to something like

List1.RowSource=Left(List1.rowsource,Len(list1.rowsource)-1) & ” Order By field2;”
List1.requery

This (I think) would sort the list by field2 instead of field1.

The Left(…Len…)) is just there to take the semicolon off the end before appending the Order By

This is a pretty crude code example, but I’m just doing it off the top of my head. If you need something a bit more detailed, please let me know. Or give it a few minutes…I’m sure someone else will post something better. That’s what usually happens.

Reply | Quote

You can download a sample database from Roger’s Access Library here that demonstrates how you can order a list box dynamically. It may not be exactly what you want, but the code in the sample database gives an idea how to go about this.

Reply | Quote

I just went out and looked at the example you posted – it was exactly what I was looking for! Thanks Hans!! And thanks to everyone else that helped me! I’ve got it working now.

Reply | Quote