code to control sorting

[Mark Andrews]

Anybody have some good code to control user defined sorting. Let me give
you an example of what I mean:

In my program the user can setup various lookup values (used for dropdowns
etc...). Sometimes they like to see those dropdowns in a certain order.
They might add (Blue, Green, Red) and like to see it as (Green, Red, Blue).

Normally these lookup tables are pretty simple (key, text value), so now I'm
adding a sort column and would like some nice code for arrows to move values
up or down in the sort order.

Don't explain conceptually how to approach this I'm only looking for code
examples or links to code examples.

Thanks in advance,
Mark

 

[Stefan Hoffmann]

hi Mark,

Don't explain conceptually how to approach this I'm only looking for code
examples or links to code examples.

Use Google or any other search engine.

<concept>
Clear the item list and use ComboBox.AddItem to add your sorted values
to the item list.
The better approach is adding a sorting column, e.g. Number(Long), to
your lookup table. Sort by that column in the RowSource using "Order By
[SortColumn]".
</concept>

lol.

mfG
--> stefan <--

 

[Mark Andrews]

I always try search engines before I post on this newsgroup, but when you
throw in the word sort you get a lot about basic sorting capabilities. This
is just something I know a bunch of people have written (a form when you
enter items and have up and down arrows to move them up or down the list) so
when you leave the form all items are entered and in the correct sort order.
Just trying to not recreate if possible.

Sorry, your answer did not help me out,
Mark

hi Mark,

Don't explain conceptually how to approach this I'm only looking for code
examples or links to code examples.

Use Google or any other search engine.

<concept>
Clear the item list and use ComboBox.AddItem to add your sorted values to
the item list.
The better approach is adding a sorting column, e.g. Number(Long), to your
lookup table. Sort by that column in the RowSource using "Order By
[SortColumn]".
</concept>

lol.

mfG
--> stefan <--

 

[Rick Brandt]

Anybody have some good code to control user defined sorting. Let me
give you an example of what I mean:

In my program the user can setup various lookup values (used for
dropdowns etc...). Sometimes they like to see those dropdowns in a
certain order. They might add (Blue, Green, Red) and like to see it as
(Green, Red, Blue).

Normally these lookup tables are pretty simple (key, text value), so now
I'm adding a sort column and would like some nice code for arrows to
move values up or down in the sort order.

Don't explain conceptually how to approach this I'm only looking for
code examples or links to code examples.

Sorry, but there will be no code examples readily available that will
just plug into how you have things set up. I can give you an example of
a generic method that "might" work for you with some tweaking,

To move an item one higher in the list. Let's say that the item I am
moving has a sort Value of 5. I have to first run an update query that
increases the Sort value of all rows that are one less than 5.

Dim sql as String
sql = "UPDATE TableName " & _
"SET SortValue = SortValue + 1 " & _\
"WHERE SortValue >= " Me.ListBoxName.Column(n) - 1

CurrentDB.Execute sql, dbFailOnError

The 'n' above is the column index of a hidden column in your ListBox that
contains the sort value for the selected row. These are zero-based so
Column(0) is the first column, Column(1) the second, etc..

This creates a Gap in the SortOrder in your lookup table because there is
no longer a SortValue of 4. You have to run another update to set the
desired row to 4.

sql = "UPDATE TableName " & _
"SET SortValue = " & Me.ListBoxName.Column(n) - 1 & " " & _
"WHERE SortValue = " & Me.ListBoxName.Column(n)

CurrentDB.Execute sql, dbFailOnError

Then you Requery the ListBox...

Me.ListBoxName.Requery

If all of that works then your item should move one position up in the
ListBox. A similar method should work to move a row one position down in
the list.

 

[Mark Andrews]

Thank you very much. That will help me out!

Mark