Prevent Duplicate Entries VBA

Question

First off I am a bit of a novice when its comes to VBA, so everything I do is a bit of hit and miss but normally I eventually figure out the problem. However this time I have been stuck for days and can't seem to find the issue!

I have the following form and subforms with the below structure. (Access2013)

Main Form [Job Number]
Subform [Out2] (this is where a user scans a barcode into the relevant field)
Subform [DS] (this is where the scanned barcode from [Out2] creates a new record)
Subform [DS] fields : Id, Job No, BarCode, Description, Date, User

What I am trying to achieve with the code below, is in 'The Before Update' event of the [DS] BarCode field, the Dcount function will check the list of Barcodes already entered in the subform container [DS], and if there is more than one it will undo the duplicate entry. Unfortunately nothing is happening when a duplicate entry is entered. (not even errors)

P.S. Setting the table (No Duplicates) thing will not work for this DB.

Private Sub BarCode_BeforeUpdate(Cancel As Integer)

  Dim BarCode As String
     Dim strLinkCriteria As String
     Dim rsc As DAO.Recordset

     Set rsc = Me.RecordsetClone

     BarCode = Me.BarCode.Text
    strLinkCriteria = "[Barcode]=" & "'" & Replace(Me![BarCode], "'", "''")

     'Check Items Subform for duplicate BarCode
     If DCount("BarCode", "Forms![Job Number]![DS]", strLinkCriteria) > 0 Then

         'Undo duplicate entry
         Me.Undo
         'Message box warning of duplication
         MsgBox "Warning Item Title " _
              & BarCode & " has already been entered." _
              & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
         'Go to record of original Title
         rsc.FindFirst strLinkCriteria
         Me.Bookmark = rsc.Bookmark
     End If

     Set rsc = Nothing
End Sub

I don't really know Access, but going out on a limb here I'm pretty sure you need to set the Cancel parameter to False once you've identified the duplicate, and then exit the procedure. — David Zemens
Setting the table (No Duplicates) thing will not work for this DB - that "thing" would be a primary key, and if your database was normalized, it should "just work". Normalize your data, fix the problem at the source. If Access supports unique constraints (aka "natural keys"), add that. It's specifically the job of the database schema to ensure uniqueness and data consistency/integrity. IMO needing to do that with code means you have a problematic schema that needs fixing. — Mathieu Guindon
Yep, this is a database design issue, not a code issue. Any attempt to enforce code only constraints on a database is doomed to fail. If your backend doesn't support the constraint you need, find an appropriate backend that does or redesign your schema in a way that makes it possible. — Comintern

Gustav Gustav · Accepted Answer · 2017-01-22T13:13:44

Here is how to handle this:

Private Sub BarCode_BeforeUpdate(Cancel As Integer)

    Dim rsc As DAO.Recordset
    Dim BarCode As String    
    Dim Criteria As String

    Set rsc = Me.RecordsetClone

    BarCode = Nz(Me!BarCode.Value)
    Criteria = "[Barcode] = '" & Replace(BarCode, "'", "''") & "'")
    rsc.FindFirst Criteria
    Cancel = Not rsc.NoMatch

    If Cancel = True Then
        ' Message box warning of duplication
        MsgBox "Warning Item Title " _
            & BarCode & " has already been entered." _
            & vbCrLf & vbCrLf & "You will now been taken to the record.", _
            vbInformation, "Duplicate Information"
        ' Go to record of original Title
        Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing

End Sub

Prevent Duplicate Entries VBA

1 Answers