Check for duplicates based on Name

[chellebell1689]

Hello,
I have a form (MemberDetails) and I want it to check if the member I'm adding has already been added, or at least give me a warning that the member might be a duplicate. I have a FName field and LName field that I would like checked (together). I know it's possible because I've seen it done on the Address Book template, but they have it set up to where the First & Last name are saved as one field in another table and a bunch of other crazy stuff that I don't understand why they did it. I don't know VBA (learning that this semester).

In case someone wants to just modify the expression used in the template to suit my needs, here it is.

=IIf(DCount("*","[Contacts Extended]","[ENV_NUM]<>" & Nz([ENV_NUM],0) & " And [Contact Name] = '" & Replace(Nz([Contact Name]),"'","''") & "'")>0,"Possible Duplicate","")

Thank you in advance for the help!

P.S.
Let me know if you need more info or clarity.
Also, if this is a duplicate post, sorry. I tried searching but all I could find is based on the ID or how to do the duplicate query (and that's not it because they way I saw it built was through an expression).

 

[Ranman256]

have a query , qsNameCheck, that uses the text boxes as parameters in the query. We then check the count of records for duplicates...if there are some, show the list.

if Dcount("*","qsNameCheck","[FirstN]='" & txtFirstN & "' and [LastN]='" & txtLastN  & "'")>0 then
  docmd.openquery "qsNameCheck"
end if

 

[chellebell1689]

Thanks for that, and thanks for explaining it.

Quick question. The expression I entered in the OP was entered into a box in the top corner of the form and would show "Possible Duplicate" if the "new" member looked like a duplicate, how do I set this up?

 

[Ranman256]

do a FUZZY search for possible duplicate:

if Dcount("*","qsNameCheck","[FirstN] like '*" & txtFirstN & "*' and [LastN]like '*" & txtLastN & "*'")>0 then
docmd.openquery "qsNameCheck"
end if

 

[spikepl]

Here is the master's take on this:

http://allenbrowne.com/vba-Soundex.html

 

[chellebell1689]

Ok I tried the Soundex function, but I need to modify it so that it just shows "Possible Duplicate" in the set box if the current record is a possible duplicate. Also, how do I ask it to check for the first name too? Would I add the function to the Form Query again, just with "FName", instead of "LName"? Also, when I added the soundex to my form, it sits at "New" and won't let me go to any members. I think this is because it's trying to pull up two matches (that is if I understand what's going on with this...)?

 

[jdraw]

If you are concerned with "possible duplicates" and you are talking about member names, you could add addition checks on other fields
--phone number
--zip code
-- could also use the Like operator eg "*yourCriteria*"

to help discriminate "possible duplicates".

As for the error message

If Dcount (your criteria) > 0 then 'Possible duplicate

MsgBox "This is a possible duplicate......other info in your message...."
...
... code to do what you want in case of possible duplicate...

How many members are involved in your business?
What is the impact of a duplicate --that is how difficult and how often is this condition to process?

 

[chellebell1689]

This is for my church, and we currently have some where around 700-800 people in the database (this includes moved, transferred, deceased, visitors, etc.) I was going to modify it to check if they are Jr., Sr., III, whatever based on a third box.

I'm sorry to be difficult but I've only really worked with macros, and touched on expressions, and copied VBA. How would I tell it to put the message in a special text box on the form?

**Edit**
Sorry, forgot to add impact of duplicates. I want to make sure it checks for duplicates so that when we add in Sunday School roll for the week, and we add a visitor to the class the system will check for a duplicate so we don't add the same person over and over. It just makes our system more accurate.

 

[jdraw]

Can you post a copy of your database (zip format) --remove anything confidential first?
We don't need all the records necessarily- just enough to understand your set up.
I don't use macros. I'm interested in your tables and relationships along with a clear description of what you are trying to do.


After posting:
I looked at some of your other posts. I saw a database at
http://www.access-programmers.co.uk/forums/showpost.php?p=1431059&postcount=21 and saw that vbaInet gave you some advice. Hopefully your current database design has been updated to normalized structures. You really have to get your tables and relationships designed to meet your business needs if you are to have a useful database. Tables and relationships first, then the other parts will be easier to develop.

 

[chellebell1689]

Here ya go! I left some extra stuff because I couldn't remember what was used as a subform and whatnot. The form I'm working on is "Members Details" and the box I'd like the message to appear in is in the top right corner (can't see it unless your in design view, or there's text in the box).

Thank you so much for the help! And please excuse any mess you see, still new to this.

**Edit**
Yes, that one was a hot mess!! I started over after playing with the normalizing tutorial I was given. I believe I did MUCH better on this one, though I think I still might need work, but so far everything is going smoothly.

 

[jdraw]

Do you have a clear description of WHAT you are trying to do?
A statement of requirements or requirements document?

 

[chellebell1689]

As far as this question goes or for the database as a whole?

As far as this question goes:
I just want that text box in the top right corner of the Members Detail form to say "Possible Duplicate" or "There is already a member who matches this information", or something to let the user know that they might be adding someone again. Upon typing this and thinking of it, it would be nice if I could get it to open a little pop up to show some basic details about the possible duplicate. (After we get it to notice a duplicate, I can add that)

As for the database as a whole...
That's a hard one. The church staff is currently using a DOS database called Alpha v6. My pastor want's to bring the church to the 21st Century and wants me to recreate the Alpha db in Access. That's what I've been slowly doing (as I learn a more in depth usage of Access).

Hope that answers your question. Thanks again!

 

[chellebell1689]

I still need help with this...please?

 

[jdraw]

I'm going to look at your database.
Attached is an error I get when trying to open Member Details.

In a nutshell, first overview look at your form --too complex -too many details.

It may be meaningful to you and your users, but i don't find it intuitive.
I would have expected something to allow me to Enter a New Member, but I don't see such. A textbox in the upper right to identify a Possible Duplicate???

Give me some detailed instructions for

-adding a new whatever that is a possible duplicate.
-some sample data would also be helpful

I'm willing to help, but I'm not about to start from 0. Get me to thepoint where you're having an issue(s) and I'll try to assist.

 

[chellebell1689]

I'm so sorry, I forgot to explain how it all works and point out where I am at.

Ok so looking at the form, the top number is their EnvNum (or the key number for their record). Then it shows the name they go by. The "Go to" allows the user to look up a name and open their record. The next three buttons allow you to go to the next member, add a new member, and close the form (while saving it. I am working on a cancel button so that if they do start adding a duplicate, they can hit cancel and it cancels adding the new person). So when you hit New Member, it will pull up a blank form, you can fill in the info with whatever, the only thing is, in the top middle is a field called "Family ID" that is required to enter an address! And then to save the record, you must have them assigned to a Sunday School class (or at least have them as "N/A"). I've got myself added twice in the db provided. My main thing is I want it to check the first name, last name, and if there is a suffix. (That way it doesn't count a Jr. & Sr. as duplicates.) Oh and as for the box in the upper right, I have it set transparent back & border, so that it only shows when there is a possible duplicate (empty boxes annoy me). To edit/view this box, you must be in design view.

I believe that's everything...let me know if you need more info.

As for the error, just ignore that. It's for a subform in the Members Detail form (under the membership tab), but it's nothing you need for this. Its just shows if/when the Pastor/Deacon visited the member and what came about.

 

[jdraw]

Just got back to your database.
I looked at form design and saw your text box.
Went to form view,
added Michelle Dyane Carnahan
selected N/A from SundaySchool
then got the attached error

I do not know what triggered an attempt to save the record. Please clarify.

The form occupied more than my available 17" monitor and did not provide a way for me to go to the bottom of the screen. perhaps there is a button there that adds the record?

Access will have issues with field names containing embedded spaces or special characters (#).

It seems you have conditions that must be satisfied in order to Add a Member.

I selected the Only Family available and was asked if I wanted to save the changes to the form?? I clicked Yes and can see the new record (attached)

Tried to find your code --found you are using macros??
I don't us macros ==> converted the macros to code in my copy
Added error message/possible dups, then entered a new record
-Dyane Michelle Carnahan

and got the Tempvars error???

Then found that the names I had been using for your fields were incorrect.
Modified my BeforeUpdate code and then tried to add a duplicate record.

The error routine was executed , the PossDups box was filled as per the attached jpg.

Here is the duplicate check code I wrote and tested:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim sPossibleDup As String
    Dim nameCheck As String    'first middle and lastname for comparison
    nameCheck = Me.FName & Me.MName & Me.LName & Me.Suffix 'form info
    Debug.Print nameCheck ''see what is being attempted addition
    sPossibleDup = "Possible Duplicate"
    If DCount("*", "Members", "[FName] &  [MName] & [LName] & Suffix ='" & nameCheck & "'") > 0 Then
        Me.PossDups = sPossibleDup
    End If
End Sub

My advice/suggestions at this point:

-get someone who knows macros to assist you
-get your logic/business criteria better defined
-the data entry process is convoluted (in my opinion)
- you could/should get another forum member to review and comment and get another (more) opinion(s)

I would recommend you go back to the design. Restate and clarify all of your business facts. Get your tables and relationships set up to support your business facts. The task you have is a big undertaking and, again in my view, the forms are too complex-trying to do too much. The data entry flow is not intuitive, but perhaps with some training and a user manual your intended audience could work with it.

Part of my hesitancy may be because of my lack of familiarity with macros, but my gut feel is it is complex and processes/components not clearly defined.

Sorry if this sounds too negative.

Good luck with your project.

 

[chellebell1689]

The save is automatic, I didn't set that up, it must be a default with the access I'm using and must have stuck with it when I uploaded. The only thing I have set up is when you click a name in the go to field, next record button, new member button, and the close button. Those will tell the db to save the record.

The edges are set to resizeable, and every once in a while it does the same thing to me, I just click where the top of the form window is supposed to be and drag down a little, then it resizes itself.

The only field that has special characters is the record title field ("AutoName"), and that's just for the form to look pretty. The actual name fields are the ones I'm concerned about to check for duplicates. Also, the AutoName field isn't actually saved anywhere, it's only generated on the form.

As for the error, i don't know why you got that, I pulled up the db I posted and I'm not getting that error. Maybe it was a temporary glitch?

 

[jdraw]

I have been editing my latest post while you responded.

FamilyID# is the one I saw.
Parent/Guardian

See this free data model for how Student Roster is accommodated.

Your SSAttendance table is not Normalized and will cause other issues.

There is a relationship between Student and Class as per the data model, and attendance can be checked via the Roster table.

You have done a pile of work. Very commendable. As I said, it is a complex undertaking because of all of the "things" you are managing, the level of detail and the varied conditions involved.

 

[chellebell1689]

Thank you for your opinion/advice, I truly appreciate it! As I've said is some of my other posts, I'm still new to this. In school they cover tables, forms, reports, and queries, but it's all basic stuff. No relationships or macros/expressions/code. The only other thing they cover is how to create a letter in Word and use Access to fill certain areas. Everything else I've taught myself and learned from here.

As for the SS Attendance, I've seen that before and it helped me normalize (better), but it doesn't do what I need. I need the system to auto-generate my class roster so that I can just click if they were present or not. The way it's setup now I have to put in the date myself, but I have a query that fills in my table for me. I know it's wrong, but the only other way I was told was to write in the roster every time I take roll. I don't want to do that, let alone someone who barely knows computers.

As for the member detail form, the Pastor wants to be able to see all the details associated with each member at once.

I have tried posting my db to ask if it looked good as far as normalization goes, but the only replies I got were links to tutorials on normalization and was told if I study that I should be able to answer my own question.

Again, thank you for your help! Eventually I will have it complete and will have a pro take a look at it.

**EDIT**
I tested the code you provided and I keep getting
"Compile Error
Method or data member not found"
and it highlights Me.LName
I tried retyping that in and still got the error. I just copied the code you provided and pasted it in the form's before-update event. Was there something else I needed to do?

 

[jdraw]

Me.LName is the control on your form related to Lastname of the new member.

The code I provided was vba. I used the BeforeUpdate event of the form to see if there were some errors (duplicate names) where duplicate names based on your post were
FirstName MiddleName LastName and Suffix
-in the code-
[FName] & [MName] & [LName] & Suffix

I am using vba and you are using macros, that may be an issue but should not focus on LName - in my view.

Did you add the code as a module behind form?

You deleted a post which had in the reason for delete was: Found the error

Does that mean you have that code working?