none
How to prevent duplicates with validation during EDITING RRS feed

  • Question

  • Hey guys, I have some validations on save to prevent duplicate records, but I came across a loophole where I allow someone to edit the record and add they duplicate it. SO if I add my usual validation and no change are made to the primary key, in this case "FIN" then it won't allow the edits because it's reading a duplicate value in my DLookup. Any thoughts? Perhaps is there a way to add a count and if it's >2 then apply the validation error or if it's <2 then allow the save?

    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(Me.txtPatientFIN) Then
        MsgBox "Please enter a patient's FIN#.", vbCritical, "REQUIRED FIELD"
        Me.txtPatientFIN.SetFocus
        Exit Sub
    ElseIf IsNull([AdmitDate]) Then
        MsgBox "Please enter a patient's Admission Date.", vbCritical, "REQUIRED FIELD"
        [AdmitDate].SetFocus
        Exit Sub
    ElseIf Me.txtPatientFIN = DLookup("PatientFIN", "tblVADAdmissionHx", stLinkCriteria) Then
         MsgBox "FIN # " & NewFIN & " already exists.", vbCritical, "DUPLICATE RECORD"
         Me.txtPatientFIN.SetFocus
    
    Else
        mSaved = True
        DoCmd.Close
    End If


    • Edited by InnVis Wednesday, August 28, 2019 7:34 PM Edits
    Wednesday, August 28, 2019 7:34 PM

Answers

  • Figured it out! Here's how I solved:

    Dim stLinkCriteria As String
    Dim NewFIN As String
    Dim CurrentRecord As String
    
    On Error Resume Next
    NewFIN = Me.txtPatientFIN.Value
    CurrentRecord = Me.txtpkAdmissionHxID.Value
    stLinkCriteria = "[PatientFIN] = " & "'" & NewFIN & "' and [pkAdmissionHxID] <> CurrentRecord"
    On Error GoTo 0
    
    If Me.txtPatientFIN = DLookup("PatientFIN", "tblVADAdmissionHx", stLinkCriteria) Then
         MsgBox "FIN # " & NewFIN & " already exists.", vbCritical, "DUPLICATE RECORD"
         Me.txtPatientFIN.SetFocus
    
    End If

    Feels good.

    • Marked as answer by InnVis Wednesday, August 28, 2019 10:02 PM
    • Edited by InnVis Wednesday, August 28, 2019 10:05 PM
    Wednesday, August 28, 2019 9:47 PM

All replies

  • You can modify the code to test for whether they are adding a new record or updating an existing one:

    ElseIf Me.NewRecord And Me.txtPatientFIN = DLookup("PatientFIN", "tblVADAdmissionHx", stLinkCriteria) Then
         MsgBox "FIN # " & NewFIN & " already exists.", vbCritical, "DUPLICATE RECORD"
         Me.txtPatientFIN.SetFocus
    


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, August 28, 2019 7:54 PM
  • Tested this and it did not work, likely due to the mSaved = False boolean on the main form that prevents anything from saving. This is why the last line makes mSaved = True. Is there a way to create a tempVar and use that to validate instead? Have no idea how tempVars work.
    • Edited by InnVis Wednesday, August 28, 2019 8:07 PM edits
    Wednesday, August 28, 2019 8:04 PM
  • Tested this and it did not work, likely due to the mSaved = False boolean on the main form that prevents anything from saving. This is why the last line makes mSaved = True.

    Main form?  Is this a subform?  If so, I don't see how the mSaved boolean variable could be inherently shared between main form and subform, even if somehow it was being set to True.

    I think we need to see more of the context.  If you want to, you can create a cut-down copy of your database containing just the elements necessary to demonstrate the problem, compacted and then zipped to minimize the size of the file, and e-mail it to me.  You can get the address from my website, which is listed in my signature and my forum profile.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, August 28, 2019 8:13 PM
  • The Boolean is both on main form and subform. It's a complicated form, you'll see when I send you a copy - it has some work arounds. Driving atm, the second I get home I'll strip down a copy. Thanks for taking the time.
    Wednesday, August 28, 2019 8:21 PM
  • Tested this and it did not work, likely due to the mSaved = False boolean on the main form that prevents anything from saving. This is why the last line makes mSaved = True. Is there a way to create a tempVar and use that to validate instead? Have no idea how tempVars work.

    Hi InnVis,

    I am afraid I did not study your problem in too much detail, but it looks like your mSaved boolean breaks up regularly.

    I think, you want to add a new record if a combination of field values not yet exists, and else return the the already existing record.

    I use this kind of functionality in a Persons database with over 150,000 persons in it, to find or add the right one. In fact it is a standard functionailty for any record in any table.

    If this is what you want, I can give you the guidelines to build such a function.

    Imb.

    Wednesday, August 28, 2019 8:39 PM
  • That would be great! My method of preventing saves without validation is short and sweet but cause a lot of problems in cases where I want to manipulate multiple related records at the same time. I should mention the part that caused the actual prevent save is an If statement in the before update of the forms that .Cancel and .Undo if Boolean = false.
    Wednesday, August 28, 2019 8:43 PM
  • That would be great! My method of preventing saves without validation is short and sweet but cause a lot of problems in cases where I want to manipulate multiple related records at the same time.

    Hi InnVis,

    I am in fovour of the robust approach.

    What do you mean with the multiple related records. Are these the consults (or something else) of a Patient?

    To start with: in my systematics every Patient is defined by its own Patient_id, an unique number as PrimaryKey, All related records contain this Patient_id as ForeignKey. This makes things simple.

    Do you use such a construction?

    Imb.

    Wednesday, August 28, 2019 9:04 PM
  • I do indeed. This particular shell tracks patients and their admissions. Every time a patient comes into the hospital he is assigned a unique ID called a FIN. I use the FIN to track particular visits, but a patient can return to the hospital for multiple reasons so my database keeps a separate record of patients and a separate record for visits. Essentially this form we're talking about allows users to view a list of all current patients with a particular "issue" in this case it's a special heart pump. It allows the user to search the listbox to see if the patient already exists, but on top of that has validation on save event to ensure user didn't miss the step. If the patient is in the list, when he is selected the subform has another listbox with all related visits identified by FIN and the user can filter that subform and view details pertaining to that visit. Adding a new patient is not a problem, neither is adding a new FIN or editing the patient's field, but it gets tricky when the user wants to edit the admission history (maybe they mistyped the FIN# initially and need to make correction).
    • Edited by InnVis Wednesday, August 28, 2019 9:25 PM
    Wednesday, August 28, 2019 9:24 PM
  • Figured it out! Here's how I solved:

    Dim stLinkCriteria As String
    Dim NewFIN As String
    Dim CurrentRecord As String
    
    On Error Resume Next
    NewFIN = Me.txtPatientFIN.Value
    CurrentRecord = Me.txtpkAdmissionHxID.Value
    stLinkCriteria = "[PatientFIN] = " & "'" & NewFIN & "' and [pkAdmissionHxID] <> CurrentRecord"
    On Error GoTo 0
    
    If Me.txtPatientFIN = DLookup("PatientFIN", "tblVADAdmissionHx", stLinkCriteria) Then
         MsgBox "FIN # " & NewFIN & " already exists.", vbCritical, "DUPLICATE RECORD"
         Me.txtPatientFIN.SetFocus
    
    End If

    Feels good.

    • Marked as answer by InnVis Wednesday, August 28, 2019 10:02 PM
    • Edited by InnVis Wednesday, August 28, 2019 10:05 PM
    Wednesday, August 28, 2019 9:47 PM