Sorting a list box in MS Access

[DolfTraanberg]

-{ Quote: " quoting: UNICRON link=board=6;threadid=12163;start=0#msg78220 date=1060232217]
I am a database developer ......" }-

I know, off topic, but do you know a way to sort the content of an unbound listbox in Access 2000

Dolf

[UNICRON]

not off topic anymore ;)

what is the row source and row source type?

If you are using a table or query, use the query builder to add a sort order to the recordet.

if you are using a list, order the list as you write the values in.

[DolfTraanberg]

what I want to accomplish, is transferring data from one listbox to a second one.
The first time it's working well, because in the first listbox, the data has already been sorted, but when you modify the first transfer, it becomes a mess, because with the second move the data will just be added to the list.
Does this make any sense?
Dolf

like this:

[DolfTraanberg]

I mean a mess like this ;D

[UNICRON]

honestly not really :-\

I don't know what type of algorithm you are using to accomplish this. I you wish to send me what you have , I can try to get it working for you. Or you could post some more implementation details, that might help.

OH the pics help abit. That is TDS is it not?

[DolfTraanberg]

;D It seems I'm not the only one, having this problem....

For now I got only way traffic:

Private Sub cmdSelect_Click()
Dim strItems As String
Dim strItems2 As String
Dim intItem As Integer

For intItem = Keuzelijst1.ListCount - 1 To 0 Step -1
If Keuzelijst1.Selected(intItem) Then
strItems = strItems & Keuzelijst1.Column(0, intItem) & ";" & _
Keuzelijst1.Column(1, intItem) & ";" & _
Keuzelijst1.Column(2, intItem) & ";"
Else
strItems2 = strItems2 & Keuzelijst1.Column(0, intItem) & ";" & _
Keuzelijst1.Column(1, intItem) & ";" & _
Keuzelijst1.Column(2, intItem) & ";"
End If

Next intItem
If Keuzelijst3.ListCount > 0 Then
For intItem = 0 To Keuzelijst3.ListCount - 1
strItems = strItems & Keuzelijst3.Column(0, intItem) & ";" & _
Keuzelijst3.Column(1, intItem) & ";" & _
Keuzelijst3.Column(2, intItem) & ";"

Next intItem
End If
Keuzelijst3.RowSource = ""
Keuzelijst3.RowSourceType = "Value List"
Keuzelijst3.RowSource = strItems
Keuzelijst1.RowSource = ""
Keuzelijst1.RowSourceType = "Value List"
Keuzelijst1.RowSource = strItems2

End Sub

[UNICRON]

I think I understand your intentions:

Take all selected list items from one listbox ad add them to a second box.

Since the first box was sorted, the items selected should be gathered in sorted order. You purposely divide the list into two strings, both ordered in reverse sorted order.

you add whatever is already in the the second box to the one string, and set the other string to the fist box.

Assuming the second box starts off empty, any values in there would be in reverse sorted order.

the problem you are having is that you need to sort the contents of Keuzelijst3 with strItems, not just tack them on the end.

[UNICRON]

I created a table to add the value to, used it to sort the values and spit them back into your list. It could be done slicker, but we'd have to modify more of your other stuff. This should do you.

I used the DAO object model, if you use ADO, you'll have to change the recordset code or add the DAO object model into your references.



Private Sub cmdSelect_Click()
Dim strItems As String
Dim strItems2 As String
Dim intItem As Integer

Dim sql As String
Dim rs As Recordset

DoCmd.SetWarnings (0)
On Error Resume Next
sql = "CREATE TABLE tbl_Keuzelijst3 (col1 text(50), col2 text(50), col3 text(50))"
DoCmd.RunSQL sql
sql = "DELETE * FROM tbl_Keuzelijst3"
DoCmd.RunSQL sql
DoCmd.SetWarnings (1)

sql = "SELECT * FROM tbl_Keuzelijst3"
Set rs = CurrentDb.OpenRecordset(sql)


For intItem = Keuzelijst1.ListCount - 1 To 0 Step -1
If Keuzelijst1.Selected(intItem) Then
rs.AddNew
rs!col1 = Keuzelijst1.Column(0, intItem)
rs!col2 = Keuzelijst1.Column(1, intItem)
rs!col3 = Keuzelijst1.Column(2, intItem)
rs.Update

Else
strItems2 = strItems2 & Keuzelijst1.Column(0, intItem) & ";" & _
Keuzelijst1.Column(1, intItem) & ";" & _
Keuzelijst1.Column(2, intItem) & ";"
End If

Next intItem

If Keuzelijst3.ListCount > 0 Then
For intItem = 0 To Keuzelijst3.ListCount - 1
rs.AddNew
rs!col1 = Keuzelijst3.Column(0, intItem)
rs!col2 = Keuzelijst3.Column(1, intItem)
rs!col3 = Keuzelijst3.Column(2, intItem)
rs.Update

Next intItem
End If

rs.Close
sql = "SELECT * FROM tbl_Keuzelijst3 ORDER BY col3"
Set rs = CurrentDb.OpenRecordset(sql)

While Not rs.EOF
strItems = strItems & rs!col1 & ";" & rs!col2 & ";" & rs!col3 & ";"
rs.MoveNext
Wend
rs.Close

Keuzelijst3.RowSource = ""
Keuzelijst3.RowSourceType = "Value List"
Keuzelijst3.RowSource = strItems
Keuzelijst1.RowSource = ""
Keuzelijst1.RowSourceType = "Value List"
Keuzelijst1.RowSource = strItems2

End Sub

[DolfTraanberg]

PERFECT,
Thanks a lot.
I was searching the net now for about two days and all I found were people with the same problem ;D
I wish I could give you a box of karma cookies!

I'll pass this link on to Wayne ;D
Dolf

[UNICRON]

if you want to do sorting outside of access (no tables to use), you could have just used a two dimentional array and any one of the sort algorithms like bubblesort, mergesort, quicksort, radixsort, heapsort etc etc.

I just used tables because in access that is fastest.

[DolfTraanberg]

Yes, I like that idea to sort it without using tables, but I'm afraid I don't know any of those algorithms you suggested. Any links?
Thanks
Dolf

[UNICRON]

-{ Quote: " quoting: Dollefie link=board=9;threadid=12164;start=0#msg78765 date=1060461308]
Any links?
" }-

yes, the best one I know of is http://www.google.com/

;D

[DolfTraanberg]

Ok sorry. :-[
I did search google, but what I got was related to C+, pearl, PHP etc. , i'll try to narrow it down.
Dolf

[UNICRON]

http://www.joyofvb.com/JVB_AlgorithmToolkit.htm

http://www.vb-helper.com/tut1.htm

[DolfTraanberg]

Thanks again :D
Dolf

[UNICRON]

Think nothing of it freind. Just happy to help ;D