Microsoft Access Autonumber

Posted on 2014-07-13
Last Modified: 2014-07-13
I am setting up a db with Autonumber as the primary key in several tables. I am creating the autonumber through a form entry and I am having problems with the formatting. I need the autonumber to be 5 digits and always positive. I set my field property in my table to the following:

Field Size: Long Integer
New Values: Random
Format: 00000

When I created the first two records in this table through my form the first autonumber that was 1248127065 and the second was -7090466874. Two problems that I need to fix:

1. Autonumber number needs to be 5 numbers.
2. Autonumber should never be negative.

I am working in Access 2007.
Question by:gacto
    LVL 26

    Assisted Solution

    Not sure what you're trying to do. The Autonumber field should not be manually changed. When a new record is added the autonumber will automatically increment by one.
    LVL 56

    Assisted Solution

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    << I need the autonumber to be 5 digits and always positive>>

      You can't do that.  If you want to control the number, you need to generate and assign your own key and not use autonumber.

      You got the wide ranging values because you set the new value property to "random" rather then sequential.

    LVL 84

    Accepted Solution

    An Autonumber field is a surrogate key field, and should never have any sort of "meaning" to your data. It's basically a pointer back to a specific record. It is guaranteed to be unique, but no necessarily sequential (although it usually is).

    You cannot format the AutoNumber, so you won't be able to enforce your "Always 5 digits" requirement. You could perhaps seed your Autonumber field to start at 10000, which would start off the 5 digit values.

    You're getting negative numbers because you've set the New Values to "Random". Set it to "Increment" instead.

    However, if you must have the 5 digit field, then you'll have to add a standard Numeric or Text field, and create a routine that handles this, and run it at the Form level whenever you add a new record. You can use the Form's Current event for something like this:

    Sub Form_Current()
      If Me.NewRecord Then
        Me.YourIDField = Nz(DMAX("YourIDField", "YourTable"),0) + 1
      End If
    End Sub

    Of course, if you have multiple users there's the possibility that you could have duplicated values in that field. If that's a concern, then you'd need to setup a table that stores the "next available number", and pull from that table just before you insert the new record. This would reduce the likelihood that you will end up with a duplicate.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now