Duplicate Customer Information...

spalmateer

Registered User.
Local time
Today, 13:21
Joined
Dec 5, 2000
Messages
46
Hello,
I have a customer form with personal information (ie name, address, phone, ect.). I am trying to create a control that dosen't allow duplicate customer information to be entered. For example if an address is entered already in the customer table- then if someone enters the same address in the form a window pops up telling the user that that address has already been entered. Is there an easy way to accomplish this? I know that I could probably do this with composite keys but often we have a customer address with no name and vice versa. Currently my primary key is an autonumber cust number. Thanks in advance for the help and insight!
 
In the Before Update event of the field that you want to check for duplicates you could use DLookup to see if the information entered into the field already exists in the table. If it does then cancel the update and inform the user that the data already exists.
 
You could use the Dcount to look for the name like this. If the value (count) = 0 (no matches) the new name will be allowed. If the value = 1 a message will pop up saying the name has allready been entered. Try this:

Private Sub txtName_BeforeUpdate(Cancel As Integer)

Dim Answer As Variant

If DCount("[Name]", "YourTable", "[Name]= '" & Me!txtName & "'") = 1 Then

Answer = MsgBox("Name is allready on the list", vbOKOnly, "Hey You!")
If Answer = vbOK Then Cancel = True

Exit Sub

End If

End Sub

Post back if you have any more ?'s
 
I'm sorry- I hate to be a bother but I'm still fairly new to this. I'm having a little trouble with the argument. Do I type that string in exactly as seen or where do I enter my names? My form name is Customer Information Form-Main. My field name is Last Name and the information is stored in table Customer Information. Also what happens if it returns more that 1 or 2 values like in the case of a common last name. Maybe I should just use this on the address field since that would be more unique. Would there be a way to enter a record even if the message came up? Where do I enter the module - before update? Thanks so much for your help!
Scott

[This message has been edited by spalmateer (edited 12-11-2000).]

[This message has been edited by spalmateer (edited 12-11-2000).]
 
You should put your own field names in there. The [Name} is the value you are checking, [YourTable] would be the table where the information is located, The second [Name] is used to set your equals to what ever is in the [txtName].

You are right about the problem with common last names, it will not let you enter them. Address may work but what if you need to enter multiple people from the same address? You may try concatenating the fields so that Bob Jones will be JonesBob to Access and it will check against both names together as one value. I will work on this and post the code back if you are interested in this as a possible solution.
 
I thought of another possible solution. This will check the records to see if there is a matching first and last name before it stops the action.

Private Sub txtLast_Exit(Cancel As Integer)

Dim Answer As Variant

If DCount("[LastName]", "YourTable", "[LastName]= '" & Me!txtLast & "'") = 1 _
And DCount("[FirstName]", "YourTable", "[FirstName]= '" & Me!txtFirst & "'") = 1 Then

Answer = MsgBox("Name is allready on the list", vbOKOnly, "Hey You!")
If Answer = vbOK Then Cancel = True

Exit Sub

End If

End Sub
 
Oops, nevermind the above worked the first time I tried it but once I re-tried it repeat first and last names were being accepted. I am not sure what the deal is but obviously it is not going to work the way I wanted, Sorry!
 
Thanks Talismanic for the help!
I've tried entering that code before update on the phone field but it's not working. I'm attempting to use this on phone because it should be unique. Here's how I entered it in:

Private Sub Phone_BeforeUpdate(Cancel As Integer)


Dim Answer As Variant

If DCount("[Phone]", "Customer Information", "[Phone]= '" & Me!Phone & "'") = 1 Then

Answer = MsgBox("Name is allready on the list", vbOKOnly, "Hey You!")
If Answer = vbOK Then Cancel = True

Exit Sub

End If

End Sub

What am I doing wrong? I might have some of the names listed wrong. The table that the phone field is stored in is called: Customer Information. The form name is: Customer Information Form-Main. The field name is Phone. Thanks again for your help!
Scott

