Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

MS ACcess autonumber sequence

Posted on 2010-08-23
Last Modified: 2013-11-28
HI In a table the primary key field is autonumber data

the values in the autonumber are 20090001, 2009002......

2009 is the year so i now i want the sequence to start from 2010000 keeping all the 20090001,20090002....values intact. Please help me. THank You.
Question by:vihaan
  • 5
  • 4
  • 3
  • +5
LVL 18

Expert Comment

ID: 33502665
A simple way would be to insert x records (however many needed to get to 20099999), then delete them.

Expert Comment

ID: 33502680
This is a good explanation.


Let me know if it is unclear, or if you are not sure how to perform any of the steps.

LVL 42

Expert Comment

ID: 33502809
May need to add data for other required columns:

Insert into yourtable (autonumberfield)
   select 20099999

Delete from yourtable where autonumber field = 20099999
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 33502919
To set your own starting point and incrementing value, here are two methods.
In all cases, make a BACKUP of your table first!!

** VBA Code method - Example**
Place this code in a standard module. Do not name the module the same as the Function below.

Public Function mResetAutoNumber() As String
    Dim sSQL As String
    Dim lStartVal As Long, lIncrement As Long
    lStartVal = 1000   'change to your desired starting number
    lIncrement = 50    'change to your desired increment
    sSQL = "ALTER TABLE Table4 ALTER COLUMN AN COUNTER (" & lStartVal & ", " & lIncrement & ");"
    CurrentDb.Execute sSQL
    mResetAutoNumber = "Auto Number has been re-numbered"
End Function

The first number (lStartVal) is the starting value, and the second (lIncrement) is the increment.
The example above will start the Auto Number at 1000 and increment by 50.

To run the code one time, open the VBA Immediate Window, and type
this line (including the question mark, followed by Enter:

?Function mResetAutoNumber(<YourStartValueNumber>, <YourIncrementNumber>)
?Function mResetAutoNumber(500,3)

You can run this against an empty existing table, or to modify the next higher Auto Number value and increment.
It will *not* change any existing auto number values.

This works for A2K and later.

**Data Definition SQL Query method - follow these steps:

1) Open a new query in design view.
2) Menu>>View>>SQL View
3) Enter the following SQL - all on one line:
ALTER TABLE [SomeTableName] ALTER COLUMN [YourAutoCounterFieldName] COUNTER(<YourStartValueNumber>,<YourIncrementNumber>);

<YourIncrementNumber> is optional.  Default is one.

Example:  ALTER TABLE [Table1] ALTER COLUMN [AutoNum] COUNTER(300,5);

4) Menu>>Query>>Run

That's it.

LVL 19

Expert Comment

by:Eric Sherman
ID: 33502981
>>>>HI In a table the primary key field is autonumber data <<<<<<<

Why do you use a autonumber if you want total control over the numbering sequence.  I normally use a Long Integer field data type and increment the number programmatically using a NEXTID field to store the value of the next available number.

Just my $.02

LVL 42

Expert Comment

ID: 33503077
Bad:  embedding the year in an autonumber field
Better:  embedding the year in a long integer
Best:  Separate fields for year and sequence within year
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33503093
vihaan,I'm with ET: while you certainly can manipulate an AutoNumber to start at a point other than 1 (and to do things like encode a year), I don't think that you should.IMHO, the only legitimate use for an AutoNumber is to provide a unique index value, such as for a primary key.  Any time you start trying to attach semantic value to an AutoNumber you are playing with fire.And just for kicks, what if for one year you end up inserting >9,999 items?  :)Patrick
LVL 75
ID: 33503130
You don't need to insert any items ...:-)


Expert Comment

ID: 33507602
Hi, go simple. Leave the primary key alone. don´t mess with your primary keys after the table has data on it. Create a parallel field at your convenience.
Simply create a new field called "idbyyear". Create a quick code to transfer just the last 4 numbers from the primary key to the new field, but add the year you what to the fist 4 numbers.
In your input form include a counter to populate this new field at your convenience.
Now make the modificatios at the front-end application to start using this field instead of the primary key..
ID: 33511917
As ET and others say, don't put any meaningful information into an Autonumber.  To expand on the idea of a "next available" counter, you can increment it using an updatable recordset in VBA code.  This will ensure that you won't get duplicate sequences.  Don't use the method of finding the highest value and adding 1 - that way can allow duplicates to occur.
LVL 75
ID: 33512901
"This will ensure that you won't get duplicate sequences."
I don't think you can guarantee that in a multi-user environment ...

