Autonumber increment technique (1 Viewer)

[usm01]

Question:
Wouldn't be better to store a copy of Autonumber in another table and when the form safely saves the record increment the number otherwise don't increment.
so the next autonumber should be taken from the other table.
In this way there will be no gaps between Autonumber.

how would it be done.

 

[Uncle Gizmo]

This question has been addressed many times within the forum. Follow this link to get you started:

http://www.google.co.uk/search?hl=en&as_q=&as_epq=increment+autonumber&as_oq=&as_eq=&num=10&lr=&as_filetype=&ft=i&as_sitesearch=http://www.access-programmers.co.uk/forums/&as_qdr=all&as_rights=&as_occt=any&cr=&as_nlo=&as_nhi=&safe=off

It is relatively simple to do this for a standalone one user database, however once you start using a database with backend tables, tables access to by more than one user, then it becomes more difficult.
Whichever way you go about it, I would recommend you continue to use the auto increment ID Field generated by MS Access for the identity of each record. If you need a consecutive numbering system, then consider some of the options described within this forum, but don’t use it for linking your records together.

 

[Banana]

Also, to point one more thing: It wouldn't still prevent the later deletion which would then create a gap in the sequence.

As Uncle Gizmo linked you, it should become apparent that autonumber was never intended to provide a sequential numbering; it was only intended to provide a unique identifier. If you must have a sequence with no gaps, then the links Uncle Gizmo gave you will give you some ideas.

 

[gemma-the-husky]

usm01

this is an equally valid way of setting recordid numbers, especially where the number may not be continuous, but is reset from time to time. you get control over the number sequence that isnt possible any other way

basically you have three approaches. it just depends which you want to use for the app.

1. have an autonumber, and let access allocate the numbers
2. use dmax to find the highest number used, and then increment
3. get the next number from an external table, and then increment

its all a matter of taste really. - note that you are calling the numbers autonumbers, but in the case of options 2/3 above, you would just use a long, not an autonumber - you are actively managing the number yourself

 

[usm01]

Thanks For The Info
I would Prefer no 2 or 3 to prevent gaps.

 

[genesis]

i would prefer #2

 

[boblarson]

And I would prefer the autonumber because I don't care what the record ID is. Access can manage those for me just fine. They are a SYSTEM used tool to manage the relationships between records. So who cares if it is incremented or random, positive or negative? Let the system handle that part.

Now, for numbers like INVOICE numbering and other OUTWARD FACING items, that is DIFFERENT and would require the use of #2 or #3. But for Primary Keys - Let Access manage them.