Renumber records on insert, delete and allow only step change of 1

[martyn]

I have a form , each record has an autonumber but also a number I gave it '
SID '
The form runs of a query which sorts by SID field
eg

Auto No SID xxxxx xxxxx
1 4
2 5
3 7
4 8
etc

1st problem - how do I constrain the user to type in 6 instead 0f 7 above ie
there should only be allowed a step change of 1 from the last record to get :
Auto No SID xxxxx xxxxx
1 4
2 5
3 6
4 7



2nd problem - once I have acheived the above I may wish to insert a record
between 5 and 6 (Field SID) - I dont want 5.5 etc
This would need to be called 6 and the next records should automatically
pick this up and scroll down 1 number - Note I would have to scrioll down the
numbers first as it will not let me label a record 6 where one has been
already labeled 6?

3rd problem - Bit like the above but may have to delete a record, how do I
select it and delete and then the other records would move up one to where it
was originally positioned

 

[Rick B]

One post per question please.

http://www.mvps.org/access/netiquette.htm

 

[Tim Ferguson]

1st problem - how do I constrain the user to type in 6 instead 0f 7
above ie there should only be allowed a step change of 1 from the last

You don't: you allocate the number programmatically and take it out of
the users' hands. Try googling for 'MS Access Custom Autonumber' for
plenty of ways of doing this.

2nd problem - once I have acheived the above I may wish to insert a
record between 5 and 6 (Field SID) - I dont want 5.5 etc
This would need to be called 6 and the next records should
automatically pick this up and scroll down 1 number - Note I would
have to scrioll down the numbers first as it will not let me label a
record 6 where one has been already labeled 6?

Yuk! - this kind of thing has no place in a database - it's barely even
zeroth normal form!! The contents of the field should depend on the
primary key, the whole primary key, and nothing but the primary key.
Specifically, it should not depend on values in other unrelated records.

If you want to count rows, either (a) use a spreadsheet or (b) do it in a
query. For more information on (b) try googling for something like "row
number sql query".

3rd problem - Bit like the above but may have to delete a record, how
do I select it and delete and then the other records would move up one
to where it was originally positioned

See above.


Hope that helps


Tim F