LVL 19

Expert Comment

by:Eric Sherman
ID: 33513010
>>>I don't think you can guarantee that in a multi-user environment ...<<<<

Sure you can!!  I have a Dispatch application with 8 endusers taking calls and creating work orders all at the same time.  The VBA function that I wrote to increment the NextWorkOrderID will lock the record first.  If by chance another dispatcher is creating a work order and the exact same second the code will loop until the record is unlocked.  This works and have been for the past couple of years with no problems.

LVL 75
ID: 33513076
Well, if you are really using a locking scheme to insure this, then I can believe. But most people don't go to that extreme ... and just rely on the DLookup + 1 scheme.

LVL 19

Expert Comment

by:Eric Sherman
ID: 33521650
vihaan ....

All you have to do is establish a 1 record setup table with your Long Integer field you want to increment ... in my code below it's called bucket1.  

Just review the following code from my Dispatch application.  Basically before the New Work Order is created in the table the code below will grab the stored NextWorkOrderID then increment it and save the record.  It will then pass the work order number to a field on the form.  The code will lock the record and if another user should try to create a new work order at the exact same time it will loop until the record is unlocked.  You have to open the recordset using dbPessimistic along with the error trapping as shown below.

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim rst As Recordset
Dim varWO As Long
Dim strStatus As String
strStatus = SysCmd(acSysCmdSetStatus, "Assigning Work Order Number, Please wait ....")

On Error GoTo Err_BeforeInsert

Set rst = CurrentDb.OpenRecordset("bucket1", dbOpenDynaset, dbPessimistic)

rst.Edit                                            'Lock the record
varWO = rst!nextworkorderid         'Grab the next work order stored in the table
rst!nextworkorderid = rst!nextworkorderid + 1       'Increment the NextWorkOrder
rst.Update                                                                'Save the Value

Me.WO_ID = varWO                               'Pass the work order number to the form

strStatus = SysCmd(acSysCmdClearStatus)
Exit Sub

    If Err.Number = 3218 Then  'The record was locked
        Resume Continue_WasLocked
        MsgBox Err.Number & "  " & Err.DESCRIPTION
        Resume Exit_BeforeInsert
    End If
End Sub

Let me know if you have any other questions.

LVL 19

Expert Comment

by:Eric Sherman
ID: 33521713
mx ...

>>>>>Well, if you are really using a locking scheme to insure this, then I can believe. But most people don't go to that extreme ... and just rely on the DLookup + 1 scheme.<<<<<

Yes, I started out with the DLookup +1 scheme but with 8 users creating work orders at the same time I did not want to take the chance of creating duplicate order numbers.  The above code has worked with no problems for a number of years now.

ID: 33522265
Hi mx,

We follow the principle that if something *can* happen, it *will* happen.  Getting a duplicate with the Max + 1 method isn't likely with just a few users, but it's not that hard to use a locking method anyway.  And the locking method does guarantee uniqueness, because only one process can increment at a time.

ID: 33522446
Hi ET,

I like your incrementing routine, but I think it's more complex than necessary.  I don't think you actually need the pessimistic lock and the loop to try again.  In our incrementer routines, we just open the record for edit, increment the value, and update the record, as you do.  Only one process can do this at a time - others have to wait.  But since they wait for so short a time, we've never needed to use a retry loop.

LVL 19

Expert Comment

by:Eric Sherman
ID: 33527302
Hi Armen ...

>>>>> But since they wait for so short a time, we've never needed to use a retry loop.<<<<<

Yes, it happens so fast that to be honest as far as I can tell the the retry loop has never been executed and the display message is never displayed.  It is there mainly for "just in case" or if by chance a workstation should get hung up for some reason.  Again, the code has worked for a number of years and it's instant.


Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

828 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