Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2005
    Posts
    20

    Unanswered: access listbox not sorting properly

    I have a form with a listbox that is bound to a query. On the rowsource property I hit the ellipses to build the query. I am able to write the query and I specify 2 columns to sort on. When I run this in the query builder I get rows returned in the correct sort order. This form is set to launch on application startup and when it does load, the list box is not sorted in the order I specify. It seems to be re-sorted on the bound column. If I go into design mode then back to form view, without making any changes, the listbox will populate and is sorted in the correct order. Then the next time I launch the app, the sort order is messed up again and I have to go into design view then back to form view. Does anyone have a clue as to what is going on? After about 10 hours I give up.
    Thanks, Bill

    Edit: Forgot to mention that if I close the application but leave the instance of access open so that my odbc connections are still open, then relaunch my app in the open window (don't have to log into odbc again) the listbox will sort correctly.
    Last edited by hayesbcajh; 09-05-07 at 17:47.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Are you logging into ODBC and linking the tables every time you open app? If you link in an app via ODBC (SQL Server tables?) There is a save password checkbox. The next time you open the app, you should not need to re-connect/re-link. The table should already be there in the mdb. If the table is not already linked into the app each time you open the mdb, the setting to prompt for the file location each time is checked and you need to uncheck it.

    Otherwise, linking in a table each time you open the app most likely won't save your sorting order as the table isn't recognized upon MSAccess opening.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I usually set the rowsource for the control after I open the form then control.requery - usually it sorts as expected - although if in fact these are linked (not to a JET db) than sort behavior can sometimes give unexpected behavior

    BTW - when no sort (Order by) is given the data is always sorted by the key field in ascending order.
    Dale Houston, TX

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by axsprog
    BTW - when no sort (Order by) is given the data is always sorted by the key field in ascending order.
    FALSE
    When there is no ORDER BY clause specified, data is not sorted. If ORDER BY is ommitted then you cannot trust the data to be in any order.
    George
    Home | Blog

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Hold on Geo

    I have several tables in the present db I am working on

    Order number is Always sorted ascending regardless of the other fields in the query - as long as ordernumber is in the sql statement and I specify no sort - I will say this to clarify my statment - I ALWAYS place the ordernumber or primary key first in my select statements - and that may be reason I have always observed the ascending order behavior. In addition - all tables that I generally Order By ASc - I usually sort the table in tableview and then save that setting - so my statement while incorrect technically is correct locally (at my desk) because I have only done it that way for 16 years.

    Thank you Geo for calling me out on that.

    Good morning BTW
    Dale Houston, TX

  6. #6
    Join Date
    Mar 2005
    Posts
    20
    I ended up re-linking all my tables (20 of them) and it seems to be working again.

Posting Permissions

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