creating sequence in form_delete event

[radek225]

In my form (Datasheet), after delete some positions i need to recount my sequence e.g. if i have
1
2
3
4
after deleting position 2, query will change value bigger than 2 to achieve the correct sequence

from
1
3
4
to
1
2
3

so my query is

strSQL = "UPDATE tblProcesyZlecenia SET kolejnosc = kolejnosc - 1 WHERE kolejnosc in (select kolejnosc from tblProcesyZlecenia where id_kartatechnologiczna=" & Me.id_kartaTechnologiczna & " and kolejnosc>" & Me.kolejnosc & ")"
CurrentDb.Execute strSQL

And it works well!! But only if i select only one row. When i select two or more rows then Ms Access shows error that stops these changes because someone tried to change data in the same time. Is there any possibilities to solve this problem?

 

[Minty]

Normally having a "Fixed Sequence / Line number" is not a good thing in a database and indicates a poor design. Why do you need it? What are you trying to achieve?

 

[radek225]

Normally having a "Fixed Sequence / Line number" is not a good thing in a database and indicates a poor design. Why do you need it? What are you trying to achieve?

I need it to determine the sequence of production process in specific job. I have many Processes which could be used in different sequences according to the job. It is impossible to define all kind of jobs and sequence of process of these. I need to set every time

 

[gemma-the-husky]

how do you set it then? manually repositioning the tasks?

are the tasks in order without the renumbering? such as tasks 1,4,7,8,9 - that you want to renumber 1,2,3,4,5?

If so, why? you know the order of the tasks that make up this job. There is no need to renumber them.

 

[radek225]

how do you set it then? manually repositioning the tasks?

are the tasks in order without the renumbering? such as tasks 1,4,7,8,9 - that you want to renumber 1,2,3,4,5?

If so, why? you know the order of the tasks that make up this job. There is no need to renumber them.

My query (which i mentioned above) repositioning the tasks.

Sometimes I need to add a new task between the others while processing job. So i need to have sequence to avoid mess