Check For Duplicate Name Before Creating Record

[AMD_GAMER]

Hi,
I currently have a database that keeps track of patients. Before a
new patient is added as a record, I want to check and make sure there
is no other person in the database with the same first and last name.
Obviously, one or the other is fine, but I don't want 2 people with
the same name "John Smith." The data is inserted via a form with two
fields called [First Name] and [Last Name]. How can I check this
before the record is submitted via the "Submit" button?

 

[Jeff Boyce]

So, you don't care if two people actually have the same name, you're going
to force one of them to change his/her name? <g>

What if you have a father and son as patients who share the same name?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[AMD_GAMER]

So, you don't care if two people actually have the same name, you're going
to force one of them to change his/her name? <g>

What if you have a father and son as patients who share the same name?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,
I currently have a database that keeps track of patients. Before a
new patient is added as a record, I want to check and make sure there
is no other person in the database with the same first and last name.
Obviously, one or the other is fine, but I don't want 2 people with
the same name "John Smith." The data is inserted via a form with two
fields called [First Name] and [Last Name]. How can I check this
before the record is submitted via the "Submit" button?

If that is the case, the user could add a middle initial to the name.
When performing queries and reports, I don't want two people to be
displayed.

 

[Linq Adams via AccessMonster.com]

Having done programming in a medical environment for over a decade I can tell
you that you're going to have to do better than adding a middle initial to a
first and last name! No matter how odd a first and last name, you will run
across duplicates! You've really got to add some unique piece of Identifying
data to the patient name. A DOB field would cut down the odds of having two
individuals with the same three fields identical, but a SSN or PtID number
would be even better.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[Linq Adams via AccessMonster.com]

Sorry! Forgot your original question! I'd use DLookUp() or DCount() to check
and see if the particular patient is already in your table. Since the data to
be checked would be in multiple controls, you really need to do this in, I
think, in the Form_BeforeUpdate event.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[John W. Vinson]

If that is the case, the user could add a middle initial to the name.
When performing queries and reports, I don't want two people to be
displayed.

I once worked with Dr. Lawrence David Wise and his colleague Dr. Lawrence
David Wise. They both had middle initials, middle names in fact; they happened
to be the same.

Names ARE NOT UNIQUE. That's a fact of life. If you have two people who have
the same name, you must - no option!!! - accommodate your system to that fact,
not arbitrarily change people's names to accommodate them to your system.

The people's names are not the problem. Your arbitrary decision to insist that
different people must have different names IS the problem.

John W. Vinson [MVP]

 

[AMD_GAMER]

Sorry! Forgot your original question! I'd use DLookUp() or DCount() to check
and see if the particular patient is already in your table. Since the data to
be checked would be in multiple controls, you really need to do this in, I
think, in the Form_BeforeUpdate event.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

Actually, you made a great point. The form has SSN....I don't why I
didn't think of that first. Haha. Thanks.

 

[AMD_GAMER]

Actually, you made a great point. The form has SSN....I don't why I
didn't think of that first. Haha. Thanks.

Since the forms are set up to search for a patient using First and
Last Name (which makes sense), checking the SSN would not actually
solve the problem. You could still have 2 patients named "John Smith"
but with different SSN. I know the best way to search for a patient
would be to use the ID or SSN...but the receptionist obviously isn't
going to know those values off the top of her head. Is there a way to
alert the user that both the First and Last Name are already in the
database. What code would be needed for this?

 

[John W. Vinson]

Actually, you made a great point. The form has SSN....I don't why I
didn't think of that first. Haha. Thanks.

Bear in mind that some people don't have SSN's; some people have fake
(possibly duplicate) SSN's; it's illegal to require a SSN; and even at that
the Social Security Administration has issued duplicate SSN's.

Use at your own risk.

John W. Vinson [MVP]

 

[AMD_GAMER]

Bear in mind that some people don't have SSN's; some people have fake
(possibly duplicate) SSN's; it's illegal to require a SSN; and even at that
the Social Security Administration has issued duplicate SSN's.

Use at your own risk.

John W. Vinson [MVP]

I am going to perform a DLookup on the SSN to see if it is already in
the database. I am not requiring a SSN...simply looking for a
duplicate.

 

[Bob Quintal]

@i12g2000prf.googlegroups.co
m:

Hi,
I currently have a database that keeps track of patients.
Before a
new patient is added as a record, I want to check and make sure
there is no other person in the database with the same first and
last name. Obviously, one or the other is fine, but I don't want 2
people with the same name "John Smith." The data is inserted via a
form with two fields called [First Name] and [Last Name]. How can
I check this before the record is submitted via the "Submit"
button?

My father and I object! we are both Robert Quintal on our identity
papers.

You need to add an identity number to your patients table and use
that as criteria for unicity. You use the PatientID as primary key
in the patients table and as a foreign key into your other tables to
referecne the correct patient.

 

[John W. Vinson]

Hi,
I currently have a database that keeps track of patients. Before a
new patient is added as a record, I want to check and make sure there
is no other person in the database with the same first and last name.
Obviously, one or the other is fine, but I don't want 2 people with
the same name "John Smith." The data is inserted via a form with two
fields called [First Name] and [Last Name]. How can I check this
before the record is submitted via the "Submit" button?

If you want to *ALLOW* duplicates (which I'd very much recommend), but just
want to give the user a "heads-up" that there is already at least one John
Smith in the database... you can use the Form's BeforeUpdate event to detect
the situation. Sample code might be

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim strSQL As String
Dim iAns As Integer
If Me.NewRecord Then ' only check for new additions
strSQL = "[LastName] = """ & Me!txtLastName _
& """ And [FirstName] = """ & Me!txtFirstName & """"
Set rs = Me.RecordsetClone ' get the form's recordset
rs.FindFirst strSQL ' find this person's name
If rs.RecordCount > 0 Then
iAns = MsgBox("There is a patient of this name already " _
& "at " & rs!Address1 & ", " & rs!Street & " " & rs!City _
& "Select Yes to add record anyway, No to use existing record, " _
& "Cancel to erase and start over:", vbYesNoCancel)
Select Case iAns
Case vbYes
' do nothing
Case vbNo
' jump to the found record
Cancel = True
Me.Bookmark = rs.Bookmark
Case vbCancel
Cancel = True
Me.Undo
End Select
End If
End If
End Sub

Untested air code; needs error checking, a better display of other information
to match the patient, etc. but should get you started.

John W. Vinson [MVP]