Autonumber Reset problems

S

Secret Squirrel

I am trying to reset my autonumber field back to a certain number but I can't
seem to get it to work. When I reset it back using a computer with Win98 and
Access 2000 it seems to work fine. But when I try and reset it back using
WinXP and Access 2000 it won't reset. Does anyone know of any problems of
this kind?
 
S

Steve Schapel

Secret Squirrel,

What approach are you using to "reset" the Autonumber? In earlier
versions of Access, compacting the database would reset an Autonumber's
seed back to the highest number used +1, but in later versions this does
not happen. However, in tables where *all* the records have been
deleted, the Autonumber seed should be returned to 0 when the database
is compacted.
 
S

Secret Squirrel

Yep, tried that too. It seems kind of strange to me that it won't work on XP.
 
O

Ofer

Did you try compact and repair on the mdb where the table is located in?
And not on the mdb that has a link to this table
 
S

Secret Squirrel

I am using the procedure where you run an append query to reset it to a value
that you want. It seems to work ok on my pc at home but it won't work on my
pc at work. I'm thinking there is something wrong with the install of office
that may be causing this. What do you think?
 
S

Steve Schapel

Secret Squirrel,

As far as I know, you can't use that approoach to set the Autonumber
*back* to your nominated value. If you use an Append Query to add a
record with an Autonumber value *less than* the highest one that has
been entered, well the seed for the next record entered will remain to
be 1 greater than the highest value entered. As far as I know, this
would apply in all versions of Access. That method is sometimes used to
set the seed for the Autonumber *forward* to a specified number. If you
want it to go back, I think you have no option but to delete *all*
records, and then compact the database.
 
S

Secret Squirrel

Steve,
Say for example a user enters a new record but then realizes it was an error
and it needs to be deleted. If I delete that record how do I reset the
autonumber field to select the next number in sequence? Since a new record
was created and then deleted the system will still go to the next number
after the one that was deleted. I've been able to reset this in the past on
other databases using the append query function. But for some reason it
won't work on my current database. But when I try this at home it works fine.
Doesn't that sound like it could be a problem with the office install?
 
J

John Vinson

Say for example a user enters a new record but then realizes it was an error
and it needs to be deleted. If I delete that record how do I reset the
autonumber field to select the next number in sequence?
Click to expand...

That's a misuse of autonumbers. The user doesn't even need to delete
the record - they could hit the <Esc> key twice after ONE SINGLE
KEYSTROKE in the new record, and the autonumber will be used up. It's
all but impossible to prevent gaps.

I'd suggest using a Long Integer field with a Custom Counter - which
(in a system where it's unlikely for multiple users to simultaneously
enter new records) could be as simple as code in the Form's
BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = DMax("[ID]", "[tablename]") + 1
End Sub

John W. Vinson[MVP]
 
Top