I've been having some trouble with this piece of code with the straightforward task of preventing a user from entering a duplicate ID into the field SurveyID. However, as I've been testing the various ways a user can "break" my database design, I've noticed an issue. If the user is in an old record, and they accidentally started editing the field, but then re-type the correct survey ID (so clicked on it and started typing without noticing they're in the wrong field, then noticed their error and re-typed in their correct ID 10002), it still reads it as a duplicate (even though it's...it's own duplicate) and gives them the error message. I've already figured out a slight workaround so that when this happens the value of the field is reset, but since receiving the "this is a duplicate" message could confuse users, I'm trying to find a solution that just prevents it from happening in the first place.
It may be useful to note that SurveyID is NOT the primary key in this case. I'm wondering whether that may be the key to my solution, somethign saying that if the primary key of this record and the primary key of its "duplicate" are the same, to not proceed--but I'm not sure how to do it.
Here is my code
It may be useful to note that SurveyID is NOT the primary key in this case. I'm wondering whether that may be the key to my solution, somethign saying that if the primary key of this record and the primary key of its "duplicate" are the same, to not proceed--but I'm not sure how to do it.
Here is my code
Code:
Private Sub SurveyID_BeforeUpdate(Cancel As Integer)
'checks for duplicates'
If DCount("SurveyID", "test", "SurveyID=" & Nz(Me.SurveyID, 0)) > 0 Then
Beep
MsgBox "The Survey ID number you have entered is a duplicate. Please double check that the number you entered is correct. If it is correct, please X."
Me.SurveyID.Undo
Cancel = True
End If
End Sub