Starting AutoNumber field with 1,000 in MS ACCESS

Hi,
  I have a database system where I have ORDER table (and order item table and others). The order table has a field called "Order Number".
  The data type of Order Number field is "AutoNumber", so naturally it begins with 1 in a brand new table.
  However the user wants that number to start from 1,000 instead. (Apparently when you give out the order number 1, it does not leave a good impression as opposed to 1,000).

  I thought about creating 999 empty records (so that each record has a number 1 thru 999 in order number field) and delete them. That way when the user clicks [CREATE NEW ORDER] button in Order Entry Form, the system will populate 1,000 in the order number field.

 Is there a better way of accomplishing this? Any possible issues that I am not aware of?
LVL 1
sgleeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bbaoIT ConsultantCommented:
>  the user wants that number to start from 1,000 instead

better know what's the background or specific reason that makes the user have such a concern. e.g. do they need to do some kind of calculation on the AutoNumber field in the near future?

however, as the data type is system built-in aiming for keeping unique primary keys,  it is not recommended to do any adjustment to its value.

anyway, if you have to, IMPO, your "silly" way is the most safe way and recommended. :)
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Some people will tell you autonumber values don't matter ... and really they don't but I am with you ... I like to start with 4 digits :)

Make this append query
INSERT INTO MyTablename ( Autonumber_fieldname) 
SELECT 1000 

Open in new window

then compact/repair the database after it runs.  If there are required fields, they must be specified too.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
glad to see you answering questions, Bing ~
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Nick67Commented:
Now, I can answer your question straight-up -- but that really isn't the sanswer.
Autonumbers are unique -- but that's it.
You shouldn't use them for anything user-related.

Go into a table
Begin a record
Hit ESC
Repeat.

Notice that you now have a missing autonumber.
That will drive the user insane.
So, the real answer is that you shouldn't use an autonumber for this purpose

That being said, here is some nice ADOX code for screwing with autonumbers
The first function is of interest to you

Function ResetSeed(strTable As String) As String
    'Purpose:   Reset the Seed of the AutoNumber, using ADOX.
    Dim strAutoNum As String    'Name of the autonumber column.
    Dim lngSeed As Long         'Current value of the Seed.
    Dim lngNext As Long         'Next unused value.
    Dim strSql As String
    Dim strResult As String

    lngSeed = GetSeedADOX(strTable, strAutoNum)
    If strAutoNum = vbNullString Then
        strResult = "AutoNumber not found."
    Else
        lngNext = Nz(DMax(strAutoNum, strTable), 0) + 1
        If lngSeed = lngNext Then
            strResult = strAutoNum & " already correctly set to " & lngSeed & "."
        Else
            Debug.Print lngNext, lngSeed
            strSql = "ALTER TABLE [" & strTable & "] ALTER COLUMN [" & strAutoNum & "] COUNTER(" & lngNext & ", 1);"
            Debug.Print strSql
            CurrentProject.Connection.Execute strSql
            strResult = strAutoNum & " reset from " & lngSeed & " to " & lngNext
        End If
    End If
    ResetSeed = strResult
End Function

Function GetSeedADOX(strTable As String, Optional ByRef strCol As String) As Long
    'Purpose:   Read the Seed of the AutoNumber of a table.
    'Arguments: strTable the table to examine.
    '           strCol = the name of the field. If omited, the code finds it.
    'Return:    The seed value.
    Dim cat As New ADOX.Catalog 'Root object of ADOX.
    Dim tbl As ADOX.Table       'Each Table in Tables.
    Dim col As ADOX.Column      'Each Column in the Table.

    'Point the catalog to the current project's connection.
    Set cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables(strTable)

    'Loop through the columns to find the AutoNumber.
    For Each col In tbl.Columns
        If col.Properties("Autoincrement") Then
            strCol = "[" & col.Name & "]"
            GetSeedADOX = col.Properties("Seed")
            Exit For    'There can be only one AutoNum.
        End If
    Next

    'Clean up
    Set col = Nothing
    Set tbl = Nothing
    Set cat = Nothing
End Function


Function DeleteAllAndResetAutoNum(strTable As String) As Boolean
    'Purpose:   Delete all records from the table, and reset the AutoNumber using ADOX.
    '           Also illustrates how to find the AutoNumber field.
    'Argument:  Name of the table to reset.
    'Return:    True if sucessful.
    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    Dim strSql As String

    'Delete all records.
    strSql = "DELETE FROM [" & strTable & "];"
    CurrentProject.Connection.Execute strSql

    'Find and reset the AutoNum field.
    cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables(strTable)
    For Each col In tbl.Columns
        If col.Properties("Autoincrement") Then
            col.Properties("Seed") = 1
            DeleteAllAndResetAutoNum = True
        End If
    Next
End Function

Open in new window

Gustav BrockCIOCommented:
However the user wants that number to start from 1,000 instead.
You need to redesign this.
The AutoNumber is for you and the database and should never have any meaning.
Order numbers are for humans and must created separately.

Even if you fix the start number, you'll get in trouble when the user deletes an order and the AutoNumber gets lost. And numbers may change if the database should need a repair someday.

/gustav
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
to correct a common misconception that "Autonumbers are unique".  In theory that is true.  In reality, a table in only assured to have unique autonumbers if the autonumber field is a Primary Key or has a Unique Index.

I can understand fixing the start number to keep the number of digits more consistent.

