Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting via VBA (Access 2002 )

    It has been a while since I programmed Access in VBA, so bear with me. <img src=/S/angel.gif border=0 alt=angel width=15 height=21>

    I have this bit of code that works just fine...Just that for some strange reason, when I added a building and rooms, the number order isn't correct (It looks like this: 9 10 11 12 1 2 3 4 5 6 7 8 in the dropdown list) and I checked my tables, and they all are ordered in sequential order. Go figure...Anyway, what I am attempting is to force it to order in Ascending order, but since the combo box is actually programmed to look at the cabin then filter the rooms, I can't simply use the Record Source Query. I'd like to add a command to the line that forces it to sort in ascending order. Here's the snippet of code:

    Forms!frmHousingMatchup!fsubRoom!cboRoom.RowSource = "SELECT [tblroom].[RoomID], [tblroom].[Room]" & "FROM [tblroom]WHERE [CabinID] = " & Me!CabinID

    Where in that code do I tell it to sort in ascending order?

    Thanks a million! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

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

    Re: Sorting via VBA (Access 2002 )

    The sort order of the table is ignored; you have to specify the sort order in the row source of the combo box. If you want to sort on Room:

    Forms!frmHousingMatchup!fsubRoom!cboRoom.RowSource = "SELECT [tblroom].[RoomID], [tblroom].[Room]" & "FROM [tblroom] WHERE [CabinID] = " & Me!CabinID & " ORDER BY [tblRoom].[Room]"

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting via VBA (Access 2002 )

    Man...This must be a new record or something. Just as I close the webpage, pops up your message! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Thanks for your advice.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting via VBA (Access 2002 )

    OK, I tried the code and it works...New problem although.

    It now sorts it as: 1 10 11 12 2 3 4 5...instead of 1 2 3 4...10 11 12. How do you force Access to sort numbers the way it should be sorted?

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

    Re: Sorting via VBA (Access 2002 )

    Apparently Room is a text field, text is sorted differently from numbers.

    If you have only "real" numbers as room numbers, change the field to Number (Long Integer); it will sort correctly then. However, if you also have room numbers such as 11A, you can't do that. In that case, change the last part of the row source to

    ORDER BY Val([tblRoom].[Room])

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting via VBA (Access 2002 )

    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    I completely forgot that fields default to "TEXT"! I'll fix it.

    Thanks 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
  •