Microsoft Access Autonumber

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< 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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.