auto number index snafu

[keepout]

I have a 2003 DB I've been adding to for 2 years. It's at 423 records now.
I export to a PDF for simpler searching and viewing.

Using the search the other day, I went to grab DVD 186, on carousel tower2.
And what came up didn't match the record.

The index was totally screwed.

I went thru all 423 records and created a numeric index by hand. The auto
number field is useless.
But I would still prefer an auto number field.

Trouble is when I create the auto number field, it's still off the actual
numbers of the records..
ie: 99-100 is actually 99-186,187, 188, 189 100-385, 386, 387 etc..
old auto number numbers are scattered everywhere and have no relation to the
actual record.

Luckily the other [text stuff] in each record is correct.

How do I recreate the auto number, and make it match the actual number ofeach
record ?

 

[Douglas J. Steele]

Autonumbers are not guaranteed not to have gaps. Realistically, the only
purpose for an Autonumber field is to provide a (practically guaranteed)
unique value that can be used as a primary key. Having gaps in the number
doesn't impact that ability.

If the value of the field is important, then an Autonumber field is likely
the wrong answer. "Roll your own" ID number, putting code in the form's
BeforeInsert event to determine the next number to use.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a 2003 DB I've been adding to for 2 years. It's at 423 records now.
I export to a PDF for simpler searching and viewing.

Using the search the other day, I went to grab DVD 186, on carousel tower 2.
And what came up didn't match the record.

The index was totally screwed.

I went thru all 423 records and created a numeric index by hand. The auto
number field is useless.
But I would still prefer an auto number field.

Trouble is when I create the auto number field, it's still off the actual
numbers of the records..
ie: 99-100 is actually 99-186,187, 188, 189 100-385, 386, 387 etc..
old auto number numbers are scattered everywhere and have no relation to the
actual record.

Luckily the other [text stuff] in each record is correct.

How do I recreate the auto number, and make it match the actual number of
each
record ?

 

[Golfinray]

Start by compacting and repairing the database. If you have not done that in
2 years there is all likelyhood your table is corrupted. Then see if it
repairs your autonumber. If not let the table wizard analyze and see what it
finds.

 

[John W. Vinson]

How do I recreate the auto number, and make it match the actual number of each
record ?

You don't.

Think about it. Suppose you had 33156 records in your table. You find that
record number 3 was a test record that never should have been left in the
table and needs to be deleted.

Do you want to renumber 33152 records in this table, and in all the related
tables, and on all the printouts that have ever been generated, and in all the
minds that are retaining them?

An autonumber has ONLY one purpose: to provide a meaningless unique key. It's
not a count; if you want to count records, do a Totals query and count
records. It's not a "record number"; Access tables are unordered "bags" of
data and don't HAVE record numbers.

If you want a sequential, gapless number, use a Long Integer field (not an
autonumber); assign new values using VBA code in your data entry form; and
come up with some satisfactory scheme for handling deleted records.

 

[keepout]

Autonumbers are not guaranteed not to have gaps. Realistically, the only
purpose for an Autonumber field is to provide a (practically guaranteed)
unique value that can be used as a primary key. Having gaps in the number
doesn't impact that ability.

If the value of the field is important, then an Autonumber field is likely
the wrong answer. "Roll your own" ID number, putting code in the form's
BeforeInsert event to determine the next number to use.

it's a DVD database. Until the other day the DVDID field [auto number] was
correct. And it was the number of each DVD. The DVD's have more than single
movies on each DVD so alphabetical organization isn't useful. The reason for a
searchable PDF.
I've since deleted the auto number field. and tried several times to re-install
an auto number field, but at record 100, it loses integrity.
I don't understand what the auto number field is sorting on. It would be good
if it matched my new DVDID field.
I don't know what you're talking about "Roll your own" ID number, puttingcode
in the form's BeforeInsert event to determine the next number to use.'
It would make things simpler to have a number created.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a 2003 DB I've been adding to for 2 years. It's at 423 records now.
I export to a PDF for simpler searching and viewing.

Using the search the other day, I went to grab DVD 186, on carousel tower 2.
And what came up didn't match the record.

The index was totally screwed.

I went thru all 423 records and created a numeric index by hand. The auto
number field is useless.
But I would still prefer an auto number field.

Trouble is when I create the auto number field, it's still off the actual
numbers of the records..
ie: 99-100 is actually 99-186,187, 188, 189 100-385, 386, 387 etc..
old auto number numbers are scattered everywhere and have no relation tothe
actual record.

Luckily the other [text stuff] in each record is correct.

How do I recreate the auto number, and make it match the actual number of
each
record ?

 

[Douglas J. Steele]

One approach is to put code into the form's BeforeUpdate event to use DMax
to determine the largest current value for the Id, and increment it.
Something like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!Id = Nz(DMax("Id", "DVDTable"), 0) + 1

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

it's a DVD database. Until the other day the DVDID field [auto number] was
correct. And it was the number of each DVD. The DVD's have more than single
movies on each DVD so alphabetical organization isn't useful. The reason
for a
searchable PDF.
I've since deleted the auto number field. and tried several times to
re-install
an auto number field, but at record 100, it loses integrity.
I don't understand what the auto number field is sorting on. It would be
good
if it matched my new DVDID field.
I don't know what you're talking about "Roll your own" ID number, putting
code
in the form's BeforeInsert event to determine the next number to use.'
It would make things simpler to have a number created.

Autonumbers are not guaranteed not to have gaps. Realistically, the only
purpose for an Autonumber field is to provide a (practically guaranteed)
unique value that can be used as a primary key. Having gaps in the number
doesn't impact that ability.

If the value of the field is important, then an Autonumber field is likely
the wrong answer. "Roll your own" ID number, putting code in the form's
BeforeInsert event to determine the next number to use.

 

[keepout]

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!Id = Nz(DMax("Id", "DVDTable"), 0) + 1

End Sub

it's over my head. I cut&pasted it, but have no idea how to activate it.