Prevent duplicate entry of a record, but allow edits of the same record.

Question

Hello everyone, I have a code that prevents duplicate entries if the data contains the same information as a field called "txtPatientFIN." My issues is that when I open up my form to edit the record, it also checks validation and prevents me from updating that record stating the FIN already exists. Can I add a clause to the code to only check if it's a new record and just skip verification if it's editing the current record? Thanks for the help, here's my code:

Private Sub cmdSaveAdmission_Click()
Dim stLinkCriteria As String
Dim NewFIN As String

On Error Resume Next
NewFIN = Me.txtPatientFIN.Value
stLinkCriteria = "[PatientFIN] = " & "'" & NewFIN & "'"
On Error GoTo 0

If IsNull([txtPatientFIN]) Then
    MsgBox "Please enter a patient's FIN#.", vbCritical, "REQUIRED FIELD"
    [txtPatientFIN].SetFocus
    Exit Sub
ElseIf IsNull([txtVADPatientLastName]) Then
    MsgBox "Please enter a patient's LAST NAME.", vbCritical, "REQUIRED FIELD"
    [txtVADPatientLastName].SetFocus
    Exit Sub
ElseIf IsNull([txtVADPatientFirstName]) Then
    MsgBox "Please enter a patient's FIRST NAME.", vbCritical, "REQUIRED FIELD"
    [txtVADPatientFirstName].SetFocus
    Exit Sub
ElseIf Me.txtPatientFIN = DLookup("PatientFIN", "tblVADAdmissionHx", stLinkCriteria) Then
     MsgBox "FIN # " & NewFIN & " already exists.", vbCritical, "DUPLICATE RECORD"
     [txtPatientFIN].SetFocus
Else
    mSaved = True
    DoCmd.Close
    Forms!frmStartShell.navSUB!lstActiveAdmissions.Requery
    Forms!frmStartShell.navSUB!lstUnresolvedNonconformities.Requery
    Forms!frmStartShell.navSUB!lstActiveInfections.Requery
End If

End Sub

Answer 1

Hi InnVis

You can prevent duplicate values in a field in an Access table by creating a unique index.
...
Set a field's Indexed property to Yes (No duplicates)

1. In the Navigation Pane, right-click the table that contains the field, and then click Design View.
2. Select the field that you want to make sure has unique values.

https://support.office.com/en-us/article/prevent-duplicate-values-in-a-table-field-using-an-index-b5eaace7-6161-4edc-bb90-39d1a1bc5576

---

Cheers // Peter Forss Stockholm

Answer 2

This worked thank you! I used that number as the primary key.

Answer 3

Hi InnVis

I am happy to help.
This Forum has helped me too. Many times.

---

Cheers // Peter Forss Stockholm