# Access 2007: Help with creating custom autonumber field



## mkenal (Apr 14, 2010)

Hello all,

I am a not a novice computer user by any means, or even a novice MS Office user. I am, however, brand new to Access.

We are currently in the process of creating a database with which to track complaints that we have received, so that we can ensure they are processed in a timely manner with all the appropriate steps taken. We would like to have a unique case number assigned to each complaint, automatically populated when a new complaint is entered into the database. I've created and populated the database from the Excel spreadsheet that we had been using to track these complaints.

The problem I'm having however is creating the autonumber in the "case" field that we want! We want our complaints to be auto-populated with a case number "XX-YYYY" where XX is the last two digits of the year in which the complaint was entered and YYYY is an autonumber, starting with 0001 and going up from there.

Can anyone help me with how I can make this populate correctly? I tried using the following in the Format mask in design view, based on the in-program help:

"10-"****

with the thinking that if necessary I can create a database file for each calendar year (useful for archiving purposes at least). However, ~every~ entry shows up as "10-" (with nothing after the hyphen) until I click on it (at which time a seemingly random, but sequential, 8-digit number shows up, starting with "69313633").

Please help!!


----------



## karlhaywood (Jan 17, 2010)

Hi MKENAL try this link below
http://www.ehow.com/how_2020475_field-automatic-numbers.html

Let me know how you get on.


----------



## mkenal (Apr 14, 2010)

Unfortunately that doesn't tell me anything new. I've already got the field type set to "autonumber" and "incremental," which is why it's populating that 8-digit string incrementally (I just wish I knew why it decided to use that string?? I have nothing similar anywhere in my database!)

It's not how to make it autonumber that I'm having so much trouble with, rather it's how to make it format correctly and start with the correct number. I want it to be "10-0001," "10-0002," etc., not the current "69313633," "69313634," etc. (with "10-" displayed until I click on it - no matter what the actual atuo-number is).

Thanks for the attempt though!


----------



## OBP (Mar 8, 2005)

mkenal, welcome to the forum.
There are 2 ways to do this, one by adding an extra field to your Table and the other by using a VBA Recordset.
the first way means adding a new field to contain your Unique key. In the first field on your form where you actually enter some data (i.e. not the current autonumber field) you add a line of VBA code to the Field's After Update Event, like this
me.newIDfield = format(Date(), "YY") & " -" & format(me.currentautonumberidfield, "0000")
where newIDfield is your new ID field, currentautonumberidfield is your current ID field less any formatting.

The only problem with this method is if you cancel an Entry after starting it you will have a Gap in your Series, but if that is not important it will work well.


----------



## mkenal (Apr 14, 2010)

Okay thanks. I actually started from scratch and recreated the database (without importing the data from Excel yet), and now the autonumber is formatted and working properly. I am having some new problems relating to subforms and table relationships, but I'll address those in a new post if needed.

Final question regarding autonumbers: is there a way to format it so that the first two digits change based on the date? The example I gave above for how we're numbering our cases, "10-0001" etc., is based off what case in what year (we're in 2010 so all the cases this year start with 10). How can I format it so that when we get to next year, it changes to "11-" but starts over again at "0001?" Or is there a way? I need to be able to track unresolved complaints from the year before so I'm trying to avoid creating separate databases (and thus "premature" archiving) for each year if at all possible.

Thanks again everyone for your help!


----------



## mkenal (Apr 14, 2010)

OBP said:


> mkenal, welcome to the forum.
> There are 2 ways to do this, one by adding an extra field to your Table and the other by using a VBA Recordset.
> the first way means adding a new field to contain your Unique key. In the first field on your form where you actually enter some data (i.e. not the current autonumber field) you add a line of VBA code to the Field's After Update Event, like this
> me.newIDfield = format(Date(), "YY") & " -" & format(me.currentautonumberidfield, "0000")
> ...


Unfortunately I know not the first thing about VBA so I doubt I would be able to implement that.


----------



## OBP (Mar 8, 2005)

I can talk you through Implementing it, as it does exactly what you want.


----------

