Creating a New Record and AutoNumbering Problems

[Douglas J. Steele]

That's the way Autonumbers are: if a number isn't used (because, say, you
decide not to save the record), the number is lost. However, if you're
trying to assign meaning to the value of the Autonumber, you shouldn't.
Autonumbers exist for one purpose only: to provide an (almost guaranteed)
unique value that can be used as a Primary Key. They fill that purpose
whether or not there are gaps. Seldom, if ever, should the value of the
Autonumber field be shown to your users.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I have a database which records businesses and the activities our managers

do with these businesses. I enter the data in this one form that contains
subforms....and whenever a new business comes up I need to enter it into the
database. The autonumbering system generates the next sequential ID number.

However, something happened recently where it skipped a number....so

instead of ID#3923 it went to #3924....I deleted the record and tried it
again, but then it went to #3925....it's like it won't "re-use" the number
of the deleted record...even though there is no data or record because the
record was deleted.....so it will say record #3923 on the bottom but it will
assign ID#3926 and up. I've deleted it and tried it again a few times and
now it's up to ID#3930. Does anyone know how to fix it so that ID#3923 will
be assigned like it should? I can't very well report: "We have a total of
3923 businesses in the database and ID#3930 is this....doesn't make sense.
Can anyone help me???? Thanks

 

[MK]

I have a database which records businesses and the activities our managers do with these businesses. I enter the data in this one form that contains subforms....and whenever a new business comes up I need to enter it into the database. The autonumbering system generates the next sequential ID number.

However, something happened recently where it skipped a number....so instead of ID#3923 it went to #3924....I deleted the record and tried it again, but then it went to #3925....it's like it won't "re-use" the number of the deleted record...even though there is no data or record because the record was deleted.....so it will say record #3923 on the bottom but it will assign ID#3926 and up. I've deleted it and tried it again a few times and now it's up to ID#3930. Does anyone know how to fix it so that ID#3923 will be assigned like it should? I can't very well report: "We have a total of 3923 businesses in the database and ID#3930 is this....doesn't make sense. Can anyone help me???? Thanks

 

[MK]

Thanks for your help. I also looked up previous posts and found people had similar problems with the autonumbers skipping....

But my Primary Key is the ID# which happens to be autonumbered. I have 3 regional managers that deal with these businesses all the time and whenever they have updates to those businesses, the way we communicate about those businesses is through their ID number....that's the only way I can keep track of quickly being able to find those businesses in the database. So the managers know what the ID# is for all their businesses.

Should the ID# column not be autonumbered? I figured this was the easiest way to just generate a new number....

 

[Douglas J. Steele]

It really depends on the nature of its usage.

If the value of the number is important, I'd probably not use an Autonumber.
If, for example, you're using it to create invoice numbers, and your
auditors want to know the details for every invoice, then an Autonumber is
probably a bad choice, since you have no way of knowing whether a missing
number is an invoice that really existed, but has been deleted from the
system, or if it's just a skipped autonumber.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thanks for your help. I also looked up previous posts and found people had

similar problems with the autonumbers skipping....

But my Primary Key is the ID# which happens to be autonumbered. I have 3

regional managers that deal with these businesses all the time and whenever
they have updates to those businesses, the way we communicate about those
businesses is through their ID number....that's the only way I can keep
track of quickly being able to find those businesses in the database. So the
managers know what the ID# is for all their businesses.

Should the ID# column not be autonumbered? I figured this was the easiest

way to just generate a new number....