1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007: Help with creating custom autonumber field

Discussion in 'Business Applications' started by mkenal, Apr 14, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. mkenal

    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!!
     
  2. karlhaywood

    karlhaywood Account Closed

    Joined:
    Jan 17, 2010
    Messages:
    680
  3. mkenal

    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! :)
     
  4. OBP

    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.
     
  5. mkenal

    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! :)
     
  6. mkenal

    mkenal Thread Starter

    Joined:
    Apr 14, 2010
    Messages:
    6
    Unfortunately I know not the first thing about VBA so I doubt I would be able to implement that. :D
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I can talk you through Implementing it, as it does exactly what you want.
     
  8. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/916884

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice