Duplicate names

  • Thread starter Thread starter jaylt
  • Start date Start date
J

jaylt

I have a mailing list that I use to print mailing lables with. The trouble I
am having is duplicate entries of the names and addresses. This database is
split, address.mdb and address_be.mdb. Most all the data entry is done
though a form in address.mdb.
Is there a way to get Accress to test for the first and last names dup
before allowing the dup to be saved? Or just even to check for a name?

Thank You,
Jay
 
There are some coding methods to do this trhat will take
some explination, you could also make a compound primary
key that would have both the First and Last Name as part
of the key. If you try to insert a duplicate of both
names, you will get an error. You may want also to add
middle initial just in case you do have two Fred Smiths.

Before you try to make this happen, you will need to clean
up your database and get rid of the duplicates.

Post back if you need more help.

Kevin
 
I have a mailing list that I use to print mailing lables with. The trouble I
am having is duplicate entries of the names and addresses. This database is
split, address.mdb and address_be.mdb. Most all the data entry is done
though a form in address.mdb.
Is there a way to get Accress to test for the first and last names dup
before allowing the dup to be saved? Or just even to check for a name?
Click to expand...

Sure; you do need to just *check* not prohibit - names are not unique!
I used to work with Dr. Lawrence David Wise and his colleague, Dr.
Lawrence David Wise.

I'd suggest just a little bit of VBA code in the data entry form's
BeforeUpdate event: something like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
Set rs = Me.RecordsetClone ' get the form's recordsource
rs.FindFirst "[Lastname] = " & Chr(34) & Me!txtLastName & Chr(34) _
& " AND [FirstName] = " & Chr(34) & Me!txtFirstName & Chr(34) _
& " AND [ID] <> " & Me!txtID
' using your own field and control names of course; ID is the
' Primary Key field, just want to be sure the code doesn't find
' the same record you're checking for duplicates as a duplicate!
If Not rs.NoMatch Then ' if there is a match
iAns = MsgBox("Duplicate name found! Click Yes to add anyway," _
& vbCrLf & "No to cancel this record and open the found one," _
& vbCrLf & "Cancel to cancel this entry and start over", _
vbYesNoCancel
Select Case iAns
Case vbYes
' do nothing
Case vbNo
Cancel = True
Me.Undo ' erase the current record
Me.Bookmark = rs.Bookmark ' go to the found one
Case vbCancel
Cancel = True
Me.Undo
End Select
End If
End Sub
 
jaylt said:
I have a mailing list that I use to print mailing lables with. The trouble I
am having is duplicate entries of the names and addresses. This database is
split, address.mdb and address_be.mdb. Most all the data entry is done
though a form in address.mdb.
Is there a way to get Accress to test for the first and last names dup
before allowing the dup to be saved? Or just even to check for a name?

Thank You,
Jay
Click to expand...

I worked in a store with two Donald Leroy Smiths so just checking names will
not do.
But a duplicate query showing and sorted by address will get you started.
If this is a small list that should do.
If it a larger list then most just let the duplicates go by.

It will not solve a *lot* of problems if the info comes from different
sources.

D. Smith and Donald Smith may be the same people.
123 West 3rd and 123 W third may be the same.

If this is a large list farming it out to a place that can CASS certify can
save a lot of money.
 
I want to thank everyone who responded to my post. John V had some VBA code
that fit the bill very nicely.
I am still pretty new to VBA and was a little overwhelmed.
Thank you so much to all again.

Jay Thomas
South Puget Sound Com. College
 
Back
Top