Customise the duplicate values error message

[lsmcal1984]

Dear all,

I have created an index in a table, which specifies that the fields [DateWorked] and [StaffName] cannot be duplicated. This is within the tblTimesheets table.

Now I need to program an error message that is displayed instead of the standard error 3202. I am using this code on the form's OnError event:

Sub|Function TimesheetError()
On Error GoTo Err_TimesheetError    
Err_TimesheetError:                     
MsgBox (That date has already been entered for this staff member!)
Resume Exit_TimesheetError                
End Sub|Function

However, I'd like it to use code on the BeforeUpdate property of the DateWorked field and code that suppresses the error 3202.



Many thanks!

[pbaldy]

In that event, you could use a DCount() with the appropriate criteria to test if the proposed values would be a duplicate and add

Cancel = True

to stop the update.

[Missinglinq]

What Paul gave you will stop the update, but I believe you'd have to remove the 'cannot be duplicated' at the Table level, or the system error message will still pop up.

Alternatively, you could trap the error at the Form level, with code in the Form_Error event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3202 Then 'Duplicate Data is Not Allowed
  
  If Screen.ActiveControl.Name = "DateWorked" Then
   Response = MsgBox("This is a Duplicate Entry for the DateWorked Field!", vbExclamation, "Duplicate Date Worked Are Not Allowed!!!")
   Response = acDataErrContinue
  End If

  If Screen.ActiveControl.Name = "StaffName" Then
   Response = MsgBox("This is a Duplicate Entry for the Staff Name!", vbExclamation, "Duplicate Staff Names Are Not Allowed!!!")
   Response = acDataErrContinue
  End If

End If

End Sub

But a question that I think needs to be addressed, because it does make a difference, here, is what your goal actually is. Do you

Want to not allow a duplicate Staff Name to be entered?

And/Or

Not allow a duplicate Date Worked to be entered?

Or is your goal to not allow a Record where both the Staff Name and the Date Worked are duplicated? It makes a difference in how you approach the problem!

If the latter is the situation, the code needs to be in the Form_BeforeUpdate event, and the Where Clause of DCount() needs to include both Fields. That would be something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
 If DCount("*", "Your Actual Table Name", "StaffName='" & Me.StaffName & "' And [DateWorked] = #" & Me.DateWorked & "#") > 0 Then
  MsgBox "This Staff Member/Date Worked Combination Has Already Been Entered! Try Again!"
  Cancel = True
 End If

End Sub

Linq ;0)>