Sorting via VBA (Access 2002 )

Topic

New Reply

It has been a while since I programmed Access in VBA, so bear with me.

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!

Reply | Quote

Replies

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]”

Reply | Quote

Man…This must be a new record or something. Just as I close the webpage, pops up your message!

Thanks for your advice.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

I completely forgot that fields default to “TEXT”! I’ll fix it.

Thanks again.

Reply | Quote