[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Autonumber Increment Event

Posted on 2005-05-13
Medium Priority
Last Modified: 2008-02-26
I have a form that uses an autonumber field.  When a new record is created, the form shows "autonumber" in the field.  Only when I start to enter data in another control on the form, does the autonumber field actually display a value.

I would like to use the autonumber field when I generate a new record to create a Ticket Number (the TicketNo field is a test field that is a concatenation of a couple of fields including the autonumber field).

Does anybody know how I would go about using the autonumber field immediately upon creating a new record in the form?  I have tried adding VB code to the OnClick event for a "New Record" command button to no avail.

Thanks in advance.
Question by:SquaredB
  • 2
LVL 51

Accepted Solution

Steve Bink earned 1000 total points
ID: 13998530
>>> I have a form that uses an autonumber field.  When a new record is created, the form shows
>>> "autonumber" in the field.  Only when I start to enter data in another control on the form,
>>> does the autonumber field actually display a value.

This behavior is by design.  The only way to reserve an autonumber value and populate the control is to begin the edit of a new record.  Just moving to a new record does not trigger the assignment.  I usually use something like an automated timestamp for the original creation of the record.  When the user clicks the button to add new, I put this line of code in the button's OnClick event:

Me!MyTimestampField = Now()

That provides for auditing of when a record was created, and fulfills the requirement of an edited field for the autonumber to populate.
LVL 51

Expert Comment

by:Steve Bink
ID: 13998546
FYI: once an autonumber value is reserved for a new record, it is totally unusable.  If you delete the new record, or otherwise dispose of it, that number will not be reused by Access.  It will not matter if you have saved the record or not...Access increments the seed at the same time it provides a specific value to a new record.

Author Comment

ID: 13998577
Thanks.  Exactly what I needed.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question