autonumber and undo

[SusanV]

If a user starts a new record and then realizes the record already exists or
gets interrupted or whatever and clicks on an "Undo" button (me.undo) or
click the "exit without saving" button (DoCmd.Close acForm,
"frmSelectCustomer", acSaveNo), the record is not saved but the next new
record entered is no longer sequential in the autonumber field.

Why is this? Not a big deal, mainly curiosity (I use DMax for required
sequential fields).

 

[Guest]

...because access thinks it's used that number (why it does this is beyond me)
if however you add/undo and then compact the database, it starts again from
the correct value.

....not that you can really go around compacting the database every 30 seconds.

 

[SusanV]

Thanks Jack - as they say, it's not a bug it's a feature!!

<grin>

 

[Rick Brandt]

..because access thinks it's used that number (why it does this is
beyond me) if however you add/undo and then compact the database, it
starts again from the correct value.

It's because in a multi-user environment it is the best way to avoid
collissions on the same number. AutoNumber is assigned upon dirtying the
record and an indefinite amount of time can pass before the record is
actually saved to the table. As soon as Jet provides the next value it must
assume that it wil be used and since the specific value of an AutoNumber is
not supposed to matter (only its uniqueness) it should not be a problem for
the app.

Also the compact thing no longer works in the newer versions. AutoNumber is
now only reset if the table is completely empty.

 

[SusanV]

Thanks Rick, that makes sense.

Susan

It's because in a multi-user environment it is the best way to avoid
collissions on the same number. AutoNumber is assigned upon dirtying the
record and an indefinite amount of time can pass before the record is
actually saved to the table. As soon as Jet provides the next value it
must
assume that it wil be used and since the specific value of an AutoNumber
is
not supposed to matter (only its uniqueness) it should not be a problem
for
the app.

Also the compact thing no longer works in the newer versions. AutoNumber
is
now only reset if the table is completely empty.