Sequencing Steps

[adamquestion]

Hello,

I was not sure what to search for regarding this question. I am using Access 97 and I have a form with a header that contains information for a project. Beneath that header is a subform running a continuous form of steps for that project. I currently have a field in which the user inputs a # for that step and the form is sorted by that number to order the steps.

Say that I have entered 45 steps and I realized that the 46th step and the 47th step should actually be step 1 and 4. Currently I have to renumber each step to accommodate this change. I would like to know if there is any way to key steps 1 and 2 into the 46th and 47th steps and have every step renumber and sort automatically. Changing current steps 46 to 1, 1 to 2, 2 to 3, 47 to 4, and 3 to 5 and so on.... Like when I change the priority of a movie on Netflix.

Thank You

 

[jzwp22]

In your application, is the number you use for sequencing an autonumber field? If it is, I would recommend creating a new field called seqno. You can approach this several different ways, but I have always separated my sequence numbers so that I have some room between them. Let's say you have a sequence of steps for project number 111.

seqno ProcedureName ProjNo
10 Let cat out 111
20 Let cat in 111
30 Feed cat 111
.
.
.
200 Go to bed 111

Now let's say you should really feed the cat before you let it out. You can just use an update query to change the seqno from 30 to 9. You will not have to renumber the entire list. The update query would go something like this:

UPDATE yourtablename SET seqno=9 WHERE seqno=30 and ProjNumber=111