Solved

MS ACcess autonumber sequence

Posted on 2010-08-23
18
649 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
  • 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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) 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>)
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 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
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now