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

  • 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

    Monday, August 19, 2019 12:57 PM

Answers

All replies