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

Jim.

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


SUMMARY
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.
MORE INFORMATION
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:
Text
Memo
Number (if the field's DefaultValue property is blank)


Access ADP Fields:
binary
decimal
float
int
ntext
numeric
nvarchar
real
smallint
text
tinyint
varbinary
varchar

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:
Fld1
Fld2
Fld3
Fld4

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
0
 
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.
0
 
Dale FyeCommented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.

Jim.
0
 
Dale FyeCommented:
Jim,

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.
0
 
Gustav BrockCIOCommented:
Well, I've never seen this - no matter what version from 1.0 and onwards.
0
 
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
https://support.microsoft.com/default.aspx?scid=kb;EN-US;271607

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

Jim.
0
 
Dale FyeCommented:
@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.
0
 
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.

Jim.

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
     rs.MoveLast
   Else
     If strCriteria = "Prev" Then
       rs.MovePrevious
     Else
       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
   Next
   
   frm.Painting = True
   
End Function

Open in new window

0
 
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!
0
 
Gustav BrockCIOCommented:
That macro or similar is what I would recommend to use.

Thanks Jim for the clarification.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.