Access auto number

[ktritten]

I have a problem where 4 records were deleted and now the
auto assign number does not match the record # (example:
record 634 of 640 when ideally it should be the same).

Is there a way to fix this?

Thanks in advance!
Karen

 

[Rick B]

There is nothing to "fix". An autonumber and a record number are two
different things. The records are stored in the tables in no particular
order. Deleting records will leave gaps in autonumbers.

You should not care what the record number is.

Also, the autonumber (as posted many many many times in these newsgroups) is
simply a unique record identifier and should not be used if you need a
meaningful number. It is just an internal key number.

Read previous posts for more details.

Rick B

 

[Rick Brandt]

I have a problem where 4 records were deleted and now the
auto assign number does not match the record # (example:
record 634 of 640 when ideally it should be the same).

Is there a way to fix this?

Thanks in advance!
Karen

AutoNumber is not designed to match the record count. It is only intended
to give you a unique value per record. Any time you care about the value in
any way other than uniqueness then AutoNumber should not be used. There are
numerous (perfectly normal) actions that will cause an AutoNumber to have
gaps in the sequence

 

[Dirk Goldgar]

I have a problem where 4 records were deleted and now the
auto assign number does not match the record # (example:
record 634 of 640 when ideally it should be the same).

Is there a way to fix this?

It's not broken. "Record numbers" are meaningless in Access, and
autonumbers exist for the sole purpose of generating a unique key for a
record. The value of an autonumber should never be used to mean
anything. Autonumbers, by their very nature, will develop gaps in the
sequence whenever a record is deleted, or when a user begins to create a
new record and then decides not to save it.

Most often, autonumbers shouldn't be shown to the users at all (though I
have been known to do it, just to give the users a handle to identify a
record that wasn't otherwise easy to single out). But if you care
whether the autonumbers have gaps, then you are already assigning them
more meaning than you ought.

In a relational database, records are can be presented in any requested
sequence, but have no inherent sequence of their own; hence, record
numbers are meaningless. If your application requires a sequential
number for external reasons -- an invoice number, for example -- then
you should use some other mechanism to generate those numbers so that no
gaps will develop.

 

[Guest]

I posted this question though about a year ago and an
individual gave me instructions on how to fix this
problem.

 

[Rick B]

ummmm. Again, there is nothing to "FIX". If you got a response in the past
that you feel "fixes" this issue, please share it with us, though. I think
we'd all be interested to see it.



Rick B

 

[Dirk Goldgar]

I posted this question though about a year ago and an
individual gave me instructions on how to fix this
problem.

It's possible to reset the autonumbers so as to start numbering again at
the next value after the highest one currently in the table, but it's a
pointless exercise, since it's not possible to stop the gaps from
reappearing in normal use -- at least, not unless you (a) prevent users
from adding records directly via bound forms or datasheets, and (b)
don't permit records to be deleted.

There have been bugs in the Jet database engine where autonumbers jumped
wildly. If I'd ever experienced that, I admit I'd probably fix the
autonumbers, after applying the patch to Jet. But that's only because
of concerns about the bug.

The only reason I can think of for trying to reset autonumbers, in the
absence of a bug, is when you're distributing an empty database and a
desire for "tidiness" makes you want to reset all autonumber fields to
zero. Even then, it's just an emotional impulse (which I acknowledge);
not a practical consideration.