automatic re-indexing for sorting purpose

[Mr.K]

I have a tbl ClassTitles. It has two fields: one is a custom ID the other is a className. I use the ID to properly sort the classes in order in which they need to be taken(this order is used later for certification requirments and many other quaries). However, I need to create a form where the user can add a new class and position it correctly among the other classNames. E.g.

ID | Class
1 | drawing level I
2 | drawing level II
3 | drawing technique

Now let's say they offer a new class Drawing level III. The user has to add it via a form so it gets written into the table as:

3 | Drawing level III
... and automatically push all the other classes below that, so now "drawing technique" would have index 4, etc..

thanks for any suggestions on how to tackle this

 

[Oldsoftboss]

As Pat suggests, keep another field to record the position.
I implemented code from here...
http://www.access-programmers.co.uk/forums/showthread.php?t=103456
Works well.

Dave

 

[Mr.K]

I'm sorry, I forgot to specify that my ID column is not an autonumber index - it is the index column on which I sort. As Pat writes above, it is a "manually" inserted number which tells the query how to sort the classes. I should have called it differently not to confuse you, e.g. SortID

My question was about the proccess of inserting of a new class. If I have a new class title which I want to insert at position 3, then how can I automate the form to move all the other class titles (after position 3) lower. How can I reset their SortID from 3 to 4, 4 to 5, 5 to 6, etc..

I'm trying to figure out the solution from the tread OldSoftBoss suggested, but a little more specific help would be appreciated.

(I thought my form would ask "where would you like to insert the record?" Choices:
at the beginning
at the end
or between -> and here 2 comboboxes with the source requested from the existing ListTable.
Any other prettier solutions?)

 

[Mr.K]

any VBA suggestions from the experts to my last post would be greatly appreciated.