Preventing duplicate records in Access 2013

Question

I've looked at the below question which is exactly what I am trying to do, however the posted answer doesn't seem to work for me. My query is essentially the same, but I will elaborate.

I have a form which updates a table of information with the following:

Forename
Surname
EmailAddress

The database also adds DateEntered automatically via the =Date() in the default value setting of the field, and there is a primary key auto number called CPDEAID.

I added the following code to the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Set rst = Me.RecordsetClone
    rst.FindFirst "[CPDEAID] <> " & Me.CPDEAID & " AND [Forename] = " & Me.Forename & " AND [Surname] = " & Me.Surname & " AND [EmailAddress] = " & Me.EmailAddress
    If Not rst.NoMatch Then
        Cancel = True
        If MsgBox("This person already exists; would you like to go to the existing record?", vbYesNo) = vbYes Then
            Me.Undo
            DoCmd.SearchForRecord , , acFirst, "[CPDEAID] = " & rst("CPDEAID")
        End If
    End If
    rst.Close
End Sub

However, this doesn't seem to work. The only way that I can get my database to not create the duplicate record is via creating a multi-column index - but this is a little messy as I want a clean "user-friendly" front end.

Am I missing something very simple here?

Prevent Duplicate Records, Query Before Creating New Records

I am not sure what you mean by multi-column index making it little messy and not clean "user-friendly" front end? Does it cause pop-up warnings that you don't want the users to see? — Slai, Aug 21 '16 at 21:45
Add the field as a primary key or set a multi-column index. Either trap the error number that's produced when a duplicate is about to be added, or do something like a `DCount` to determine how many records exist, and when it's greater than 0, display the message. — Ryan Wildry, Aug 21 '16 at 23:29

score 0 · Answer 1 · edited May 23 '17 at 12:23

It probably doesn't work because in your rst.FindFirst you build an SQL string that compares text columns, but you don't use single quotes around the parameters.

But you shouldn't do that either with user input, because that risks SQL injection.

Instead, use a parameter query as shown here, or use an utility function like this in a standard module:

' DLookupPar: Look up a value with a parameterized query
' The first 3 parameters are identical to DLookup, but in Criteria you can use parameters.
' Then add the exact number of parameters as following arguments, in the exact order as used in <Criteria>
'
' Sample call:
' varUserID = DLookupPar("UserID", "tUsers", "Username = [parUser] AND [Password] = [parPassword]", Me!Username, "aPassword")
'
Public Function DLookupPar(Expr As String, Domain As String, Criteria As String, ParamArray arParams() As Variant) As Variant

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim strSelect As String
    Dim i As Long

    strSelect = "SELECT " & Expr & " FROM " & Domain & " WHERE " & Criteria
    'Debug.Print strSelect

    Set db = CurrentDb
    ' Create temporary querydef (no name) and set all parameters from arParams
    Set qd = db.CreateQueryDef("", strSelect)
    For i = LBound(arParams) To UBound(arParams)
        qd.Parameters(i) = arParams(i)
    Next i

    Set rs = qd.OpenRecordset(dbOpenSnapshot)
    If Not rs.EOF Then
        ' Return the first and only column
        DLookupPar = rs(0)
    Else
        DLookupPar = Null
    End If
    rs.Close

End Function

and use it like this in your form:

Dim ID As Long
Dim rs As Recordset

ID = Nz(DLookupPar("CPDEAID", "yourTable", _
    "[CPDEAID] <> [parCPDEAID] AND [Forename] = [parForename] AND [Surname] = [parSurname] AND [EmailAddress] = [parEmailAddress]", _
    Me.CPDEAID, Me.Forename, Me.Surname, Me.EmailAddress), 0)

If ID > 0 Then
    ' MsgBox etc
    Set rs = Me.RecordsetClone
    rs.FindFirst "[CPDEAID] = " & ID
    Me.Bookmark = rs.Bookmark
    rs.Close
End If

Preventing duplicate records in Access 2013

1 Answers1