MS ACcess autonumber sequence

Posted on 2010-08-23
Medium Priority
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 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 93

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

601 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