Copy a Record with AutoNumber ID

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy


Back in 2004, I wrote an Access Archon article (Copying Records and Linked Records) that described a method for copying a record with possible linked data in another table, using an array based on form controls. This article describes a different approach, based on table fields rather than form controls, and also shows how to deal with creating a new record in another table, linked one-to-one with the main table.

The Copying Technique

In my older article, the fields to be copied were picked up from controls on a form, skipping those with “No Copy” in their Tag properties. This technique worked well for a form based on a single table, but not so well when the form is based on a query that includes two or more tables. In the case that inspired this article, a main form was based on a query that included two tables joined one-to-one, and it had a subform based on another table, also linked one-to-one to the main table.

In some cases, the one-to-one table is split off from the main table because the number of fields needed exceeds the limit for an Access table; a one-to-one join might also be needed to make some information confidential. In either case, when a record in the main table is copied, it is also necessary to make new records in the other table(s), so that is what my code does.

The sample database, Copy Record with AutoNumber ID.accdb, has a table of employees which will be familiar to old Access hands, since it dates from early versions of the sample Northwind database. I added a table of financial data, linked to tblEmployeesAutoNumber one-to-one. The technique used to do the copying in this instance uses a table called tlkpNoCopyFields instead of the Tag property of a control to indicate which fields should not be copied.

The Relationships diagram for the database is shown in Figure A:

AW 1715-A1

Figure A. The Relationships diagram

And the tabbed form in Figures B and C:

AW 1715-B1

Figure B. The General Data tab of the Employees form

AW 1715-C1

Figure C. The General Data tab of the Employees form

VBA Code

In this database, the EmployeeID field is an AutoNumber field, so the determination of the new value is a little different; a new record is created and the value of the EmployeeID field is picked up from the new record; it may not be the next number after the last EmployeeID value, but it will be unique, and it will match the ID of the new record in the main table.

The first procedure is called from the Copy Record button on frmEmployeesAutoNumberID:

Private Sub cmdCopyRecord_Click()


On Error GoTo ErrorHandler


   Dim intReturn As Integer

   Dim lngID As Long

   Dim lngNewID As Long

   Dim strPrompt As String

   Dim strTitle As String


   'Get current ID value

   lngID = Nz(Me![EmployeeID])


   strTitle = "Question"

   strPrompt = "Copy Employee ID " & lngID & " to a new record?"

   intReturn = MsgBox(prompt:=strPrompt, _

      Buttons:=vbQuestion + vbYesNo, _



   If intReturn = vbNo Then

      GoTo ErrorHandlerExit

   End If


   lngNewID = CopyRecordAutoNumber(lngID)

   Debug.Print "New ID: " & lngNewID


   If lngNewID > 0 Then

      strPrompt = "Copy of Employee ID " & lngID _

         & " to Employee ID " & lngNewID & " successful"


      DoCmd.GoToRecord record:=acLast


      strPrompt = "Copy of Employee ID " & lngID _

         & " to Employee ID to a new ID not successful"

   End If


   MsgBox prompt:=strPrompt, _

      Buttons:=vbInformation + vbOKOnly, _




   Exit Sub



   MsgBox "Error No: " & Err.Number _

      & " in CopyLinkedRecordsAutoID procedure; " _

      & "Description: " & Err.Description

   Resume ErrorHandlerExit


End Sub

Two other procedures are called in turn; they are located in basUtilities:

Public Function CopyRecordAutoNumber(lngID As Long) As Long


On Error GoTo ErrorHandler


   lngNewID = Nz(CopyTableAutoNumber("tblEmployeesAutoNumber", lngID))


   If lngNewID = 0 Then

      CopyRecordAutoNumber = 0

      GoTo ErrorHandlerExit

   End If


   lngNewID = Nz(CopyTableAutoNumber("tblConfidentalDataAutoNumber", lngID))


   If lngNewID = 0 Then

      CopyRecordAutoNumber = 0

      GoTo ErrorHandlerExit

   End If


   CopyRecordAutoNumber = lngNewID



   Exit Function



   MsgBox "Error No: " & Err.Number _

      & " in CopyRecordAutoNumber procedure; " _

      & "Description: " & Err.Description

   Resume ErrorHandlerExit


End Function


Public Function CopyTableAutoNumber(strTable As String, lngID As Long) _

   As Long


On Error GoTo ErrorHandler


   Dim intCounter As Integer

   Dim lngFieldCount As Long

   Dim lngNewID As Long

   Dim rstNoCopy As DAO.Recordset

   Dim rstSource As DAO.Recordset

   Dim rstTarget As DAO.Recordset

   Dim strField As String

   Dim strNoCopyTable As String

   Dim strSearch As String

   Dim varData As Variant


   CopyTableAutoNumber = 0

   strNoCopyTable = "tlkpNoCopyFields"

   Set rstSource = CurrentDb.OpenRecordset(strTable, dbOpenDynaset)

   Set rstTarget = CurrentDb.OpenRecordset(strTable, dbOpenDynaset)

   Set rstNoCopy = CurrentDb.OpenRecordset(strNoCopyTable, dbOpenDynaset)

   lngFieldCount = rstSource.Fields.Count

   Debug.Print lngFieldCount & " fields in " & strTable


   strSearch = "[EmployeeID] = " & lngID

   Debug.Print "Search string: " & strSearch

   rstSource.FindFirst strSearch


   'Iterate through table fields, copying values from each

   'one that has data to a new record (except for fields that

   'should not be copied)



   For intCounter = 0 To lngFieldCount - 1

      strField = rstSource.Fields(intCounter).Name

      'Debug.Print "Field name: " & strField _

         & " (Field No. " & intCounter & ")"


      'Search for field name in No Copy table

      strSearch = "[FieldName] = " & Chr(39) & strField & Chr(39)

      'Debug.Print "Search string: " & strSearch


      rstNoCopy.FindFirst strSearch



      If rstNoCopy.NoMatch = True Then

         varData = rstSource.Fields(intCounter)

         'Debug.Print "Field data: " & varData


         If IsNull(varData) = False And varData <> "" Then

            rstTarget.Fields(intCounter) = varData

            Debug.Print strField & " in " & strTable & " copied"

         End If


         'Field should not be copied

         Debug.Print strField & " in " & strTable & " not copied"

      End If



   Next intCounter

   'Get new ID value from new record

   lngNewID = rstTarget![EmployeeID]

   Debug.Print "New ID: " & lngNewID

   CopyTableAutoNumber = lngNewID




   Exit Function



   MsgBox "Error No: " & Err.Number _

      & " in CopyTableAutoNumber procedure; " _

      & "Description: " & Err.Description

   Resume ErrorHandlerExit


End Function


The code in the sample database needs the following references (in addition to the default references):

Microsoft Word 14.0 Object Library (for SortDeclarations procedure only)

Microsoft Forms 2.0 Object Library (for SortDeclarations procedure only)

If you import code or objects into a database of your own, you may need to set one or more of these references. The version number may differ, depending on your Office version; check the version you have. References are set in the References dialog, opened from the VBA window. For more information on working with references, see Access Archon #107, Working with References.

Supporting Files

The zip file containing this article, in Word 2007-2010 format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site, as, which is the last entry in the table of Access Archon columns for Access Watch.

Document Name Document Type Place in
Copy a Record with AutoNumber ID.accdb Access 2007-2010 database (can also be used in higher versions of Access) Wherever you want
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address