How can I renumber an autonumber field that has missing numbers ?

  • Thread starter Thread starter Roger Ottaway
  • Start date Start date
R

Roger Ottaway

I have just upgraded from Access97 to Access2002. I have a database for all
of our office accounts. Each account gets an AccountID which is an
autonumber field, starting with 1 at the start of the year and ending up
around 500+by the end of the year.

Each month I print a report listing all accounts plus their ID number in
ID/date order. From time to time a data entry mistake is made and I end up
with a gap in the AccountID autonumbers. How can I renumber the AccountID
field entries so that I re-insert a missing number? I am sure I did this
with Access97, but can't figure out to do it with Access2002. Every time I
delete and then recreate the autonumber AccountID field the records seem to
resort themselves so that they are no longer in the correct date order !



thanks ... Roger
 
-----Original Message-----
I have just upgraded from Access97 to Access2002. I have a database for all
of our office accounts. Each account gets an AccountID which is an
autonumber field, starting with 1 at the start of the year and ending up
around 500+by the end of the year.

Each month I print a report listing all accounts plus their ID number in
ID/date order. From time to time a data entry mistake is made and I end up
with a gap in the AccountID autonumbers. How can I renumber the AccountID
field entries so that I re-insert a missing number? I am sure I did this
with Access97, but can't figure out to do it with Access2002. Every time I
delete and then recreate the autonumber AccountID field the records seem to
resort themselves so that they are no longer in the correct date order !



thanks ... Roger


.
open your table in design view and delete the autonumber
Click to expand...
field then save the table as a new table. Open your new
table in design view and set the date field as an index.
use this table to populate a new table with the autonumber
field and they should be in date order
 
I do not know if is the official way, but first take a
copy of the datasheet and play on this copy.
Do all the following actions individually and save after
each one:

make sure the data is in the order you want it.

go into design view

take out any primary keys.

Delete the auto number field

Put in new auto number field and its design details.

put back any primary keys

Hopefully you should now be back in correct order with no
gaps.

If so rename this to original name and delete bad sheet.

MAKE SURE EVERYTHING IS WORKING OK BEFORE DELETING
ORIGINAL BAD DATASHEET.

good luck

gill
 
I have just upgraded from Access97 to Access2002. I have a database for all
of our office accounts. Each account gets an AccountID which is an
autonumber field, starting with 1 at the start of the year and ending up
around 500+by the end of the year.

Each month I print a report listing all accounts plus their ID number in
ID/date order. From time to time a data entry mistake is made and I end up
with a gap in the AccountID autonumbers. How can I renumber the AccountID
field entries so that I re-insert a missing number? I am sure I did this
with Access97, but can't figure out to do it with Access2002. Every time I
delete and then recreate the autonumber AccountID field the records seem to
resort themselves so that they are no longer in the correct date order !
Click to expand...

An Autonumber has one purpose, and one purpose ONLY: to provide a
meaningless, almost-guaranteed unique key. It's not guaranteed to be
sequential, gapless, or much of anything else other than unique (and
there are bugs that violate even that constraint).

If you want a sequential AccountID, you'ld do much better to use a
Long Integer field and some VBA code to assign it. For instance, if
you're entering new accounts using a Form, you could put code in the
Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtAccountID = NZ(DMax("[AccountID]", "[tblAccounts]", _
"AcctYear = " & Year(Date))) + 1
End Sub

using of course your own table and field names.
 
Back
Top