auto numbering

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

Guest

i have a data base with 118 records using the primary key to control
numbering of each record?
when i go to enter a new record the numbering jumps to the next record as
235. how do i delete all the numbers in between .would appreciate help on
this regards dennis
 
i have a data base with 118 records using the primary key to control
numbering of each record?
when i go to enter a new record the numbering jumps to the next record as
235. how do i delete all the numbers in between .would appreciate help on
this regards dennis
Click to expand...

An autonumber has one purpose, and one purpose ONLY: to provide a meaningless
unique value.

Autonumbers will *always* have gaps. If gaps aren't acceptable, don't use an
autonumber; instead use a Long Integer and write VBA code to update it to

=NZ(DMax("[fieldname]", "[tablename]"))

in the Form's BeforeUpdate event.

An autonumber - either way you do it! - is *NOT A COUNT*. It's an identifier.
If you want to count records, do a Totals query and count records - if you
store the count in a field in the table, you're vulnerable to record
deletions. What do you do when you have 3198 records in the table and realize
that you need to delete record 2? Renumber 3195 of them? How about printouts,
peoples' minds, post-it notes...?

John W. Vinson [MVP]
 
Go into Design View for your table and click on the autonumber field and look
down at the Genral Tab. I'm guessing you'll see the New Values is set to
"random." If so, change it to "Increment." But be warned, autonumbers, even
set this way, will almost never be consecutive! If you delete a record its
autonumber will be gone forever. If you start to enter a new record then
don't save it, its autonumber is gone forever. Other things will affect th
eautonumber sequence. The simple fact is that they're not intended for
incremental numbering of records. If you want this, you have to set up your
numbering scheme. As someone here once said "Autonumbers are unfit for human
consumption!"

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
 
thanks john i now better understand the role of autonumbers but do i still
have to include the primary key which is an autonumber as well as a new
column named record No and use the vbo data you gave me .As you can tell i am
a new user of access. many thanks rgds dennis

John W. Vinson said:
i have a data base with 118 records using the primary key to control
numbering of each record?
when i go to enter a new record the numbering jumps to the next record as
235. how do i delete all the numbers in between .would appreciate help on
this regards dennis
Click to expand...

An autonumber has one purpose, and one purpose ONLY: to provide a meaningless
unique value.

Autonumbers will *always* have gaps. If gaps aren't acceptable, don't use an
autonumber; instead use a Long Integer and write VBA code to update it to

=NZ(DMax("[fieldname]", "[tablename]"))

in the Form's BeforeUpdate event.

An autonumber - either way you do it! - is *NOT A COUNT*. It's an identifier.
If you want to count records, do a Totals query and count records - if you
store the count in a field in the table, you're vulnerable to record
deletions. What do you do when you have 3198 records in the table and realize
that you need to delete record 2? Renumber 3195 of them? How about printouts,
peoples' minds, post-it notes...?

John W. Vinson [MVP]
Click to expand...
 
thanks john i now better understand the role of autonumbers but do i still
have to include the primary key which is an autonumber as well as a new
column named record No and use the vbo data you gave me .As you can tell i am
a new user of access. many thanks rgds dennis
Click to expand...

You certainly do not need two different numbers; a table can have one and only
one primary key. An Autonumber is one way to generate a unique identifier; the
code in the form's beforeinsert event is a different way to do so.

Again, though: whether you use an Autonumber or roll your own, *THIS IS NOT A
RECORD NUMBER*.

Think about it. You have 21,378 records in the table. You realize that
RecordNo 3 is *a duplicate*, *an error*, and that it must be deleted. You
delete the record.

You now have 21,375 records *with incorrect record numbers*.

Now what do you do?

An alternative is to never, under any circumstances, allow records to be
deleted. You can have a yes/no field "Valid" defaulting to True, and turn it
off when a record is found to be invalid; this will let you keep the "record
number" sequence intact.

John W. Vinson [MVP]
 
Back
Top