autonumber

[sadie]

Why is it that when a record is deleted, the Autonumber doesn't reset to reflect an accurate number of entries in the database

 

[namliam]

Because the autonumber simply increments everytime you add a records

1,2,3,4,5

If you then delete 3,
Then add a new record (6)

your table will look like: 1,2,4,5,6 with 5 records in all...

Autonumber does not fill missing records...

Regards

 

[Mile-O]

• Access is a relational database thingy. Autonumbers used for primary keys can act as foreign keys in other tables. If you delete a record with an autonumber and that key is foreign in another table (you may not have Cascade Delete selected) then the records in the other table could connect (wrongly!) with the new autonumber if, as you suggest, it was to replace a missing number.
• The autonumber is used although it's value shouldn't matter to you. It's can be used for realting fields in different tables. I hope you are not using the autonumber as a visible ID within your database.
• It's just an incrementer of records; not a counter of recods.

What, seriously, does it matter?

 

[mission2java_78]

What, seriously, does it matter? [/B]

Its purdy though when its in order

If you want to reset autonumber back to 0, 1, 2, 3...
you have to delete all records and do a compact and repair on the database. (2000)/(XP)

But again your end user shouldnt care about a ID number...its
just a way to relate your records in your database.

 

[jdcallejas]

Ok, I understan that we should not use the autonumber as a PRIMARY KEY ON THE TABLE... I created a table that has a LeaseId and its the primary key on it.

Now this number should only increment when you create a record right? Well this is happening to me, I open the Lease Form, the Lease Number is the LeaseID. When I start a record and decide not to create it I just close the form.

I then goto the table and I can see that there is no record created but if I go back to the form the LeaseId does increment to the next number...

Why is this happening if there is no record created?

I am using Access07

Thanks
DaniBoy

 

[John Big Booty]

An autonumber is assigned as soon as you start to create a new record, whether or not the new record is saved or not is irrelevant. The number has been assigned and therefore can not be used again.

Also it is perfectly alright to use an autonumber as a primary key, an autonumber should not however be used for any other purpose.

 

[gemma-the-husky]

JD

Ok, I understan that we should not use the autonumber as a PRIMARY KEY ON THE TABLE... I created a table that has a LeaseId and its the primary key on it.

this isnt correct - of course you have an autonumber as a PK

the point is that in general an autonumber should not be used as a reference number etc etc in your system. many developers hide autonumbers, so users dont even know they are there. the ONLY purpose for an autonumber is to generate a unique (primary) key, that can be used to relate items in different tables

if you need a sequential reference then you need a different technique to generate the reference - in which case its a matter of taste whether you still have the autonumber, or instead use the manual sequential reference as your primary key - although note that it is beeter to use numbers as keys, rather than text strings

note that there is always an issue with maintaining sequences

even if you have manual control, and you have keys

17
18
19
20
21

etc, and delete row 19 from the table, you now have no row 19. But you have a real practical problem if you try to fill the gaps. thats why its better to flag unwanted rows as "dead", rather than delete them.

 

[jdcallejas]

Hello,

Thanks I knwo understand. Now I have another issue, I have created the main form and subform for the Leases, this is the setup or better yet I will upload the DB. Ok when I create a Lease I first fill out the Main Form, now it should not let me save anything on the main form if there is no record on the subform right? Well its doing this. I have created other databases before in Access2003 and dont remember this happening. Example I created a database for a little store I had in Nicaragua, I remember that I needed to have a product on the invoice for the form to close otherwise it would show a msg thet the primary key was null.. I also have lots of other questiosn on this DB that I am creating, if you love access and would like to help me one on one it would be great. We either do it here or here is my Aim=daniboy1969 and MSN=jdcallejas@hotmail.com

Thanks
daniboy

 

[ColinEssex]

I would suggest you start a new thread with new questions, as continuing a thread that was started 5 years ago can be misleading.

Col

 

[boblarson]

Ok, I understan that we should not use the autonumber as a PRIMARY KEY ON THE TABLE

Where'd you get THAT idea?? You are reversed you SHOULD use an Autonumber as a PRIMARY KEY but for NOTHING ELSE, unless you can live with gaps or negative numbers or jumps by the hundreds or thousands.

Why is this happening if there is no record created?

Access will assign an autonumber when you start to create a record and if the record is abandoned so is the autonumber, so it starts with the next one when creating the next record. It doesn't matter at all if you set things up right because your users will NEVER SEE the primary key autonumber. You can display other things for them but an autonumber should be considered a SYSTEM thing and the SYSTEM should manage them and use them but they are not meant "for human consumption."

 

[boblarson]

I would suggest you start a new thread with new questions, as continuing a thread that was started 5 years ago can be misleading.

Col

Yes, Colin is very correct. It is not good to append to a very old thread. Just create a new one and maybe REFERENCE the old one if you wish.

 

[jdcallejas]

Thanks Bob and Colin, I have modified the DB and its better the way you say.... I will post a new thread for the problem i see right now...

Thanks again,

Daniel