Avoiding duplicate records without using primary key

[Ed Mattison]

I have a modest sized data base and have been having trouble keeping out duplicate records with the same last name, first name, and DOB. I know I could use all 3 fields as a primary key, but I would like a simpler way of checking for duplicates. Is there such? Thanks. ed

[LaughingBull]

create a query watch this youtube https://www.youtube.com/watch?v=amRbEhQWH78

[orange]

Build a unique composite index using the 3 fields.

[JeroenMioch]

Try this code :

Dim strSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb


strSQL = "SELECT * FROM tblPersons" & _
" WHERE FirstName = '" & Me!ctrlFirstName & _
"' AND LastName = '" & Me!ctrlLastName & _
"' AND DateOfBirth = '" & Me!ctrlDateOfBirth & "


Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If Not rst.EOF Then
If MsgBox("Person " & Me!ctrlFirstName & " and " _
& vbCrLf & "with " & Me!ctrlLastName & "" _
& vbCrLf & "and date of birth  " & Me!ctrlDateOfBirth & " allready exists" _
& vbCrLf & "Click NO to cancel", _
vbCritical + vbYesNo + vbDefaultButton2) = vbNo Then
Me.Undo
Exit Sub
End If
End If

'If no match, code to create your new record below.