Auto Increment Feature

I have just upgraded from an earlier version of Access to Access 2016, and I frequently have to add batches of rows to a table in which one of the fields has to contain a serial number. In the earlier versions of Access, when I was entering data into the serial number field, one row after another, if, for example, I typed 23 in this field of the first row, then I moved to the next row and entered 24, then if I immediately moved again to the next row the field in that row would be automatically filled with 25, so by just pressing and holding the down arrow I could fill 26, 27, 28, etc. in this field through the whole batch of new rows. This saved a lot of time in data entry. Unfortunately this does not work in Access 2016, nor could I find any setting option to restore it. Any ideas?
BR DasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Gustav BrockCIOCommented:
You were just in luck. The AutoNumber is not intended for anything else than identifying a record.

If you need numbering for other uses, like your serial number, create a separate field for this and code to fill it after the import.
Dale FyeOwner, Developing Solutions LLCCommented:
The description of this is not the normal behavior of an autonumber field in a table or form, since you cannot type data into an autonumber field.

I'm guessing that in the application you are describing, there is some code in the AfterUpdate event of the control or the form, which increments a form level variable, and then the Current event checks to see if that field is NULL, and if so applies the new value to that field.  And that this may not be functioning properly after migrating to A2016.

Are you doing this in a form, or in a table?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The description of this is not the normal behavior of an autonumber field in a table or form, since you cannot type data into an autonumber field.>>

 Actually it's not related to autonumbers at all....this is a long ago forgotten feature in Access that was more than a pain in the neck for most people.   I wasn't aware though that it had been removed.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dale FyeOwner, Developing Solutions LLCCommented:

I was never aware of it as a feature;  do you remember how you configured it?  Was it at the table level?

I still have 2003 on a VM and would take a look at it if you think it was active there, and can give me some idea where to look.
Gustav BrockCIOCommented:
Well, I've never seen this - no matter what version from 1.0 and onwards.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I found the reference to the AutoFill MSKB:

271607 ACC2000: Tables Are Automatically Populated with Values As You Move Through Fields or Records;EN-US;271607

 but the content has been dropped.   I did confirm however that the feature was removed starting with Access 2007.

Dale FyeOwner, Developing Solutions LLCCommented:
@BR Das

This feature would be relatively easy to replicate using KeyPreview and the KeyDown or KeyPress event of either a datasheet or a continuous form.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Ah ha!  Found a copy.

Some people used it, but for most it was a royal pain because it could not be disabled.


Microsoft Knowledge Base Article - 271607    
ACC2000: Tables Are Automatically Populated with Values As You Move Through Fields or Records

This article was previously published under Q271607
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Novice: Requires knowledge of the user interface on single-user computers.

When you use the TAB key to move through empty fields or use the DOWN ARROW key to move down to new records in an Access 2000 table, Microsoft Access may automatically populate the next field or record with a value.
Microsoft Access 2000 includes a new feature that is named AutoFill. This feature is built into Access, and it is always enabled. You cannot disable it.

