Deleting Duplicate Values in Linked Tables

[ChuckRobert]

I created an 2010 Access database to manage personnel data. One form is used to enter new personnel. On this form, a field called "Ind_Ser" (Individual_Serial_Number) is entered as this is a unique number given in our organization (primary key), writing to the Personnel table.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Another field on this form called "Ratee_Ser" (Ratee_Serial_Number), (which is also the same number as the Individual_Serial_Number) is a hidden field, writing to the Rater/Ratee table. These tables are linked 1 to 1 with enforced referential integrity based on these values.
<o></o>
I created a process to check to see if the Individual_Serial_Number was already entered in the database, before trying to add an individual. After the Individual_Serial_Number is entered (and the number also transfers into the Rater/Ratee table, since it is linked), DCount checks the personnel table for duplicates and notifies by MsgBox before clearing the Individual_Serial_Number from the form. However, I can’t get it to clear the Ratee_Serial_Number by assigning a value of "", as both of these are required entries. I get an error message that states ‘The value violates the validation rule for the field or record’.

Can anyone offer a suggestion?

Private Sub Ind_Ser_BeforeUpdate(Cancel As Integer)
 
Dim Criteria As String
Criteria = "[Ind_Ser]='" & Me.[Ind_Ser] & "'"
 
If DCount("*", "[tbl_Personnel]", Criteria) > 0 Then
    MsgBox "Serial Number " & Me.Ind_Ser & " already exists in this database.", vbOKOnly, "Duplicate Entry"
 
    Me.Ind_Ser.Text = ""
    Me.Ind_Ser.SetFocus
    
End If
 
End Sub