... besides, incorporating ideas from others helps them get more involved in the project.  In the end, what is more important? Where an autonumber starts or seeing a smile on someone who feels they made a great contribution?

`
Nick67Commented:
Ah
"assured to have unique autonumbers if the autonumber field is a Primary Key or has a Unique Index."
And a PK by default has a unique index.
http://www.pcreview.co.uk/threads/access-autonumber-not-unique.3429287/
Well, I never use an autonumber for anything but a primary key, so I've never been bit by that particular wrinkle.  Good to know it is out there.
bbaoIT ConsultantCommented:
@crystal

thanks for your greeting. nice to meet you online, a MVP for 10+ years!

i believe we must had passed each other by somewhere at MVP summits in Redmond. :)
sgleeAuthor Commented:
@BING
"better know what's the background or specific reason that makes the user have such a concern. e.g. do they need to do some kind of calculation on the AutoNumber field in the near future?" --->  I did not ask the reason why and they don't have any prior order system to convert from, so I don't think it is because of calculation reasons.

I asked the user to re-consider the request and a moment ago, he said it was fine to start with 1.

But I will try the suggestions made above and post the results.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
@Nick, many of us who know have been bit or seen someone who has ... glad you won't have to go through the hard knocks ~ when I get a database from someone else, unique index on autonumbers is something I check for.

@Bing you're welcome and thanks ~ perhaps we have ... what is your name?
Nick67Commented:
@sglee

I think you missed the point.
It is irrelevant what the starting number is for an autonumber
because the autonumber is for you the developer and the database alone
Making the sequence, value, and completeness of an autonumber field important to a user is going to be a fail-whale.

Create an order number system that meets the users needs.
Don't use an autonumber as its basis
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
even though autonumber values are irelevant as long as they are unique, the fact that someone needs to compare them to make sure data is going into the right places remains.  That is easier to do when the number of digits is the same instead of, for instance, 17, 434 and 1198.  It is also easier for customers if the autonumber is given significance.  Many of the people I help with Access DO use autonumbers to mean something important sometimes too -- and that is fine, in my opinion.

Numbers skip, of course -- that is ok.  Perhaps the issue is more where to start and how many digits to have.
Nick67Commented:
There are those topics that generate strong debate.
This is one.
Many folks feels strongly that an autonumber makes an excellent PK, that PKs should be numeric and have no significance to the user.
I expose the PK to the user, but do not label it.
The user knows that it should trend upward over time, that's it.

the fact that someone needs to compare them to make sure data is going into the right places remains.
I have no idea what that means.
Autonumber PKs and referential integrity mean that this isn't an issue in anyway that I can conceive.

It is also easier for customers if the autonumber is given significance.
There'll be strong disagreement about that.
Something that looks like a sequence should be continuous, and autonumbers are not.
That confuses users.

Many of the people I help with Access DO use autonumbers to mean something important sometimes too -- and that is fine, in my opinion.

It's a free world.  You get to do what you want.
But user significance for autonumbers usually leads to heartache and confusion.

YMMV -- and clearly does.
Gustav BrockCIOCommented:
But user significance for autonumbers usually leads to heartache and confusion.
Right. And the heartache is not the user's but the developer's. Been there, you never go there again.

/gustav
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
all we can do is present our opinions on the use of autonumbers and let each decide for themselves.  Like naming, there is no right or wrong convention as long as there is something consistent ~

If absolute sequence is not needed, I see nothing wrong with using a number that is already unique instead of creating another one ... the engineer in me thinks it is more efficient to use what is there already ~

to each their own ... we all have our own ways ~
sgleeAuthor Commented:
Is there some type of ACCESS VB code out there that generates a new order number (one greater that previous)?
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
sure, an easy way is to use DMax to get the maximum value in the table and then add 1.  Here is how to calculate it in code.  You can calculate it to show on the form BeforeInsert event ... but recalculate it on the form BeforeUpdate event if you have multiple users.
   dim nNewNumber as long
   nNewNumber = nz( DMax ( "[fieldname]", "[tablename]") ,0) + 1
   me.SequenceNumber_controlname = nNewNumber 

Open in new window

Nick67Commented:
They are all of a type
Basically

dim NewOrderNumber as Long
dim rs as recordset
set rs = Currentdb.OpenRecordset("select top 1 OrderNumber from tblWhatever Order by orderNumber Desc",dbOpendynaset, dbseechanges)
NewOrderNumber = rs!Ordernumber + 1

Open in new window


This one is production code for new quote numbers, with Year pre-pended and reseting in the new year

Public Function NewQuoteNumber()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDbC
Set rs = db.OpenRecordset("select Top 1 QuoteNumber from tblQuotes where cint(left(quotenumber,4))= year(Date()) order by cint(right(quotenumber,4)) desc;", dbOpenDynaset, dbSeeChanges)
If rs.RecordCount = 0 Then
    NewQuoteNumber = CStr(Year(Date) & "001")
Else
    NewQuoteNumber = CStr(CLng(rs!QuoteNumber) + 1)
End If
End Function

Open in new window


The trick is to call this function during the creation of the record
That minimizes any possibility of duplicates

First thing the command button to create a new quote does is generate and commit that number

Private Sub cmdNewQuote_Click()
DoCmd.GoToRecord , , acNewRec
Me.QuoteNumber.Value = NewQuoteNumber()
End Sub

Open in new window

sgleeAuthor Commented:
@crystal & @Nick
Thanks for the sample code and I appreciate it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.