strange autonumber behavior

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,
I have a form that's bound to a table. The PK of the table is an autonumber
field.
after using the form in my organization, i enter the table and see 'holes'
in the autonumber field. there are numeruos entries of lost continuity in the
numbers, sometimes even big gaps (5230,5231,5232,5243!!!).
Any thoughs on that matter?. I dont know how to trigger the problem
Thanks in advance
 
What you describe is normal behaviour. Whenever you create a record (or
begin to create one - even if you later cancel the operation), the
Autonumber field increments by one. When you delete a record, it's
Autonumber value isn't recycled, so you will always end up with gaps -
sometimes big ones.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Thanks Graham but the problem is that neither the user nor me (as the DBA)
deleted any row....(unless its an inside job by one of the smarter users) i
tough maybe there is a problem with the autonum.
If it is an inside job do you have any ideas on how to trace them?
Thanks again
 
<<...do you have any ideas on how to trace them?>>
No, there's no way to accurately track them. You shouldn't be using them for
anything other than primary keys anyway. The user should never interact with
(or even see) an Autonumber field, because the Autonumber field was put
there there for the exclusive use of the system, so it should not make a
difference if there are gaps or not.

If you're currently showing the Autonumber to the user, I'd seriously
reconsider the wisdom of that decision, and redesign the system, because
there's absolutely nothing you can do about how Autonumber works.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
If you are using a form for data entry, and if you go to a new record, then
back up to the previous record, then go to the new record again, the new
record will skip the autonumber that would have been assigned had you
entered a record the first time. I think this holds true if you have code
that automatically puts the user at a new record when opening the database,
and the user then navigates to an existing record, but you would have to
test to be sure.
If you need a sequential number, check this link (watch for line wrapping):
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
There are other approaches if you need to combine, say, year and a sequence
number. If you need a sequential number (invoice number, for instance)
beyond what Roger's sample produces, post the details. Somebody will be
able to help.
 
Back
Top