Sorry...yet another question about duplicate entries

[Aria]

Hello,
I hate to post yet another question about duplicate entries but I could use
some help with this. I have read hundreds of posts regarding this subject and
have seen answers ranging from use a unique index, DLookup or Soundex. This
has only left me confused.
I'm trying to prevent duplicate entries for my school db and the testing
phase has not been successful. I'm not quite sure how to go about doing this
and was looking for some advice.

Problem:
1. We have multiple staff who share a last name though they are not related.
2. We have staff who share a last name, first name and *are* related.
3. We have staff who are husband and wife and share a last name as well as
an address.

Set-up:
We have site employees (teachers and support staff) as well as substitute
staff (teachers and support staff), community staff and district staff.
Adresses and emergency contact information is related 1:1 to the site
employee table. Therefore, I will not know the address of any other group
besides our own site staff.

Needs:
A way to alert the user that the name entered appears to be a duplicate. I
don't want to get too complicated here. Maybe something like setting the
focus to the combo box in the header and dropping the list to the first
instance of the staff member's name, just so a visual check can be made. I
don't know, maybe there's a better way. I'm open to suggestions. I tried to
create a unique index using information found in one of the many posts I read
but nothing happens when I test it, so I must be doing something wrong.

Actions taken:

1. I opened tblEmployees in design view.
2. Clicked the index button.
3. Went to the first blank row and named the new index I was trying to create.
4. Selected the fields I wanted to use (EmpID, FirstName, MI and LastName)
5. Set the index properties to unique and saved the index.
6. Went to the main form and deliberately input a name that was already in
the db.

Result:
Allowed to enter name and info without any error messages. Obviously, I took
a wrong turn somewhere.

Any assistance, gladly appreciated.

 

[Noëlla Gabriël]

Hi,

as you included the empID , which I assume is the PK of the table, this
empID will always be different in each record, even with duplicate names.
Try leaving the PK field out of the new unique index.

 

[ken]

Indexing on the names doesn't help here as it would be perfectly
legitimate to have two people with exactly the same names; I once
worked with two Maggie Taylors. In fact indexing on names can cause
problems. I was once present at a clinic where patients were indexed
on the basis of their first and last names, gender, and date of
birth. It was assumed that these would represent a unique candidate
key. On the day in question two patients arrived, both female, both
with exactly the same names and both with the same date of birth! A
perfect example of Murphy's Law which states that if something can go
wrong, sooner or later it will go wrong.

With people a unique numeric ID as the key is really the first
requirement. When selecting existing records, e.g. if assigning a
member of staff to a project the ProjectStaff table would include
columns EmployeeID and ProjectID, not the names of the staff members.
This table models a many-to-many relationship between Employees and
Projects so the EmployeeID and ProjectID columns are each a foreign
key referencing the primary keys of the other two tables. Data entry
into the ProjectStaff table would be via a form (most likely a subform
in a Projects form) which includes a combo box bound to the EmployeeID
column but showing the names. To guard against the wrong employee
from two with the same names the combo box should ideally include some
other column in the list, e.g. Department, to differentiate between
the employees.

To prevent the same employee being entered accidentally twice into the
Employees table you can put some code in the data entry form's
BeforeInsert event procedure which looks for existing staff with the
same first and last name, and pops up a message box to warn the user
if one or more exits, and then cancels the insert by setting the
return value of the cancel argument to true if the user opts to abort
the insertion of the new row into the table.

Ken Sheridan
Stafford, England

 

[Aria]

Thank you both for your suggestions. I won't be able to use Dept. because
that only applies to site employees. I will try to find something additional
to differentiate. It's tough though with the many classifications.

Ken, which code are you referring to? DLookup? I saw a post but it said to
use it in the BeforeUpdate event. I'll try to adapt what I saw to my
situation. I'll let you know in a few days how it went.
Thank you very much.

 

[ken]

You are quite right, it would need to be the form's BeforeUpdate event
procedure as the BeforeInsert event executes as soon as the user
begins to enter data in a new record, so is too early in the sequence
pf events, whereas the BeforeUpdate event executes when the user
attempts to save the record. To check whether the names entered
already exist in a row in the table when entering a new employee you
can examine the form's NewRecord property and call the DLookup
function if this is True. If the DLookup function returns Null then
the names don't exist, but otherwise they do so you can then seek the
user's confirmation as to whether to proceed, so the code would be:

Dim strMessage As String
Dim strCriteria As String

