DCount Duplicates reading record as its own duplicate

[eee333]

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

 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

 

[Ranman256]

can you not KEY the field and prevent ANY duplicates?

 

[eee333]

I believe not (unless I'm misunderstanding you) because there are two different fields people may use as IDs, so I can't do that. And I already have it set to just prevent duplicates, but I want it to tell them it's a duplicate right away, not when they try to proceed to a new record.

 

[Alc]

If it definitely counts the new record in amongst the check, would it not work to change this

 If DCount("SurveyID", "test", "SurveyID=" & Nz(Me.SurveyID, 0)) > 0 Then

to this?

 If DCount("SurveyID", "test", "SurveyID=" & Nz(Me.SurveyID, 0)) > 1 Then

 

[Ranman256]

then I am confused.
if you are preventing duplicates, how are they entering duplicates?

 

[Ranman256]

how about an empty screen except for the ID#
they enter, then its either duplicate and open it
or new and start new.

 

[eee333]

then I am confused.
if you are preventing duplicates, how are they entering duplicates?

because it gives you that system notification when you try to navigate away from the record, not when you first enter it into the field

 

[eee333]

If it definitely counts the new record in amongst the check, would it not work to change this

 If DCount("SurveyID", "test", "SurveyID=" & Nz(Me.SurveyID, 0)) > 0 Then

to this?

 If DCount("SurveyID", "test", "SurveyID=" & Nz(Me.SurveyID, 0)) > 1 Then

Because then it allows you to enter an actual duplicate.

 

[eee333]

how about an empty screen except for the ID#
they enter, then its either duplicate and open it
or new and start new.

that's really not the user interface I want to have. It won't work for my purposes.

 

[arnelgp]

you should test if the SurveID old value is the same as the SurveID new value, also if it is a New record:

Private Sub SurveyID_BeforeUpdate(Cancel As Integer)
dim lngOldID as long
Dim lngNewID As Long
lngOldID = nz(Me.SurveyID.OldValue,0)
lngNewID = NZ(Me.SurveyID.Value, 0)
'checks for duplicates'
If lngOldID <> lngNewID Or Me.NewRecord Then
If DCount("SurveyID", "test", "SurveyID=" & lngNewID) > 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 If
End Sub

 

[Cronk]

The simplest way of preventing duplicate SurveyIDs in the table, is to add a unique index on that field.

 

[eee333]

you should test if the SurveID old value is the same as the SurveID new value, also if it is a New record:

Private Sub SurveyID_BeforeUpdate(Cancel As Integer)
dim lngOldID as long
Dim lngNewID As Long
lngOldID = nz(Me.SurveyID.OldValue,0)
lngNewID = NZ(Me.SurveyID.Value, 0)
'checks for duplicates'
If lngOldID <> lngNewID Or Me.NewRecord Then
If DCount("SurveyID", "test", "SurveyID=" & lngNewID) > 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 If
End Sub

Yes, this worked perfectly, just what I needed! Thanks!