Access 2007: Help with creating custom autonumber field

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

mkenal

Thread Starter
Joined
Apr 14, 2010
Messages
6
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!!
 

mkenal

Thread Starter
Joined
Apr 14, 2010
Messages
6
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

Joined
Mar 8, 2005
Messages
19,895
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

Thread Starter
Joined
Apr 14, 2010
Messages
6
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

Thread Starter
Joined
Apr 14, 2010
Messages
6
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.
Unfortunately I know not the first thing about VBA so I doubt I would be able to implement that. :D
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I can talk you through Implementing it, as it does exactly what you want.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top