strCriteria = "FirstName = """ & Me.FirstName & _
""" And Lastname = """ & Me.LastName & """"

' is new record being inserted
If Me.NewRecord Then
' if so does row already exist with the new names
If Not IsNull(DLookup("EmployeeID", "Employees", strCriteria))
Then
' if so get user confirmation whether to add new employee
' of same name. If user answers 'no' cancel update
' and undo changes
strMessage = "Employee " & Me.FirstName & " " &
Me.LastName & _
" already in database. Do you want to add another" &
_
" employee of the same name?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Warning") =
vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

where the table name is Employees, its primary key column is
EmployeeID and it contains columns FirstName and LastName, both
'required'.

If you want to check for an existing employee of the same name if a
user edits an existing record, i.e. if they change an existing name to
one which already exists in the database, as well as when they insert
a new record, then it’s a little more complex. Firstly declare two
variables in the Declarations area of the form's module to store the
current first and last names when the user navigates to a record,
using the Nz function to return zero- length strings when at a new
record:

' declare variables to store names
Dim strFirstName As String
Dim strLastname As String

Then in the form's Current event procedure assign the current names,
or zero-length strings if at a new record, to the variables:

' store names in variables, assigning
' zero-length strings if new record
strFirstName = Nz(Me.FirstName, "")
strLastname = Nz(Me.LastName, "")

The code for the form's BeforeUpdate event procedure now omits the
examination of the NewRecord property, so is:

Dim strMessage As String
Dim strCriteria As String

strCriteria = "FirstName = """ & Me.FirstName & _
""" And Lastname = """ & Me.LastName & """"

' has first or last name been changed or new record being added
If Me.FirstName <> strFirstName Or Me.LastName <> strLastname Then
' if so does row already exist with the new names
If Not IsNull(DLookup("EmployeeID", "Employees", strCriteria))
Then
' if so get user confirmation whether to add new employee
' of same name. If user answers 'no' cancel update
' and undo changes
strMessage = "Employee " & Me.FirstName & " " &
Me.LastName & _
" already in database. Do you want to add another" &
_
" employee of the same name?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Warning") =
vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

Ken Sheridan
Stafford, England

 

[Fred]

In addition to that check (which is a goo idea for a particularly common type
or duplication) you might backstop that by telling them that step one of
adding a new record of any type is to see if it's already in.

 

[John W. Vinson]

I am having a bit of a problem though.

I received a syntax error with:
==> strMessage = "Employee " & Me.FirstName & " " & _
Me.LastName& " already in database. Do you want to add another"& _
" employee with the same name?"

Try putting a space around the ampersands. Spaces are meaningful!

strMessage = "Employee " & Me.FirstName & " " & _
Me.LastName & " already in database. Do you want to add another" & _
" employee with the same name?"

This does assume that your code is on a form which has controls named
FirstName and LastName. If your controls have different names use those...

 

[ken]

Aria:

You seem to be missing the underscore continuation character at the
end of the first line and a space before the first ampersand in the
second line of:

strMessage = "Employee" & Me.FirstName & " " & _
Me.LastName & " already in database. Do you want to add
another" & _
" employee with the same name?"

Just so you are sure, this is one line of code, but its entered as
three separate lines, the first two ending with the underscore
continuation character. This is to make the code more readable than
if it tailed off into the distance as a single line in the VBA window.

Ken Sheridan
Stafford, England

Ken,
What an absolutely wonderful reply! Very thorough. Thank you for going
through all of that. I would *not* have been able to do that on my own.
Thanks also for explaining when to use the BeforeInsert and the BeforeUpdate
events. You taught me something new there too.

I am having a bit of a problem though.

I received a syntax error with:
==> strMessage = "Employee " & Me.FirstName & " " & _
Me.LastName& " already in database. Do you want to add another"& _
" employee with the same name?"

I made some changes which may be wrong since I haven't been able to carryit
through to the end. I thought maybe there was an extra space after "Employee
" so I changed it to "Employee" but it still states that there is a syntax
error. I then added the _ to continue to the next line. This brings up other
error messages such as expected end of statement. Right now, I'm just
guessing. I decided to stop guessing and get some help.

This is what I have in the forms BeforeUpdate event:

Dim strMessage As String
Dim strCriteria As String
Dim strFirstName As String
Dim strLastName As String

strCriteria = "FirstName = """ & Me.FirstName & _
""" And Lastname = """ & Me.LastName & """"

' has first or last name been changed or new record being added
If Me.FirstName <> strFirstName Or Me.LastName <> strLastName Then
' if so does row already exist with the new names
If Not IsNull(DLookup("EmployeeID", "Employees", strCriteria)) Then
' if so get user confirmation whether to add new employee
' of same name. If user answers 'no' cancel update
' and undo changes
strMessage = "Employee" & Me.FirstName & " " &
Me.LastName& " already in database. Do you want to add another"& _
" employee with the same name?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Warning") = vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

Perhaps you or someone else can direct me further. Thank you.

You are quite right, it would need to be the form's BeforeUpdate event
procedure as the BeforeInsert event executes as soon as the user
begins to enter data in a new record, so is too early in the sequence
pf events, whereas the BeforeUpdate event executes when the user
attempts to save the record. To check whether the names entered
already exist in a row in the table when entering a new employee you
can examine the form's NewRecord property and call the DLookup
function if this is True. If the DLookup function returns Null then
the names don't exist, but otherwise they do so you can then seek the
user's confirmation as to whether to proceed, so the code would be:

Dim strMessage As String
Dim strCriteria As String

strCriteria = "FirstName = """ & Me.FirstName & _
""" And Lastname = """ & Me.LastName & """"

' is new record being inserted
If Me.NewRecord Then
' if so does row already exist with the new names
If Not IsNull(DLookup("EmployeeID", "Employees", strCriteria))
Then
' if so get user confirmation whether to add new employee
' of same name. If user answers 'no' cancel update
' and undo changes
strMessage = "Employee " & Me.FirstName & " " &
Me.LastName & _
" already in database. Do you want to add another" &
_
" employee of the same name?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Warning") =
vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

where the table name is Employees, its primary key column is
EmployeeID and it contains columns FirstName and LastName, both
'required'.

If you want to check for an existing employee of the same name if a
user edits an existing record, i.e. if they change an existing name to
one which already exists in the database, as well as when they insert
a new record, then it’s a little more complex. Firstly declare two
variables in the Declarations area of the form's module to store the
current first and last names when the user navigates to a record,
using the Nz function to return zero- length strings when at a new
record:

' declare variables to store names
Dim strFirstName As String
Dim strLastname As String

Then in the form's Current event procedure assign the current names,
or zero-length strings if at a new record, to the variables:

' store names in variables, assigning
' zero-length strings if new record
strFirstName = Nz(Me.FirstName, "")
strLastname = Nz(Me.LastName, "")

The code for the form's BeforeUpdate event procedure now omits the
examination of the NewRecord property, so is:

Dim strMessage As String
Dim strCriteria As String

strCriteria = "FirstName = """ & Me.FirstName & _
""" And Lastname = """ & Me.LastName & """"

' has first or last name been changed or new record being added
If Me.FirstName <> strFirstName Or Me.LastName <> strLastname Then
' if so does row already exist with the new names
If Not IsNull(DLookup("EmployeeID", "Employees", strCriteria))
Then
' if so get user confirmation whether to add new employee
' of same name. If user answers 'no' cancel update
' and undo changes
strMessage = "Employee " & Me.FirstName & " " &
Me.LastName & _
" already in database. Do you want to add another" &
_
" employee of the same name?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Warning") =
vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

Ken Sheridan
Stafford, England

Thank you both for your suggestions. I won't be able to use Dept. because
that only applies to site employees. I will try to find something additional

[quoted text clipped - 92 lines]

 

[Aria]

Thank you *so* much to everyone for helping me with this. I received error
#3078 but I was able to correct that by using my field and table names. After
I made the changes, the error message appeared along with the "yes" or "no"
choice to add?cancel the new record. Thank you once again, everyone!
--
Aria W.

Aria:

You seem to be missing the underscore continuation character at the
end of the first line and a space before the first ampersand in the
second line of:

strMessage = "Employee" & Me.FirstName & " " & _
Me.LastName & " already in database. Do you want to add
another" & _
" employee with the same name?"

Just so you are sure, this is one line of code, but its entered as
three separate lines, the first two ending with the underscore
continuation character. This is to make the code more readable than
if it tailed off into the distance as a single line in the VBA window.

Ken Sheridan
Stafford, England

Ken,
What an absolutely wonderful reply! Very thorough. Thank you for going
through all of that. I would *not* have been able to do that on my own.
Thanks also for explaining when to use the BeforeInsert and the BeforeUpdate
events. You taught me something new there too.

I am having a bit of a problem though.

I received a syntax error with:
==> strMessage = "Employee " & Me.FirstName & " " & _
Me.LastName& " already in database. Do you want to add another"& _
" employee with the same name?"

I made some changes which may be wrong since I haven't been able to carry it
through to the end. I thought maybe there was an extra space after "Employee
" so I changed it to "Employee" but it still states that there is a syntax
error. I then added the _ to continue to the next line. This brings up other
error messages such as expected end of statement. Right now, I'm just
guessing. I decided to stop guessing and get some help.

This is what I have in the forms BeforeUpdate event:

Dim strMessage As String
Dim strCriteria As String
Dim strFirstName As String
Dim strLastName As String

strCriteria = "FirstName = """ & Me.FirstName & _
""" And Lastname = """ & Me.LastName & """"

' has first or last name been changed or new record being added
If Me.FirstName <> strFirstName Or Me.LastName <> strLastName Then
' if so does row already exist with the new names
If Not IsNull(DLookup("EmployeeID", "Employees", strCriteria)) Then
' if so get user confirmation whether to add new employee
' of same name. If user answers 'no' cancel update
' and undo changes
strMessage = "Employee" & Me.FirstName & " " &
Me.LastName& " already in database. Do you want to add another" & _
" employee with the same name?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Warning") = vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

Perhaps you or someone else can direct me further. Thank you.

You are quite right, it would need to be the form's BeforeUpdate event
procedure as the BeforeInsert event executes as soon as the user
begins to enter data in a new record, so is too early in the sequence
pf events, whereas the BeforeUpdate event executes when the user
attempts to save the record. To check whether the names entered
already exist in a row in the table when entering a new employee you
can examine the form's NewRecord property and call the DLookup
function if this is True. If the DLookup function returns Null then
the names don't exist, but otherwise they do so you can then seek the
user's confirmation as to whether to proceed, so the code would be:

Dim strMessage As String
Dim strCriteria As String

strCriteria = "FirstName = """ & Me.FirstName & _
""" And Lastname = """ & Me.LastName & """"

' is new record being inserted
If Me.NewRecord Then
' if so does row already exist with the new names
If Not IsNull(DLookup("EmployeeID", "Employees", strCriteria))
Then
' if so get user confirmation whether to add new employee
' of same name. If user answers 'no' cancel update
' and undo changes
strMessage = "Employee " & Me.FirstName & " " &
Me.LastName & _
" already in database. Do you want to add another" &
_
" employee of the same name?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Warning") =
vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

where the table name is Employees, its primary key column is
EmployeeID and it contains columns FirstName and LastName, both
'required'.

If you want to check for an existing employee of the same name if a
user edits an existing record, i.e. if they change an existing name to
one which already exists in the database, as well as when they insert
a new record, then it’s a little more complex. Firstly declare two
variables in the Declarations area of the form's module to store the
current first and last names when the user navigates to a record,
using the Nz function to return zero- length strings when at a new
record:

' declare variables to store names
Dim strFirstName As String
Dim strLastname As String

Then in the form's Current event procedure assign the current names,
or zero-length strings if at a new record, to the variables:

' store names in variables, assigning
' zero-length strings if new record
strFirstName = Nz(Me.FirstName, "")
strLastname = Nz(Me.LastName, "")

The code for the form's BeforeUpdate event procedure now omits the
examination of the NewRecord property, so is:

Dim strMessage As String
Dim strCriteria As String

strCriteria = "FirstName = """ & Me.FirstName & _
""" And Lastname = """ & Me.LastName & """"

' has first or last name been changed or new record being added
If Me.FirstName <> strFirstName Or Me.LastName <> strLastname Then
' if so does row already exist with the new names
If Not IsNull(DLookup("EmployeeID", "Employees", strCriteria))
Then
' if so get user confirmation whether to add new employee
' of same name. If user answers 'no' cancel update
' and undo changes
strMessage = "Employee " & Me.FirstName & " " &
Me.LastName & _
" already in database. Do you want to add another" &
_
" employee of the same name?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Warning") =
vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

Ken Sheridan
Stafford, England

Thank you both for your suggestions. I won't be able to use Dept. because
that only applies to site employees. I will try to find something additional
[quoted text clipped - 92 lines]

 

[Aria]

I'm sorry. I meant the custom message and not an error message.
--
Aria W.

Thank you *so* much to everyone for helping me with this. I received error
#3078 but I was able to correct that by using my field and table names. After
I made the changes, the error message appeared along with the "yes" or "no"
choice to add?cancel the new record. Thank you once again, everyone!
--
Aria W.

Aria:

You seem to be missing the underscore continuation character at the
end of the first line and a space before the first ampersand in the
second line of:

strMessage = "Employee" & Me.FirstName & " " & _
Me.LastName & " already in database. Do you want to add
another" & _
" employee with the same name?"

Just so you are sure, this is one line of code, but its entered as
three separate lines, the first two ending with the underscore
continuation character. This is to make the code more readable than
if it tailed off into the distance as a single line in the VBA window.

Ken Sheridan
Stafford, England

Ken,
What an absolutely wonderful reply! Very thorough. Thank you for going
through all of that. I would *not* have been able to do that on my own.
Thanks also for explaining when to use the BeforeInsert and the BeforeUpdate
events. You taught me something new there too.

I am having a bit of a problem though.

I received a syntax error with:
==> strMessage = "Employee " & Me.FirstName & " " & _
Me.LastName& " already in database. Do you want to add another"& _
" employee with the same name?"

I made some changes which may be wrong since I haven't been able to carry it
through to the end. I thought maybe there was an extra space after "Employee
" so I changed it to "Employee" but it still states that there is a syntax
error. I then added the _ to continue to the next line. This brings up other
error messages such as expected end of statement. Right now, I'm just
guessing. I decided to stop guessing and get some help.

This is what I have in the forms BeforeUpdate event:

Dim strMessage As String
Dim strCriteria As String
Dim strFirstName As String
Dim strLastName As String

strCriteria = "FirstName = """ & Me.FirstName & _
""" And Lastname = """ & Me.LastName & """"

' has first or last name been changed or new record being added
If Me.FirstName <> strFirstName Or Me.LastName <> strLastName Then
' if so does row already exist with the new names
If Not IsNull(DLookup("EmployeeID", "Employees", strCriteria)) Then
' if so get user confirmation whether to add new employee
' of same name. If user answers 'no' cancel update
' and undo changes
strMessage = "Employee" & Me.FirstName & " " &
Me.LastName& " already in database. Do you want to add another" & _
" employee with the same name?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Warning") = vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

Perhaps you or someone else can direct me further. Thank you.



(e-mail address removed) wrote:
You are quite right, it would need to be the form's BeforeUpdate event
procedure as the BeforeInsert event executes as soon as the user
begins to enter data in a new record, so is too early in the sequence
pf events, whereas the BeforeUpdate event executes when the user
attempts to save the record. To check whether the names entered
already exist in a row in the table when entering a new employee you
can examine the form's NewRecord property and call the DLookup
function if this is True. If the DLookup function returns Null then
the names don't exist, but otherwise they do so you can then seek the
user's confirmation as to whether to proceed, so the code would be:

Dim strMessage As String
Dim strCriteria As String

strCriteria = "FirstName = """ & Me.FirstName & _
""" And Lastname = """ & Me.LastName & """"

' is new record being inserted
If Me.NewRecord Then
' if so does row already exist with the new names
If Not IsNull(DLookup("EmployeeID", "Employees", strCriteria))
Then
' if so get user confirmation whether to add new employee
' of same name. If user answers 'no' cancel update
' and undo changes
strMessage = "Employee " & Me.FirstName & " " &
Me.LastName & _
" already in database. Do you want to add another" &
_
" employee of the same name?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Warning") =
vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

where the table name is Employees, its primary key column is
EmployeeID and it contains columns FirstName and LastName, both
'required'.

If you want to check for an existing employee of the same name if a
user edits an existing record, i.e. if they change an existing name to
one which already exists in the database, as well as when they insert
a new record, then it’s a little more complex. Firstly declare two
variables in the Declarations area of the form's module to store the
current first and last names when the user navigates to a record,
using the Nz function to return zero- length strings when at a new
record:

' declare variables to store names
Dim strFirstName As String
Dim strLastname As String

Then in the form's Current event procedure assign the current names,
or zero-length strings if at a new record, to the variables:

' store names in variables, assigning
' zero-length strings if new record
strFirstName = Nz(Me.FirstName, "")
strLastname = Nz(Me.LastName, "")

The code for the form's BeforeUpdate event procedure now omits the
examination of the NewRecord property, so is:

Dim strMessage As String
Dim strCriteria As String

strCriteria = "FirstName = """ & Me.FirstName & _
""" And Lastname = """ & Me.LastName & """"

' has first or last name been changed or new record being added
If Me.FirstName <> strFirstName Or Me.LastName <> strLastname Then
' if so does row already exist with the new names
If Not IsNull(DLookup("EmployeeID", "Employees", strCriteria))
Then
' if so get user confirmation whether to add new employee
' of same name. If user answers 'no' cancel update
' and undo changes
strMessage = "Employee " & Me.FirstName & " " &
Me.LastName & _
" already in database. Do you want to add another" &
_
" employee of the same name?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Warning") =
vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

Ken Sheridan
Stafford, England

Thank you both for your suggestions. I won't be able to use Dept. because
that only applies to site employees. I will try to find something additional
[quoted text clipped - 92 lines]