Inserting between old records

[Luongo1]

Hi,

Is it possible to insert records between older ones while using an autonumber field? I'm using an auto-numbered log number for each record. However, I need to also be able to go back and insert records at certain points. Would it be possible to do this and have the auto-number field update/re-generate itself?

Sorry I couldn't explain it too clearly... any suggestions you have would be great...

 

[RuralGuy]

You are using an AutoNumber field in a manner for which it was never designed. If you need continuous numbers then you will need to do them yourself.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[JoeAtWork]

Sometimes I wonder if Tek-Tips should make a new "Access Autonumber" forum, as variations of this question come up practically every day.

If you search this forum you should eventually find code for creating your own auto-numbering function (there is a FAQ somewhere).

But one question is, if somebody deletes a record, will you need to decrease the number field by one for all records that follow the deleted one?

 

[Luongo1]

Hi Joe, really the most important thing for me is to be able to insert records at any point. I'm willing to have the log numbers remain the same even if this causes them to be out of order. Would this make things significantly easier?

 

[JoeAtWork]

Its neither easier or harder, just a little different. You would first scan through the records looking for gaps, fill it in if you find one, otherwise use the next consecutive number. Whether you are filling in a gap or adding to the end, the trick is to reserve that number. Because between the time you have determined the number and when you actually add the record, another user may have inserted a record with the same number.

There is a FAQ written somewhere demonstrating a technique that ensures two users don't get the same number. It is for a custom autonumber generator, but it should work equally as well for filling in gaps.

 

[lespaul]

The order that records are inserted into the table should be meaningless. The autonumber as a primary key should be meaningless. If you have assigned some meaning to the autonumber you are using it incorrectly.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins