Sub DuplicateValueError(strNewValue As String)

   On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblRecords", dbOpenDynaset)

   With rst
      .AddNew
      !Field1 = strNewValue
      .Update  '<--- Error 3022 thrown here.
   End With

   rst.Close
   db.Close
   Set rst = Nothing
   Set db = Nothing
   Exit Sub
    
Exit_Here:
   rst.Close
   db.Close
   Set rst = Nothing
   Set db = Nothing
   Exit Sub
    
Err_Handler:
    
   If Err.Number = 3022 Then
      strMsg = "Error occurred when you called rst.Update." & vbCrLf & vbCrLf
      strMsg = strMsg & "Please enter a unique value for this field."
    
      MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Duplicate Value Error"
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   End If
    
   Resume Exit_Here

End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    
    Const ERR_DUPLICATE_INDEX_VALUE = 3022
    Dim strMsg As String

    If DataErr = ERR_DUPLICATE_INDEX_VALUE Then
        strMsg = "This record cannot be added to the database." & vbCrLf
        strMsg = strMsg & "It would create a duplicate record." & vbCrLf & vbCrLf
        strMsg = strMsg & "Changes were uncuccessful."

        MsgBox "Error Number: " & DataErr & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Duplicate Record."

        'Set focus to the offending control
        Me.txtText1.SetFocus
               
        Response = acDataErrContinue 'prevent Access from displaying its own error message
    Else
        Response = acDataErrDisplay 'cause Access to display its own error message
    End If

End Sub

quote:

---

Isn't .addnew supposed to create a new, unique record with a new, unique primary key (indexed, no duplicates, autonumber)?

---

INSERT INTO Table2 ( ID, Field1 )
SELECT Table1.ID, Table1.Field1
FROM Table1;