Full Version: Custom Autonumber without VBA
UtterAccess Forums > Microsoft® Access > Access Forms
Hi all,
Have a couple of questions about autonumbers.
1. I want to have my autonumber format look like this:
TDA001/08, TDA002/08, TDA003/08.... with 'TDA' being constant and the '/08' representing the current year. Is this possible without VBA? If so, can someone explain the format to me?
2. I've noticed that now that I've been playing with the autonumber that after I've created a record and deleted it, the numbering doesn't reset to 001, it continues to increase. Regardless of whether there are records in it or not. Is there a way to restart the numbering?
Thanks for any help!
Welcome to Utter Access!

Run "compact and repair"

Also see from the Forum: Access FAQAs:
Autonumbers - What They Are / Are Not

Edited by: HiTechCoach on Tue Aug 5 19:01:31 EDT 2008.
The built-in AutoNumber in Access is NOT intended for this kind of usage. As Boyd pointed out, you can sometimes cause Access to reset Autonumbers in a table, but that is really not going to be workable for what you describe here.

Here's a good basic discussion.

You'll need to do some maniplutation to get this format, and that probably does mean VBA.

However, there are some things you can do to minimize the work.

TDA is truly constant for ALL records?

In that case, "TDA" adds NOTHING to the meaning of any "Autonumber". It serves only as a visual cue for for users. Prepend it to every new number generated, if you must, but that's a simple update query:

Update tblMyTable Set tblMytable.AutonumberField = "TDA" & tblMytable.AutonumberField WHERE
LEFT (tblMytable.AutonumberField,3)<>"TDA"

will do the trick. (I didn't scan my SQL so they may be a typo in there.)

The two digits appended to the back of your number represent the calendar year? That's also a simple update query when you need to append it.

Update tblMyTable Set tblMytable.AutonumberField = tblMytable.AutonumberField & "/" & Format(Date(),"YY") WHERE
Instr(tblMytable.AutonumberField,"/")= 0

That SQL update assumes there will only be one instance of the forward slash / in your autonumber.
(I didn't scan my SQL so they may be a typo in there.)

That leaves the method by which you retrieve the NEXT 3 digit number in sequence for the middle part of your autonumber. That's where some VBA will come into play.

There have been a number of discussions here about methods for doing so.

I suggest you search the archives for posts on generating non-autonumbers. They'll generally involve the DMAX() function.

I'd find one for you, but I'm sure you can turn one up.


Edited by: GroverParkGeorge on Tue Aug 5 19:41:00 EDT 2008.
Thanks, very helpful. I'm giving all a bash now!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.