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, Jan 10 '17 at 19:47
*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, Jan 10 '17 at 19:54
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, Jan 10 '17 at 20:05

score 0 · Answer 1 · answered Jan 22 '17 at 13:13

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

Used the code you posted however seems to be an Expected end statement error in Criteria = "[Barcode] = '" & Replace(BarCode, "'", "''") & "'") — CLS, Jan 23 '17 at 09:01
Yes, that's right, air code, sorry. You should be able to remove that closing parenthesis. — Gustav, Jan 23 '17 at 10:26

Prevent Duplicate Entries VBA

1 Answers1