Why does the record number keep changing on existing documents?

[Guest]

I created a database in January and it has been working fine, until
yesterday. Now, my record numbers are all different from the auto-set number
field. I looked at it again today and the numbers are even off from
yesterday. Luckily the auto numbers are correct and "stable", but the record
numbers at the bottom (next to the arrows that navigate you through the form)
are HAYWIRE. The jump forward, backward, almost totally random. Is there a
way to fix it easily and is there something I can do to make it not do it
again? I'm 1143 entries into the data base with THOUSAND more to go........
Help!

 

[Guest]

The query that your form is based on sort your records by the auto number
that you say are still "stable". The record number shown in the record
selector is simply the number (count) of that record after/from the first
record so to "restore" the record number to what you need - look at the
"sorting" of the base query.

 

[John Vinson]

I created a database in January and it has been working fine, until
yesterday. Now, my record numbers are all different from the auto-set number
field. I looked at it again today and the numbers are even off from
yesterday. Luckily the auto numbers are correct and "stable", but the record
numbers at the bottom (next to the arrows that navigate you through the form)
are HAYWIRE. The jump forward, backward, almost totally random. Is there a
way to fix it easily and is there something I can do to make it not do it
again? I'm 1143 entries into the data base with THOUSAND more to go........
Help!

Autonumbers are NOT SEQUENCE NUMBERS.

There is *no* guarantee that an Autonumber sequence will be free of
gaps (in fact it's rather unusual if it is, since any record deletion,
or even hitting <Esc> after starting a record, loses an autonumber).

If there are discrepancies between the value of your Autonumber and
the navigation numbers at the bottom of the screen, that's normal and
expected behavior, and one of the reasons most developers keep
Autonumber values concealed from user view.

The numbers on the navigation pane do not correspond to "record
numbers" in the table; the table HAS no record numbers, and no defined
order. Perhaps you've used the Form Records menu option to sort the
records as displayed on the form? If so, the navigation pane will
reflect the new sort order.

John W. Vinson[MVP]

 

[Guest]

I don't think I was clear in my original question - please be patient, this
is my first Access database and am struggling with it. I created a database
form/table to keep a running inventory of my collection. In the table I have
a primary key - auto number and I also have a record bar in the navagation
menu. Until recently, both of these numbers matched. It is my understanding
that both sets up numbers update when I enter new items. I have not deleted
or altered either set up numbers or any records. I just keep adding more
entries. The primary key auto number has remained stable, however the
navigation bar number has not, so trying to navigate through the records is
impossible now. It does not appear that I can manually change the records
numbers and my guess is that my database is somehow currupt. I am trying to
get out of rebuilding it from scratch and looking for advise so that this
situation does not happen again, if I do have to rebuild it. Thank you in
advance for any advice you can give me.

 

[John Vinson]

I don't think I was clear in my original question - please be patient, this
is my first Access database and am struggling with it. I created a database
form/table to keep a running inventory of my collection. In the table I have
a primary key - auto number and I also have a record bar in the navagation
menu. Until recently, both of these numbers matched. It is my understanding
that both sets up numbers update when I enter new items.

Your understanding is incorrect. That's not how Autonumbers work.

An Autonumber has one purpose, and one purpose only: to provide a
static, unique identifier for a record. When an Autonumber is inserted
into a new record, it's there for good until you delete the record; it
will not change, no matter what is done to other records. There will
be a gap left if you delete a record - or even if you hit the Escape
key after starting a record.

An Autonumber *is not a record number* and should not be used as one;
in fact, most developers avoid exposing autonumbers to human view,
because they are meaningless in most situations. If you want a
sequential, gap-free number you must provide it yourself.

And think about it: if you had 2284 records in your table, and
realized that you must delete record 3, would you REALLY want 2281
records to change their primary identifier? Note that you'll need to
probably change it not only in your main Collection table, but also in
all related tables - AND every piece of paper and bit of human grey
matter where every one of those numbers might be residing!


John W. Vinson[MVP]

 

[David Cox]

An autonumber primary key is mainly for use by Access. In most applications
it will not be shown to the user.

Why do you want to use the record number on the navigation bar for
navigation? Its only reliable use is as an indication of how far you are
through the set of records returned by the query.

You should be using a query to select and sort the data for your form. It
can be in just the order to suit you, so you know which way to step through
the queried data on the form to find what you want. What order do you want
the data to be in?


"(e-mail address removed)"