Insert a Number, and renumber sequentially

[palnud]

I have a form that has a field called Show Order. This is the order in which the entries in a sports event are going to perform. An entrant may request that they perform in position 5, and there may be 20 entries already listed for the event. What would be the best way to insert this entry as number 5 and renumber the current entry number 5 to 6, and 6 to 7, etc. through the rest of the records? Thanks.

 

[thornmastr]

You can do this if you have to but with a little planning you don’t have to. Include another field which you might call a position field. Define it as a decimal field. When a record is entered this number is the same as show order field. Say 5 and 5.

Now the person in position 5 needs to be pushd down bwtween a new entry to 5. So you create a new record having 5 in both fields. The person who was in position 5 now gets his positioning field to say 5.001. this gives you up to 100 people in what were positions 5 and 6. you can check with a query to determine the highest current number between two numbers and bump it up by 0.001,

Then, when you want to see an ordered placement, sort you record set on placement order.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com