Autonumbering and cancel command

[Chris]

My table has a field called IDNum that is set to
Autonumber and Primary Key. In the data entry form,
whenever a user adds a new record, the IDNum updates to
the next sequential number.

That's find, but could someone explain to me what is
happening with the incrementings of the Autonumber field
when the following actions are done:

1. Open the form for additions. The IDNum increments to
50. Save and close the form. The information for IDNum 50
appears in the table.

2. Open the form again. The IDNum field increments to 51.
This time, the user clicks button cmdCancel. A look into
the table shows that there is NO record for IDNum 51.

3. Open the form again. The IDNum field has incremented to
52 - even though IDNum 51 doesn't appear in the table.

Why does this happen, and what can be done about it?

Here is the code for opening the form from the Main Menu:
DoCmd.OpenForm "Create Incident frm", acNormal, , ,
acFormAdd, , 1

Here's the code for saving the record and closing the form:
DoCmd.Close acForm, "Create Incident frm", acSaveYes

Here's the code for closing the form without saving the
record. Notice I've been playing with some options:
Option 1:
' DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
' DoCmd.DoMenuItem acFormBar, acEditMenu, 6, ,
acMenuVer70
DoCmd.Close acForm, "Create Incident frm", acSaveNo

Option 2:
Me.Undo
DoCmd.Close acForm, "Create Incident frm"

Many thanks to anyone who can advise how to stop
autonumber incrementing on cancel, and why it happens to
being with!

Chris

 

[Cheryl Fischer]

Chris,

The AutoNumber behavior you describe is "by design"; AutoNumbers are never
re-used, even if the new record is not saved. If there is significance to
the content of the current AutoNumber field or if you need
"consecutiveness", then you may need to investigate a Custom Numbering
System. You can do a Google Groups search in the microsoft.public.access.*
groups on that subject; there will be numerous suggestions.

 

[Chris]

Thank you, Cheryl.

-----Original Message-----
Chris,

The AutoNumber behavior you describe is "by design"; AutoNumbers are never
re-used, even if the new record is not saved. If there is significance to
the content of the current AutoNumber field or if you need
"consecutiveness", then you may need to investigate a Custom Numbering
System. You can do a Google Groups search in the microsoft.public.access.*
groups on that subject; there will be numerous suggestions.

--

Cheryl Fischer, MVP Microsoft Access






.