Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS ACcess autonumber sequence

Posted on 2010-08-23
18
Medium Priority
?
661 Views
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.
0
Comment
Question by:vihaan
[X]
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
  • 5
  • 4
  • 3
  • +5
18 Comments
 
LVL 18

Expert Comment

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

Expert Comment

by:andreyman3d2k
ID: 33502680
This is a good explanation.

http://www.tech-recipes.com/rx/518/access-change-the-start-value-of-an-autonumber-field/

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

Andrey
0
 
LVL 42

Expert Comment

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


Insert into yourtable (autonumberfield)
   select 20099999

Delete from yourtable where autonumber field = 20099999
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 75

Accepted Solution

by:
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>)
Example:
?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.

mx
0
 
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

ET
0
 
LVL 42

Expert Comment

by:dqmq
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
 
0
 
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
0
 
LVL 75
ID: 33503130
You don't need to insert any items ...:-)

mx
0
 
LVL 3

Expert Comment

by:SantiagoA
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..
0
 
LVL 9
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.
0
 
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 ...

mx
0
 
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.

ET
0
 
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.

mx
0
 
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)

Continue_WasLocked:
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

Exit_BeforeInsert:
rst.Close
strStatus = SysCmd(acSysCmdClearStatus)
Exit Sub


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


Let me know if you have any other questions.

ET
0
 
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.

ET
0
 
LVL 9
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.

Cheers,
Armen
0
 
LVL 9
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.

Cheers,
Armen
0
 
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.

ET
0

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.

Question has a verified solution.

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

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.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

670 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