Autonumber increased event if the new record is not added

[Marius from Rome]

I cannot exit from creating a new record without increasing the value of an
autonumber field.

The problem can be easily reproduced: I created a simple table with an
autonumber (primary key) and a text field.

I created a form to access the table. In the form I used the wizard to
create a button to create a new record and two different buttons let the
user undo the creation of the new record.

- The first button is associated to the "add new record" wizard option;
- The second button is associated to the "undo record" wizard option
- The third button is associated to the "delete record" wizard button

Let me describe what happens:

- The user opens the form
- The user selects the first button to add a new record
- A new record is created and the value of the autonumber field is, let's
say, 6
- The users presses either the seco nd or the third button to to undo the
operarion
- The user selects again the first button to add a new record
- A new record is created and the value of the autonumber field is 7

I made several attempts using different methods applied to either the fields
and the form to let the user exit without either adding the new record and
updating the value of the autonumber field, with no success.

How can I solve this issue?

Regards

Marius

 

[John Vinson]

I cannot exit from creating a new record without increasing the value of an
autonumber field.

Correct. That's how Autonumbers are designed to work. The ONLY
function of an autonumber is to provide an almost-guaranteed unique
key.

If you don't want gaps, don't use an Autonumber; use a Long Integer
field and maintain it yourself in VBA code. For instance, you can use
code in the BeforeInsert event of a form:

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