0

In the BeforeUpdate event on my field, I have a dcount set up to disallow duplicates in the field SurveyID. This works great, except in the event that, in a past record, the user starts editing their SurveyID by accident and tries to cancel (accidentally selects the SurveyID field, starts typing, then erases it and enters the SurveyID). It reads that SurveyID as a duplicate (even though it's not really) and won't allow them to keep the ID. I've already done a half workaround by having the SurveyID reset after this, so if they're in the above scenario they can at least retain their ID. What I really want to know if there's a way with my code to prevent this from happening. The SurveyId is NOT the primary key, as there are two different ID codes they could have used. Maybe an extra piece of logic saying that if the primary key in that record in the form matches with the primary key of the "duplicate" (aka itself) then it doesn't get processed at a duplicate?

Here is my current 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
eee333
  • 51
  • 1
  • 1
  • 9

2 Answers2

0

Have you tried setting SurveyID to be indexed as Yes (No Duplicates)? As long as Required is also set to No it should not allow you to enter a duplicate value but you can also leave it blank if you wish.

You should still be able to trap the error in the form's On Error event so you can display a friendly message.

JJ32
  • 1,034
  • 1
  • 7
  • 24
-1

This is exactly why having a bound form is a bad idea. Never, ever bind a form to a table. Undoing an entry is wonky at best, and the way a bound form works is that as soon as you change fields, the record is written to the table. Yes, there is an Undo command but it's not reliable.

Unbind the table from the form, and write code that either updates a record (if it already exists) or writes a new record when you press a "Submit" button. It's a lot more coding and requires a better understanding of Access, but it's the proper way to do this.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117