I would like to have either a message box or form popup when I enter a new record and already have an existing record that has the same first and last name. For example, If I enter Tom Jones and I already have a record for a Tom Jones, the message would let me know that and show me his address so I could tell if it was the same person. How can I do this? thanks!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Duplicate record notification 3
- Thread starter storer
- Start date
What is the primary key for your table?
Is it possible/likely that you will have a person that has the same first and last name?
If you make first and last name a double primary key, then you would be given an error message each time you tried to enter a name that already exists in the table. Or if you have a unique number for each person, such as a social security number, you could make this the primary key so you are only allowed to enter a social security number once.
I'm not sure if this is what you want to do, but maybe it will help.
Mike Rohde
rohdem@marshallengines.com
Is it possible/likely that you will have a person that has the same first and last name?
If you make first and last name a double primary key, then you would be given an error message each time you tried to enter a name that already exists in the table. Or if you have a unique number for each person, such as a social security number, you could make this the primary key so you are only allowed to enter a social security number once.
I'm not sure if this is what you want to do, but maybe it will help.
Mike Rohde
rohdem@marshallengines.com
LarryDeLaruelle
Technical User
In the absence of SSN, if you are gathering locator information (Address, Phone, etc), as it seems you are, you can create a useable PK by combining part or all of the phone number with part or all of the zip code or parts of those numbers and parts of the name depending on how creative you want to be and how complex you want to make the PK.
You would need to consider whether more than one person at the same address would be likely to be added to your db.
Mike, is it possible to trap for the error received when trying to save a record with an existing PK? If so, storer could use that to generate a message box to indicate this to the user.
Hope you know how as I would like to be able to add that to some of my input forms.
Thanks.
Larry De Laruelle
larry1de@yahoo.com
You would need to consider whether more than one person at the same address would be likely to be added to your db.
Mike, is it possible to trap for the error received when trying to save a record with an existing PK? If so, storer could use that to generate a message box to indicate this to the user.
Hope you know how as I would like to be able to add that to some of my input forms.
Thanks.
Larry De Laruelle
larry1de@yahoo.com
-
2
- #5
TheInsider
Programmer
Hi,
In the table, highlite both the first and last name fields at the same time and make them primary keys. Then in the On Error code for the entry form, place the following code:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If (DataErr = 3022) Then
'custom message goes here, or do something else if
'desired.
MsgBox "Do something!"
Response = acDataErrContinue
End If
End Sub
That should be the right error number for primary and secondary key violations as well (I have only tried it with one primary key).
Rob Marriott
rob@career-connections.net
In the table, highlite both the first and last name fields at the same time and make them primary keys. Then in the On Error code for the entry form, place the following code:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If (DataErr = 3022) Then
'custom message goes here, or do something else if
'desired.
MsgBox "Do something!"
Response = acDataErrContinue
End If
End Sub
That should be the right error number for primary and secondary key violations as well (I have only tried it with one primary key).
Rob Marriott
rob@career-connections.net
-
1
- Thread starter
- #7
thanks for your replies! As this is a database that holds info on groups and families that come to camp events, I can't really use name as a primary key. Always run the chance of 2 tom smiths. SSN won't work either since collecting data for groups and individuals alike. And to be real honest, I think I would have a hard time getting folks to fill that out on a registration form. Getting complete information is difficult...especially when we are now asking them to register for a weekend event that they have been coming to for years and didn't have to fill out forms for before! that's a whole new issue.
Anyway, the database starts with a table that has an autonumber (key) and first and last/organization name. This has a one to many relationship to a members table that records the "members" of the group and/or family. Each of those records has a one to many to an address table, since we are trying to collect work, home, away at college, etc...addresses. I want to show family relationships, as well as relationships of individuals to organizations. i.e. Tom Smith comes to our volunteer work weekend, is the leader of the scout troop that comes in the fall and his wife comes to women's wellness weekends with her daughter. This information will also be used for alumni/fundraising purposes so am interested in history. Unless there is a way that I can have an PK using fields from more than one table, I can't really use address, phone number, etc... I really don't think you can have a PK like that but I've learned that almost anything you can think of is possible with access if you stare at it long enough!
I hunted around the threads and came up with the following. If I enter the name smith in last/org name field and already have a smith or two, a form will open that shows me all the smiths in the house/org table and their address so I can tell if I have already entered that same person. What would be really neat is if I could get the form to show not only smiths, but smithson, smitt, etc...so if the name isn't entered exactly the same you still can see that you might have a duplicate. this is what I have so far:
Private Sub Last_Org_Name_BeforeUpdate(Cancel As Integer)
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("household/org table"
Do Until rst.EOF
If rst![Last/Org Name] = Me![Last/Org Name] Then
GoTo theend
Else
rst.MoveNext
End If
Loop
Exit Sub
theend:
DoCmd.OpenForm "find hse/org duplicate"
Dim Msg, Style, title, response, mystring
Msg = "This may be a duplicate record. Do you want to enter a new record?" 'define message.
Style = vbYesNo + vbCritical 'define buttons.
title = "Duplicate record warning" 'Define title.
'Display message.
response = MsgBox(Msg, Style, title)
If response = vbNo Then
Cancel = True
End If
DoCmd.Close acForm, "find hse/org duplicate", acSaveYes
End Sub
The form's query has the last/org name field on the form as a parameter. so if I enter smith, all the smiths are returned. I tried using like[forms]![hse/org]![last/org name]* with " and ( every which way but loose and couldn't get what I wanted. Any suggestions?
Anyway, the database starts with a table that has an autonumber (key) and first and last/organization name. This has a one to many relationship to a members table that records the "members" of the group and/or family. Each of those records has a one to many to an address table, since we are trying to collect work, home, away at college, etc...addresses. I want to show family relationships, as well as relationships of individuals to organizations. i.e. Tom Smith comes to our volunteer work weekend, is the leader of the scout troop that comes in the fall and his wife comes to women's wellness weekends with her daughter. This information will also be used for alumni/fundraising purposes so am interested in history. Unless there is a way that I can have an PK using fields from more than one table, I can't really use address, phone number, etc... I really don't think you can have a PK like that but I've learned that almost anything you can think of is possible with access if you stare at it long enough!
I hunted around the threads and came up with the following. If I enter the name smith in last/org name field and already have a smith or two, a form will open that shows me all the smiths in the house/org table and their address so I can tell if I have already entered that same person. What would be really neat is if I could get the form to show not only smiths, but smithson, smitt, etc...so if the name isn't entered exactly the same you still can see that you might have a duplicate. this is what I have so far:
Private Sub Last_Org_Name_BeforeUpdate(Cancel As Integer)
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("household/org table"
Do Until rst.EOF
If rst![Last/Org Name] = Me![Last/Org Name] Then
GoTo theend
Else
rst.MoveNext
End If
Loop
Exit Sub
theend:
DoCmd.OpenForm "find hse/org duplicate"
Dim Msg, Style, title, response, mystring
Msg = "This may be a duplicate record. Do you want to enter a new record?" 'define message.
Style = vbYesNo + vbCritical 'define buttons.
title = "Duplicate record warning" 'Define title.
'Display message.
response = MsgBox(Msg, Style, title)
If response = vbNo Then
Cancel = True
End If
DoCmd.Close acForm, "find hse/org duplicate", acSaveYes
End Sub
The form's query has the last/org name field on the form as a parameter. so if I enter smith, all the smiths are returned. I tried using like[forms]![hse/org]![last/org name]* with " and ( every which way but loose and couldn't get what I wanted. Any suggestions?
- Thread starter
- #9
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question