Access 2016 AutoNumber RRS feed

  • Question

  • Disclaimer: I'm no expert with Access and I did not build the database, it's been around for the past 10 years and I'm learning to use it with my job. I've had no experience with databases until now.

    I have a Query and a table of stops along a route. Column of numbers and a column of the name of the stop. As it is now everything is done manually. The problem I have is when I need to add a new stop on the route I have to sit here and manually renumber everything so when the list gets printed out it stays in order.

    So say on the route: 1,2,3,4,5,6,7,8,9... I need to a new stop in between 7 and 8. I have to type in 1,2,3,4,5,6,7,8,9,10. It's not a hard thing to do other than the fact that I have about 200 stops. Is there a way to add a number point in the list somewhere and it can auto adjust the numbers? Does auto number work in this way? I've looked up auto number but it seems to only add to the bottom of the list, I need to add throughout the list.


    Thursday, March 30, 2017 11:10 PM

All replies

  • Hi,

    You can't use Autonumbers for this purpose. Once an  Autonumber is used, it can't be reused.

    You can do this using code or some other approach. For example, take a look at this demo for rearranging the sort order of records in a table.

    Hope it helps a little...

    Friday, March 31, 2017 12:26 AM
  • Let's say you have a table MyTable, with columns MyIndex, of long integer data type (not autonumber) and MyText, of Short Text data type, as below:

    MyIndex    MyText
    1               A
    2               B
    3               C
    4               D
    5               E
    6               F
    7               G
    8               H
    9               I
    10             J
    11             K
    12             L

    and you want to insert a row with a MyText value 'GG' between 7 and 8.  Then call the following function:

    Function InsertIndex(lngPosition As Long, strText As String)

        Dim rst As DAO.Recordset
        Dim strSQL As String
        strSQL = "SELECT * FROM MyTable" & _
            " WHERE MyIndex > " & lngPosition & _
            " ORDER BY MyIndex DESC"
        Set rst = CurrentDb.OpenRecordset(strSQL)
        With rst
            Do While Not .EOF
                .Fields("MyIndex") = .Fields("MyIndex") + 1
        End With
        strSQL = "INSERT INTO MyTable(MyIndex,MyText) " & _
            "VALUES(" & lngPosition + 1 & ",""" & strText & """)"
        CurrentDb.Execute strSQL, dbFailOnError

    End Function

    like this:

        InsertIndex 7,"GG"

    which will result in:

    MyIndex    MyText
    1               A
    2               B
    3               C
    4               D
    5               E
    6               F
    7               G
    8               GG
    9               H
    10             I
    11             J
    12             K
    13             L

    Ken Sheridan, Stafford, England

    Friday, March 31, 2017 6:11 PM
  • For relatively stable lists where I need to insert an ordered record every now and then, I use a low-tech solution.  In your case, I assume that stops on a route has some finite upper limit, especially if it is to be run by one driver in one day/shift.

    Create a field called something like SortOrder as a Number (Long Int).

    Make a judgement about how many edits/adds/deletions you can anticipate for that route.

    Then manually (yuck, the first time through for a long list although easy enough to write a one-time use, VBA procedure - see below) edit the list in order using 100, 200, 300, as SortOrder value.

    Then if you need to add a stop, say between 200 and 300, add the record and set its SortOrder value to 250. Next time, use half the gap again (such as 225 to insert between 200 and 250).

    Obviously a low tech solution, but very easy to understand and do for lists that don't undergo major revisions.


    Public Sub SortOrderFirstTime()
    '01-April-2017 DLT (for real! Not April Fools' code!)

    'this works the first time where the AutoNumber field
    'is the existing, desired sort order

    Dim db As DAO.Database
    Dim r As DAO.Recordset
    Dim i As Long
    Dim mSQL As String

    Const mLngGap As Long = 100
    Const mLngStart As Long = 100

    On Error GoTo TrapIt

    'RecID is AutoNumber field and is the orginal sort order

    mSQL = "SELECT tmp3.RecID, tmp3.SortOrder"
    mSQL = mSQL & " FROM tmp3"
    mSQL = mSQL & " ORDER BY tmp3.RecID;"

    Set db = CurrentDb
    Set r = db.OpenRecordset(mSQL)

    With r
        i = mLngStart
            If Not .EOF Then
                    !SortOrder = i
                i = i + mLngGap
                Loop While Not .EOF
            End If
    End With

    MsgBox "Done"


    On Error Resume Next
    If Not r Is Nothing Then
        Set r = Nothing
    End If

    Set db = Nothing

    Exit Sub


    MsgBox Err.Number & vbNewLine & Err.Description
    Resume EnterHere

    End Sub



    RecID SortOrder


    RecID SortOrder
    1 100
    2 200
    3 300
    4 400
    5 500
    6 600
    7 700

    • Edited by dave2001 Saturday, April 1, 2017 6:08 PM
    Saturday, April 1, 2017 5:08 PM
  • This is actually a “classic” computer science question! A rather beautiful question!

    If we were always using code (and not a query), then then we could/would use what is called a linked list. So even if we had 1 million rows, we would only add and update 3 records to insert and maintain the ordered list.

    However, today we have so much processing power, and your lists are relative small.

    You can update the “insert” point with a simple query:

    Update tblRoute set StopNumber = StopNumber + 1
    where stopNumber = 7 orderBy stopnumber.

    Of course the above would update every single stop number in the system greater then 7 to + 1

    So we also want to restrict the query to ONLY update the particular route.

    We thus add to the above the routnNumber.

    Update tblRoute set StopNumber = StopNumber + 1
    where stopNumber = 7 and RounteNum = 15
    orderBy stopnumber.

    Of course the above is “really” difficult for a “user” of your software. So you likely want to build some form that asks the user for the route, and then enter a insert point and THEN run the above query with the values from that form. So while this solution is easy, you should eventually write “some” code and provide a button to manage this process.

    The REALLY easy way? Let Access do this for you!!

    You thus don’t have to write the SQL by hand, and you don’t need to write any code. You can do this 100% with the mouse and drag + drop.

    While you have several examples, all require you to write SQL and some even require to write VBA. You can do this 100% with the mouse.

    Here is how:

    Create a new query – choose your table with the route information. In this example I have a table of Hotels and stops.

    Drag from the table to the query grid your two columns (route#, and stop#). Now click on the ribbon “update query”

    The result of above should be this:

    Note how I typed in the criteria of >5. This means that the update will ONLY effect 6, 7, 8 etc. (anything HIGHER then 5, or “>” or so called greater then 5.

    We also want to update the row to +1. So note the expression we typed in.

    Note VERY careful how I also said this query is to ONLY operate on the single route 7. If you don’t have separate routes and it just one set of stop numbers in the whole table, then you don’t need nor have that “route” restriction. But you now see how you could have multiple routes in that single table.

    Once you’re sure of the above, then click on the ! Run to execute this update query up in the ribbon.

    So you don’t have to write code, you don’t have to write SQL, and you can do this 100% via the mouse and drag + drop.

    As noted, try this on a copy of the database in cause you mess up.

    Have fun!

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Tuesday, April 4, 2017 3:47 AM