checking for duplicate entries in database

[Steve]

The database works fine except that people are entering duplicate patients
into it. I do not have enough expertise to code a checking routine. I have
created a field called NameCheck that consists of the Last Name field and
the First Name field concatenated with a space between them. Like Smith
John.

What I would like to have happen is after the patients first name is
entered, the last name is first, the code checks to see if there is already
someone there by that name. If there is then a form would open listing all
the patients that match both first and last names. I have written the query
and created the form but cannot get the NameCheck to pass as a parameter
when I open the form.

TIA for any help on this

Steve

 

[Allen Browne]

Steve, adapt the code below to your needs.

It checks that both name fields have a value that has not been edited back
to the same as it was (an unchanged existing record is not a duplicate of
itself). Then it builds a Where string and uses DLookup() on the primary key
of your table. That returns Null if there is no match. If it is not null, it
opens your form filtered to the same condition (so the form shows matches
only) and pops up a message.

Finally, you want to run exactly the same code in the AfterUpdate of the
last name.

Private Sub FirstName_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
Const strcForm = "NameOfYourDuplicatesFormHere"

If IsNull(Me.[LastName]) OR IsNull(Me.[FirstName]) Or _
(Me.[LastName] = Me.[Lastname].OldValue AND _
Me.[FirstName] = Me.[FirstName].OldValue) Then
'do nothing
Else
strWhere = "([LastName] = """ & Me.LastName & _
""") AND ([FirstName] = """ & Me.FirstName & """)"
varResult = DLookup("ClientID", "tblClient", strWhere)
If Not IsNull(varResult) Then
If CurrentProject.AllForms(strcForm).IsLoaded Then
With Forms(strcForm)
If .Dirty Then .Dirty = False
End With
DoCmd.Close acForm, strcForm
End If
DoCmd.OpenForm strcForm, WhereCondition:=strWhere
MsgBox "Possible duplicate."
End If
End If
End Sub

Private Sub LastName_AfterUpdate()
Call FirstName_AfterUpdate
End Sub

Notes
====
1. For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

2. If the quotes do not make sense, see:
http://allenbrowne.com/casu-17.html

3. The OpenForm with the filter would not work if the form was already open,
so it closes it in that case.

4. You probably want to disallow edits in your "duplicates" form (to prevent
concurrency problems), but the code checks whether the "duplicates" form is
Dirty to avoid this bug:
http://allenbrowne.com/bug-01.html