Duplicates Check with Msg Box alert

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello
I have a form that is used to enter patient data. The first 4 text boxes are
ID (auto numbered), Last Name, First Name, and Date of Birth. I am trying to
figure out how to code the form to check for duplicates based on Last Name,
First Name and Date of Birth...and if there already exists a match, then an
message box would "pop-up" that says "Patient is already in system". The goal
is to prevent entering the same patient twice and thus creating duplicates.
(I am able to generate duplicates reports, but I am trying to nip it in the
bud, if possible.)
Any help is greatly appreciated.
 
The best way I can think of is to create a multi-field primary key in the
table.
Remove the primary key from the ID field...if necessary.
Select the Last Name, first Name and DOB fields then click Primary Key.
Make sure that the field is Indexed (No Duplicates)...should be if it is
primary key.

That will prevent any duplicates from being entered...theorhetically!
Now you run into the problem: what happens if they spell the name wrong the
second time? Names & Addresses are always hard to validate for duplicates.
 
Kathy

Nice try, but...

A (true) cautionary tale -- a fellow I work with was arrested for
outstanding warrants in a county 90 miles away. After considerable effort,
he was able to show that he was NOT the John Smith born on 1/1/1950 that the
police were looking for... (name changed to protect the truly innocent!).
He shared a name and date of birth with someone else that the police REALLY
wanted to meet.

Basing your decision about unique patients solely on their names and DOBs is
risky.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hello
I have a form that is used to enter patient data. The first 4 text boxes are
ID (auto numbered), Last Name, First Name, and Date of Birth. I am trying to
figure out how to code the form to check for duplicates based on Last Name,
First Name and Date of Birth...and if there already exists a match, then an
message box would "pop-up" that says "Patient is already in system". The goal
is to prevent entering the same patient twice and thus creating duplicates.
(I am able to generate duplicates reports, but I am trying to nip it in the
bud, if possible.)
Any help is greatly appreciated.
Click to expand...

You would need to check once each of these fields (Last Name, First
Name, Date of Birth) has been entered. Code would have to be added to
the beforeupdate event of each of these fields testing if 1) all three
fields have been entered, and 2) if a similar record already exists.
That way if they are not entered in order, or if the user goes back
and changes one of the fields, the program will do the check again.
 
Thank you for your reply. (I am pretty much a newbie, just so you know...if
it's not obvious. ) Anyway, so far I have changed the primary key to "primary
keys" as you suggested. But I cannot index for 'no duplicates' as some
patients already have the same last name ...SMITH, John...SMITH, Robert ...or
same first name...etc. When I highlight all three keys, the field property
box below is empty...so I am wondering if there is some other way I can put
all 3 together and set to no duplicates? In other words, duplicates are
allowed unless all 3 match. Hope this makes sense.--
Thank you again,
Kbelo
 
Your approach requires the user to enter values in all three fields, THEN
tests and informs the user that s/he can't do that. This could be
considered by some to be rather unfriendly.

Another approach...

If you use a combo box (unbound) and use a query as the RowSource, you can
concatenate the LastName, FirstName DOB in the query and all the user would
need to do is start typing the last name. Access can autocomplete and show
the first record in the table that matches as each new letter is typed into
the combo box.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I believe that this is what I want to do...since I am a novice I am not
confident on how to write this code, but I will give it a try...and see if I
can figure it out. Thank you for your help.
 
Hello again,
I am trying to follow your suggestions and using some examples from other
threads. So far I have a simplier (one field check) version that is working
correctly. Here is that code:


Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & "'"

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "Warning Possible Duplicate Record", vbInformation, "Duplicate
Information"


End If

End Sub

But I am having problems when I try to add more fields to check...I seem to
get lost in all the quote marks...but I am wondering if you can see where I
am going wrong. Here is the multiple field code:

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & " 'And strFirstName = ' " &
Me.strFirstName.Value & " 'And dtmDOB=" '& Me.dtmDOB.Value &"'"

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will now be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark

End If

End Sub

Thank you for all your help...
 
Back
Top