Create Sort Order Customization Thingy

[Carouser]

I am inquiring whether it is possible to to create a sort ordering procedure like you find when you have a group of records, select any one record and you can use the up and down keys to re-order that record moving to any position in the hierarchy from first through last position. I would like to create a presenter ordering system, i,e the order which presenters will present in a session. I'm not sure what this function is called and so don't know what to look for, or if it is even possible with VB.

[June7]

Sort has to be done on data in a field. You would have to assign a value (number, alpha, or date/time) to each record that would determine the hierarchy of order.

[Carouser]

I understand that I would have to create an extra field in the appropriate table, I just do not know the logic behind how it would work if I wanted to re-order any of the fields especially just swapping the order without having to manually remove the numbers and re-insert in the order required. . The closest I have found is the "Rank function", though it does not give me the ranking gui that I was wanting.

[orange]

Further to June7's comment, if you have some sort order that doesn't follow Name or Amount order etc, and you can define it, you can add another field in a table eg MySortField
and make the order whatever you want/need. Then use that fields values to present/sort the data according to your field/values.

I have used such a field when we had bilingual data, and wanted the values in a "agreed upon" sort order .

[June7]

The values in the 'hierarchy' field would have to be edited, manually or with code.

EDIT (inspired by Paul's post following this one): A SQL UPDATE action could modify all the other records to add or subtract 1 from the hierarchy value, assuming a number type, depending on direction of move. The UPDATE would need criteria to exclude the record being 'moved' and possibly also criteria limiting to a particular group of records. I helped another poster with this long time ago, wish I could find it.

Google: access vba move record up down
here is one
http://www.accessmonster.com/Uwe/For...-within-a-form

[pbaldy]

If the numbers are contiguous, you can simply add or subtract 1 from the record you're on:

Me.Whatever = Me.Whatever + 1

and run an update query to add or subtract 1 from the appropriate record (the record with the value 1 higher or lower than the record you're on). You'd want to do that first so you don't reset the record you're on.