Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1127
  • Last Modified:

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.
3 Solutions
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)PresidentCommented:
<< 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.

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now