AUTONUMBER - HOW TO ELIMINATE deleted numbers

[jenny23]

Hello friends,
I created a form with AUTONUMBER for the first field. Every time user start filling out the 2nd field, the 1st field (autonumber) automatically increase by 1.
However, when user fill out from 2nd field, but not saved yet, then close out the form, the number still increases and saved in the table. How can I protect from that?
On my form, I have a submit button to save, but in this case, I didn't save a form yet, but the number still increases ....
Also, when I go to the table and delete 4 records (for example from number 6 to 10), then I go back the for to fill out, the autonumber doesn't start from 6, but from 11... is there anyway to protect from this?

Thanks.

 

[joeselch]

This question has been asked (and answered) many, many times on this forum -- you should try using the Search feature.

I believe you'll find the answer is: No; not really.

 

[Benjamin Bolduc]

Hi Jenny,

I found that when you need to manipulate information based on a consectutive numbering sequence, autonumbering is NOT the way to go. I only use autonumbers as primary keys when the actual number itself doesn't matter.

When I ran into a similar problem for the first time, I created a query based soley on the number field (In your case, your Autonumber field) and set it descending.

I placed a subform, based on this query, on my form. Then i added a line of code to the BeforeInsert event of the main form to take the value from the subform and add 1 to it. This becomes the next consectutive number.

This bypasses all the inflexiblity of an autonumber while still giving you an easy and reliable number sequence. Just remember to change your field to a 'Number' on your table.

I hope this helps and isn't too confusing,

Ben

 

[kousotsu]

73|-| |-|4xx0|2z \\/4y

If you absolutely have to have your numbers sequentially, you first need to build unbound forms and write your code so that updates don't happen if things are not as you want them... it's not an easy task most times. If you have records that you need to delete from the end of a sequence (as this method won't work for numbers you take out of the middle of a table), you can delete them, copy your table, paste it back with a one-offed name (data and structure paste), rename it to the original table name, and then reattach it in the relationships window. Just make sure you don't forget any relationships!! ^_^

Just a Q|_|1(|<&|)1|27y |-|4xxj0ß though... I use it for testing when I'm building new forms or playing around and don't feel like inputting the data all over again.