Hello,
Thanks in advance for your help.

When a duplicate record is added, I want a custom error message to be displayed, and then the user to be taken to the original record.

Basically if If DataErr = 3022 (error code for duplicates) Then display custom message and go to original record.


I have attached a test database as well as my code below.

Here is the code I have on the Form's On Error event.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
'If an error occurs because of missing data in a required field
'display our own custom error message
Const conErrRequiredData = 3314
If DataErr = conErrRequiredData Then
MsgBox ("All required fields must be filled in")
Response = acDataErrContinue
Else
'Display a standard error message
Response = acDataErrDisplay
End If

'If an error occurs because of a duplicate record.
If DataErr = 3022 Then
Response = acDataErrContinue
MsgBox "This appendix already exists. Press Esc to cancel and then modify your entry."
End If

End Sub

Attached Files

Test.zip (493.1 KB, 18 views)

I got it with help from Prevent Duplicate Information from being entered into a Microsoft Access form | Database Solutions for Microsoft Access | databasedev.co.uk.
I was able to take the code/example and modify it for my needs.
Thank you

By placing the following code sample into the Before Update event of the Student Number field in the main form (top half of the form), this will prevent the duplication, raise a custom error message and return the user to the original record for the Student:


Private Sub strStudentNumber_BeforeUpdate(Cancel As Integer)
'*********************************
'Code sample courtesy of srfreeman
'*********************************
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.strStudentNumber.Value
stLinkCriteria = "[strStudentNumber]=" & "'" & SID & "'"
'Check StudentDetails table for duplicate StudentNumber
If DCount("strStudentNumber", "tblStudentDetails", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Student Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub

Attached Files

Prevent_Duplicates_2000.zip (85.1 KB, 19 views)