MS ACcess autonumber sequence

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

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

May need to add data for other required columns:


Insert into yourtable (autonumberfield)
   select 20099999

Delete from yourtable where autonumber field = 20099999

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

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
 

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

You don't need to insert any items ...:-)

mx

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

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.

"This will ensure that you won't get duplicate sequences."
I don't think you can guarantee that in a multi-user environment ...

mx

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

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

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

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

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

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

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