Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort order/Filtering in a list box (XP)

    2 questions:

    #1. The below code ensures that on loading my form a list box automatically updates to show all the reports in my database however I cannot change the sort order. I have tried naming the reports 01, 02, 03 etc. but the listbox still does not list the reports numerically. I'm sure that there is some simple line of code that I am missing - could you advise me what that would be?

    #2. Would I be able to filter which reports I show in this list box e.g., only show reports where left 2 digits = 01 - 09?

    This is the code that I am using

    Private Sub Form_Load()

    Dim objAO As AccessObject
    Dim objCP As Object
    Dim strValues As String

    Set objCP = Application.CurrentProject
    lstReports.RowSourceType = "Value List"

    For Each objAO In objCP.AllReports
    Me.lstReports.AddItem (objAO.Name)

    Next objAO

    End Sub

    I appreciate your help.

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort order/Filtering in a list box (XP)

    Pete
    Maybe the fact that you are setting the rowsource type to "Value List" has something to do with it.

    I use a query instead:

    SELECT msysobjects.Name
    FROM msysobjects
    WHERE (((msysobjects.Type)=-32764))
    ORDER BY msysobjects.Name;

    I think you could add another field to this as numeric and sort from there.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort order/Filtering in a list box (XP)

    As Dave suggests, a query will take care of that problem. Otherwise, the list is in the order you added the items.
    Charlotte

  4. #4
    New Lounger
    Join Date
    Oct 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort order/Filtering in a list box (XP)

    I have amended the form to the query that Dave suggested but the order is still being controlled by the field "Id" in the MSysObjects table. There doesn't seem to be anything that I can do to override the order that the system is using even though I have amended all the potential field orders that I can. Any more ideas?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort order/Filtering in a list box (XP)

    Are you still using AddItem or did you change the rowsource to a table/query? If your SELECT statement has an OrderBy clause and Name, the order *can't* be controlled by a field that isn't in the records returned.
    Charlotte

  6. #6
    New Lounger
    Join Date
    Oct 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort order/Filtering in a list box (XP)

    No I changed completely to a table/query - and I made sure that the OrderBy clause is the field with the numeric characters, i.e., 01-,02- etc. If however I change the name of a report this then goes to the bottom of the list irrespective of what number I have assigned to the first 2 digits.

    As a further issue - how do I get the listbox just to show the results of the query and not show SQL that I am using?

  7. #7
    New Lounger
    Join Date
    Oct 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort order/Filtering in a list box (XP)

    I just deleted the original list box and re-created it and now it works fine! Sorry for being a pain - I should have tried that first.

Posting Permissions

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