Increment number

[Bob]

I have a database, that has an autonumber assigned, as is required by
Access. However, I want a field that says record Number. I have manually
typed the number to match the autonumber generated by Access. I sometimes
filter the form's output by another checked field, and the record numbers
get scrambled. How can I keep the record number matched with the autonumber
field, after using a filtered form? Autonumbered record and record should
both stay consistent with each other.
Thanks in advance.
Bob

 

[John Vinson]

I have a database, that has an autonumber assigned, as is required by
Access.

Ummm... *suggested*, perhaps a little too strongly; autonumbers are
certainly NOT required in any Access table.

However, I want a field that says record Number. I have manually
typed the number to match the autonumber generated by Access. I sometimes
filter the form's output by another checked field, and the record numbers
get scrambled. How can I keep the record number matched with the autonumber
field, after using a filtered form? Autonumbered record and record should
both stay consistent with each other.

An Autonumber is not intended to be, and WILL NOT WORK, as a "record
number". If you delete a record, that autonumber will be left missing
and will not be reused. If you create a Query sorting the records by
some other field, the autonumber value in the record for "Zzyxy" will
remain with the record for "Zzyxy", even if that record is now last in
the sort order.

If you want a volatile record number that renumbers whenever you add
or delete or resort data, don't store it in the table *at all*. In
practice such numbers aren't really very useful, since they have no
permanent connection to the data in the record; you can get line
numbers easily on a Report by using a textbox with a control source of
=1 and setting its Running Sum property to Over All. On a Form, you
can use a calculated field which will depend on the structure of your
table.

John W. Vinson[MVP]

 

[J. Goddard]

Hi -

MS Access has no requirement that a table have an autonumber field. If
you want to have your own field (Record Number) as the unique
identifier, no problem.

In your case, though, why not have a field called Record_Number, and
make it autonumber type? It amounts to the same thing.

For your forms, one way to prevent the records from being scrambled (I
assume by that you mean not in numerical order), base the form on a
query which sorts its records by the Record_Number field.

HTH

John