Starting AutoNumber field with 1,000 in MS ACCESS

sglee
sglee used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
bbaoIT Consultant
Commented:
>  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. :)
Remote Training and Programming
Top Expert 2015
Commented:
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.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
glad to see you answering questions, Bing ~
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2014
Commented:
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

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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 Programming
Top Expert 2015

Commented:
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?

`
Most Valuable Expert 2014
Commented:
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 Consultant

Commented:
@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. :)

Author

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 Programming
Top Expert 2015

Commented:
@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?
Most Valuable Expert 2014

Commented:
@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 Programming
Top Expert 2015

Commented:
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.
Most Valuable Expert 2014

Commented:
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.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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 Programming
Top Expert 2015

Commented:
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 ~

Author

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 Programming
Top Expert 2015
Commented:
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

Most Valuable Expert 2014
Commented:
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

Author

Commented:
@crystal & @Nick
Thanks for the sample code and I appreciate it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial