changing duplicates

[Ethereal]

OK, I have a table which has the following fields of relevence to my issure. SamID, SamSeqNo. SamID is allowed to have duplicates, as well as sam seqNo BUT the combination of the 2 is unique
1|1
1|2
1|3
2|1
2|2
2|3
3|1
4|1 Etc, ETC...

When a user enters something like 1|2 into that table, I want to make the table looklikt this
1|1
1|2 <--newly entered
1|3<--- old 1|2
1|4 < --- old 1|3
2|1
2|2 etc etc

i have two questions.

1) how do i implement the Dlookup function to detect duplicate records because at some pt between 1|1,1|2,1|3,1|4 it will look like 1|1,1|2,1|2,1|3

2) how to i call up a record and change one of its values ? to make the old 1|2 ===> 1|3 ? I am assuming this can all be done in vba. Or maybe a query ? Plz help!

 

[FoFa]

Seems a little squirly to me, but if you must. Create a function that uses the primary number (the first one) and then finds th ehighest SEQ number and bumps them all up by 1 working down. Then insert your new number 1.
What happens if they run the function and then backout of the insert (so basically you left with a hole).

 

[Ethereal]

Well it only runs this if the user clicks "Add" so once they click that there is no backing out. What i really need to know is "how" to do this, i have thought of the logic behind it, but lack the syntax to implement anything