Access 2000 AutoNumber gaps when acUndo is issued

[Tony_VBACoder]

In Access 2000, I have form that is bound to a table whose Primary Key field
is an AutoNumber datatype. On my form, when the user is creating a NEW
record, I have a "Cancel" button whose click event runs the following VBA
code:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

My problem is, if the user starts entering data into some of the fields, and
then clicks the "Cancel" button, the Undo code prevents the insertion of this
record, but the AutoNumber has still be incremented, which leaves gaps in the
primary keys in my table. Is there anyway that I can get the AutoNumber to
not increment in value when the Undo occurs? I am looking for VBA code to do
this.

 

[Douglas J Steele]

That behaviour is by design. There's no way around it.

However, it shouldn't matter. The only purpose of an AutoNumber field is to
provide a (practically guaranteed) unique value that can be used as a
Primary Key. 3, 6, 10 serves that purpose just as well as 3, 4, 5.

If you're trying to attach some significance to the value of the AutoNumber
field, you probably shouldn't be using an AutoNumber. Generally, you
shouldn't even be showing the value of the field to your users.

 

[fredg]

In Access 2000, I have form that is bound to a table whose Primary Key field
is an AutoNumber datatype. On my form, when the user is creating a NEW
record, I have a "Cancel" button whose click event runs the following VBA
code:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

My problem is, if the user starts entering data into some of the fields, and
then clicks the "Cancel" button, the Undo code prevents the insertion of this
record, but the AutoNumber has still be incremented, which leaves gaps in the
primary keys in my table. Is there anyway that I can get the AutoNumber to
not increment in value when the Undo occurs? I am looking for VBA code to do
this.

Yes. That is correct. So what?
If you are using the AutoNumber field for any reason other than to
assure a unique Prime Key value, you are using it for the wrong
purpose. AutoNumbers are meant to be unique, not necessarily
incrementing without gaps. Indeed, if you were to later delete that
record you would have a gap.

If you need an incrementing number, use a different method.
The usual method is to set the Default Value property to:
DMax("[NumberFieldName]","TableName")+1

By the way, a simpler code to Undo would be would be:

DoCmd.RunCommand acCmdUndo

and it's certainly more understandable.

 

[Marshall Barton]

In Access 2000, I have form that is bound to a table whose Primary Key field
is an AutoNumber datatype. On my form, when the user is creating a NEW
record, I have a "Cancel" button whose click event runs the following VBA
code:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

My problem is, if the user starts entering data into some of the fields, and
then clicks the "Cancel" button, the Undo code prevents the insertion of this
record, but the AutoNumber has still be incremented, which leaves gaps in the
primary keys in my table. Is there anyway that I can get the AutoNumber to
not increment in value when the Undo occurs? I am looking for VBA code to do
this.

No, there is no way to avoid that.

You have a bigger problem if you really need the autonumbers
to be consecutive. An Autonumber primary key is not
guaranteed to be anything other than unique. As you have
seen, there will be gaps. More than that, they might go
negative or even become random. Furthermore, if you should
ever decide to use Replication, they will not even be
numbers.

A fundamental rule of Autonumber fields is that you should
never expose them to users that might try to invent some
kind of meaning for the autonumber values.