asked on

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?

Gustav Brock

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 Fye

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 (EE MVE)

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

Dale Fye

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.

Gustav Brock

Well, I've never seen this - no matter what version from 1.0 and onwards.

Jim Dettman (EE MVE)

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.

Dale Fye

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

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Dettman (EE MVE)

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

BR Das

ASKER

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 Brock

That macro or similar is what I would recommend to use.

Thanks Jim for the clarification.