[This message has been edited by spalmateer (edited 12-14-2000).]
 
Hmmmm, I am not sure. I cut and pasted the code that you listed and made a table with the same name you used and put a phone number in the table. Opened the form typed in the phone number that I put in the table and I got the pop up message, changed it by one digit and it let it go through.

You might want to make sure that the field in the table is named Phone and in general it is not a good idea to use spaces in object names if you are going to use code with them.

But that is not the problem here because I cut and pasted your code as is and it worked fine. Maybe because there are quotes around the name.

What is the exact error that you are getting?
 
I'm actually not getting an error message. It's just not doing anything when I reenter a duplicate phone number. Could it have something to do with the input mask that I have on the phone number? Does it matter whether it is entered before update on the form properties or the field phone properties? Thanks again for the help!
 
The input mask didn't make any difference in my app. The code should work as you posted because it works in mine. I have it in the Before_Update event of the phone text box. It looks like this:

Private Sub Phone_BeforeUpdate(Cancel As Integer)

Dim Answer As Variant

If DCount("[Phone]", "Customer Information", "[Phone]= '" & Me!Phone & "'") = 1 Then

Answer = MsgBox("Name is allready on the list", vbOKOnly, "Hey You!")
If Answer = vbOK Then Cancel = True

Exit Sub
End If

End Sub

Is your database small enough to zip up and send to me?
 
Yeah I'll email it to you. It's only like 700K. It's awefully kind for you to take your time to help me- Thanks!
Scott

[This message has been edited by spalmateer (edited 12-14-2000).]
 
No problem, nine months ago I was given the assignment of building a payroll system using Access and I had never even heard of a relational database. I have come a long way because of this and one other board and I am very happy that I can return some of what I learned back to the community.

The problem you were having with the Dcount was that the field names were not being evaluated correctly. The fields on the left side of the equation should use your table names and the fields on the right should use the forms names. I put the code below in the Before_Update Event of the Phone text box and it worked as it should.

Dim Answer As Variant

If DCount("[Home Phone]", "tblCustInf", "[Home Phone]= '" & Me!Phone & "'") = 1 Then
Answer = MsgBox("Name is allready on the list", vbOKOnly, "Hey You!")
If Answer = vbOK Then Cancel = True
Exit Sub

End If

When you build your tables you should follow the naming conventions. It will become more inportant to you as you work with more code. For instance, the Home Phone field should be HomePhone.

Looking at your database I would recomend that you turn those macros into vba. Read the second post from this thread about macros and vba. I asked this question when I was just getting started with Access.

I have to go now but I will get back to you with some additional information dealing with your database if you would like.

[This message has been edited by Talismanic (edited 12-15-2000).]
 
Here is a quick and dirty way to "learn" VBA. Just don't rely on it totaly because Access doesn't allways convert the code in the most eficiant way. But it will get you started and you will pick up on a lot of the code faster.

Read the last post by me about converting macros to vba here: simple terms for visual basic
 
spalmateer, did you get the database I sent you back. I am at home now and don't have access to that email address so I am not sure if you have replied or not.
 
Hi Talismanic,
Yeah I got the database. That code worked great! I really appreciate it. I'm still having a problem with my form/subform. When I go to my subform the customer number is not updating. I have linked these forms using the wizard. My other subforms work but my Model/Serial Number subform doesn't. If you still have a copy of my database could you take a look? Thanks Again for the help! Also my email is sep1280@aol.com
Scott
 
why did you not just go into design view, choose the field, select index (no duplicates)???????????????? and save yourself the hassle of V.B.?
 
VBA? Hassle? That is blasphemy around here!

No, really using VBA in this, as in most situations allows you to have more control over your form/database. For instance the use of a pop up menu with a specific message to the user.

Or you may decide that you want to allow duplicate records in some situations. With code you can plan and manage those situations.
 

Users who are viewing this thread

Back
Top Bottom