AutoFill applies only to Access tables that have fields with certain data types and works only if you enter certain multiples of numbers. The following two lists indicate which field types are affected by AutoFill, based on the type of Access table (MDB or ADP tables) that you are using.
Access MDB Fields:
Number (if the field's DefaultValue property is blank)

Access ADP Fields:

To see how AutoFill functions, follow these steps:
Open a new Access 2000 database or a new Access project.
Create a new table, and add the following four fields:

If you are using an Access database (.mdb), assign a data type of Text to each of these four fields. If you are using an Access project (.adp), assign a data type of Binary to each of these four fields. Also set Fld1 of the .adp table as the Primary Key.
Open the table in Datasheet view.
Enter a value of 1 into Fld1. Press the TAB key to move to Fld2, and then enter a value of 2. Press the TAB key to move to Fld3, and note that a value of 3 is automatically entered. If you press TAB to move to Fld4, a value of 4 is automatically entered.
If you press the TAB key again, the pointer moves to a new record. In the new record, enter a value of 2 into Fld1.
Press the DOWN ARROW key to move down to a new record, and note that a value of 3 is automatically entered into Fld1 on the next record. As you continue to press the DOWN ARROW key, Fld1 is automatically populated with a new value.
AutoFill works only in a limited number of multiples. For example, if you enter 2 into Fld1 and enter 4 into Fld2, AutoFill inserts a 6 into Fld3, and so on. However, there are other multiples that AutoFill does not work with. Two examples of this would be multiples of 3, and days of the week.

If any of the supported field types are contiguous, AutoFill applies. In other words, within an Access table, if Fld1 were Text, and Fld2 were Number (without a default value), and Fld3 were Text, AutoFill would still apply when you press the TAB key to move from field to field. However, if the field types that support AutoFill are separated by other field types that do not support AutoFill, AutoFill is not applied. If the Text fields were separated by Currency fields, AutoFill would not be applied when you press the TAB key to move from one field to the next. In this case, however, AutoFill still applies if you press the DOWN ARROW key to move down to new records.
The information in this article applies to:
Microsoft Access 2000
Last Reviewed: 10/8/2001 (1.0)  
Keywords: KB271607

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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
And more to the question at hand, you can use one of the two procedures below to duplicate the feature.  As Dale said, you could do this in a number of ways with the BeforeInsert of the record, On enter of the field, etc.

 First is probably more what you'd want.  You'd call it from the controls OnEnter event, and if it was null, fetch the last value and add one to it.


Function GetPreviousRow(frm As Form, strFieldName As String) As Variant

        ' Fetches value of field from previous row
        Dim strBM As String
        Dim rstClone As Recordset

        ' Get the bookmark for the current row.
10      strBM = frm.Bookmark

        ' Now create the record set clone, and make it
        ' refer to the same row as rst, which is on the same
        ' row as the form.
20      Set rstClone = frm.RecordsetClone
30      rstClone.Bookmark = strBM

        ' Move the clone record set to the previous row.
        ' If this puts us at the BOF, then the result has to be
        ' FALSE, and leave the function.
40      rstClone.MovePrevious
50      If rstClone.BOF Then
60        GetPreviousRow = Null
70      Else
          ' If you're not at BOF, then retrieve the necessary info.
80        GetPreviousRow = rstClone(strFieldName)
90      End If

100     rstClone.Close
110     Set rstClone = Nothing

End Function

Open in new window

Function AutoFillNewRecord(frm As Form, strCriteria As String)

   ' Picked up from MSKB Q210236
   ' Needs control on form called AutoFillNewRecordFields, which
   ' is a semi-colon delimited list of fields to fill.
   Dim rs As DAO.Recordset
   Dim ctl As Control
   Dim strFillFields As String
   Dim intFillAllFields As Integer
   On Error Resume Next
   ' Exit if not on the new record.
   If Not frm.NewRecord Then Exit Function
   ' Jump to the correct record (to autofill form).
   Set rs = frm.RecordsetClone
   If strCriteria = "Last" Then
     If strCriteria = "Prev" Then
       rs.FindFirst strCriteria
       If rs.NoMatch Then Exit Function
     End If
   End If
   ' Exit if you cannot move to the last record (no records).
   If Err <> 0 Then Exit Function
   ' Get the list of fields to autofill.
   strFillFields = ";" & frm![txtAutoFillNewRecordFields] & ";"
   ' If there is no criteria field, then set flag indicating ALL
   ' fields should be autofilled.
   intFillAllFields = Err <> 0
   frm.Painting = False
   ' Visit each field on the form.
   For Each ctl In frm
      ' Fill the field if ALL fields are to be filled OR if the
      ' ...ControlSource field can be found in the strFillFields list.
      If intFillAllFields Or InStr(strFillFields, ";" & (ctl.Name) & ";") > 0 Then
         ctl = rs(ctl.ControlSource)
      End If
   frm.Painting = True
End Function

Open in new window

BR DasAuthor Commented:
Thanks to all for the kind help! Clear now. I can see how it could be a pain for some users! Years ago I had written a macro whereby I would paste the variable data into a temporary table and from there the macro would ask what was the initial value for the serial number, and it would then append the records to the main table, including generating the serial numbers for each. I guess I will have to resurrect that macro!

Wishing you all the best!
Gustav BrockCIOCommented:
That macro or similar is what I would recommend to use.

Thanks Jim for the clarification.
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.