databasedev.co.uk - database solutions and downloads for microsoft access

Preventing Duplicates from being entered

There will always be situations where you need to ensure that you do not have duplicates entered into your Microsoft Access database. Ideally you will want to validate data at the lowest level possible, so if you can enforce the rules at the table level (using Primary Keys), that's great.

However, while you can customize a field validation message at the table level, it's not so simple for index violations.

One possible approach is to check for the problem in a Microsoft Access form when the user is entering data, either in the Form Error event, or in the Before Update event of the controls.

Obviously, when entering a duplicate of a Primary Key value, Microsoft Access will automatically prevent you from saving the record, however this will only come into action when you may have made various entries in to fields in the form and tried to proceed to the next record. You will also only be presented with the standard Microsoft Access Error message as shown below:

Standard Microsoft Access Error message, when trying to enter a duplicate key value.

You will then have to undo the record that you have just entered, and find the original record to make changes or additions. This can be time consuming and frustrating to the user.

The following example uses code in the Before Update event of a form control to prevent a user inserting a duplicate value that has been previously entered. When this is detected, the record is undone, and the user is taken to the original value. This happens as soon as you leave the field where you have attempted to enter the duplicate key value.

We will use a the following Microsoft Access form and subform, which provides a good example of why we would need to prevent the duplication from being entered:

Form showing the Student Number (Main Form) field, that we would like to prevent duplicates from being entered into.

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

Now when we attempt to enter a duplicate Student Number, we are presented with the following message box:

Warning message when attempting to enter a duplicate record.

When we click the OK, the duplicate record is undone, and we are taken to the Student that we tried to duplicate.

You can test this out by downloading the Microsoft Access 2000 example file from the Microsoft Access Downloads page or the Microsoft Access Forms page.