Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting the recordset in a list box (Access 2000)

    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

  2. #2
    Lounger
    Join Date
    May 2002
    Location
    Wytheville,VA, Virginia, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting the recordset in a list box (Access 2000)

    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.

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting the recordset in a list box (Access 20

    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).

  4. #4
    Lounger
    Join Date
    Apr 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting the recordset in a list box (Access 2000)

    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

  5. #5
    Lounger
    Join Date
    May 2002
    Location
    Wytheville,VA, Virginia, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting the recordset in a list box (Access 20

    The sad part is that I meant to put the REQUERY in the post and just didn't for some reason or other. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  6. #6
    Lounger
    Join Date
    May 2002
    Location
    Wytheville,VA, Virginia, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting the recordset in a list box (Access 2000)

    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.row source)-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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting the recordset in a list box (Access 2000)

    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.

  8. #8
    Lounger
    Join Date
    Apr 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting the recordset in a list box (Access 2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •