Checking for duplicate name

[John]

Hi

We have a staff database and need to make sure that the same staff is not
entered twice. Is there a way for Access to flag if combination of forename
and surname fields is already in the system when adding a new record?
Preferably straight after entering forename & surname so user does not waste
time entering the rest of the info.

Thanks

Regards

[Kostas]

Well yes,
You are talking about making a natural primary key consisting of the
combination of surname and firstname fields.
One way is what I just mentioned, to specify this as your primary key.
Another way is to keep whatever key u already have in place (i.e., an
autonumber field) and enforce a uniqueness constraint on the combination of
those two fields. Select Indexed No Duplicates. I am pretty sure Access does
this for single-valued alternate keys, I am not sure it does it for a
combination of fields as in ur case, but I see no reason why it shouldn't (I
havn't tried it myself though).
hth

Konstantinos

PS: A primary key of first name and last name might be a bad idea
(especially if ur company is large) since it is often the case that people
have identical first and last names but are...well...di fferent people.

"John" <Jo**@nospam.in fovis.co.uk> wrote in message
news:41******** *************** @news-text.dial.pipex .com...

Hi

We have a staff database and need to make sure that the same staff is not
entered twice. Is there a way for Access to flag if combination of
forename
and surname fields is already in the system when adding a new record?
Preferably straight after entering forename & surname so user does not
waste
time entering the rest of the info.

Thanks

Regards

[John]

Just out of interest, how does one define two separate fields as a primary
key in access?

"Kostas" <no*****@noemai l.net> wrote in message
news:10******** *****@corp.supe rnews.com...

Well yes,
You are talking about making a natural primary key consisting of the
combination of surname and firstname fields.
One way is what I just mentioned, to specify this as your primary key.
Another way is to keep whatever key u already have in place (i.e., an
autonumber field) and enforce a uniqueness constraint on the combination of those two fields. Select Indexed No Duplicates. I am pretty sure Access does this for single-valued alternate keys, I am not sure it does it for a
combination of fields as in ur case, but I see no reason why it shouldn't (I havn't tried it myself though).
hth

Konstantinos

PS: A primary key of first name and last name might be a bad idea
(especially if ur company is large) since it is often the case that people
have identical first and last names but are...well...di fferent people.

"John" <Jo**@nospam.in fovis.co.uk> wrote in message
news:41******** *************** @news-text.dial.pipex .com...

Hi

We have a staff database and need to make sure that the same staff is not entered twice. Is there a way for Access to flag if combination of
forename
and surname fields is already in the system when adding a new record?
Preferably straight after entering forename & surname so user does not
waste
time entering the rest of the info.

Thanks

Regards

[Kostas]

You go in Design View,
Select the first row
Pressing SHIFT select the second row
Continuing to have SHIFT pressed right-click on ur mouse,
Select Primary key,

Done.

Just out of interest, have you ever worked in Access before? I am not an
expert myself but I think you oughta know these things if building a
database for a business or hire someone that does.

Cheers!
Konstantinos

select both rows and left-click and choose Set as primary key
"John" <Jo**@nospam.in fovis.co.uk> wrote in message
news:41******** *************** @news-text.dial.pipex .com...

Just out of interest, how does one define two separate fields as a primary
key in access?

[Tom van Stiphout]

On Sat, 16 Oct 2004 02:06:02 +0100, "John" <Jo**@nospam.in fovis.co.uk>
wrote:

In the AfterUpdate event of the LastName field write:
if DCount("LastNam e", "tblStaff", "LastName=' " & Forms!MyForm!La stName
& "' AND FirstName='" & Forms!MyForm!Fi rstName & "'") > 0 then
msgbox "Alarm! Such firstname/lastname already exists!",vbCrit ical
end if

I'm sure you'll want to modify this code to a MsgBox(..., vbOkCancel)
and upon OK cancel the current record, and jump to the existing
record.

-Tom.

Hi

We have a staff database and need to make sure that the same staff is not
entered twice. Is there a way for Access to flag if combination of forename
and surname fields is already in the system when adding a new record?
Preferably straight after entering forename & surname so user does not waste
time entering the rest of the info.

Thanks

Regards

[Bob Quintal]

"Kostas" <no*****@noemai l.net> wrote in
news:10******** *****@corp.supe rnews.com:

You go in Design View,
Select the first row
Pressing SHIFT select the second row
Continuing to have SHIFT pressed right-click on ur mouse,
Select Primary key,

Done.
This works for adjacent fields in the list. Use [ctrl]-click for
non-adjacent fields. Its behaviour is the standard for multi-
select boxes or in windows explorer.

Bob

Just out of interest, have you ever worked in Access before? I
am not an expert myself but I think you oughta know these
things if building a database for a business or hire someone
that does.

Cheers!
Konstantinos

select both rows and left-click and choose Set as primary key
"John" <Jo**@nospam.in fovis.co.uk> wrote in message
news:41******** *************** @news-text.dial.pipex .com...

Just out of interest, how does one define two separate fields
as a primary key in access?

--
Bob Quintal

PA is y I've altered my email address.

[Salad]

Tom van Stiphout wrote:

On Sat, 16 Oct 2004 02:06:02 +0100, "John" <Jo**@nospam.in fovis.co.uk>
wrote:

In the AfterUpdate event of the LastName field write:
if DCount("LastNam e", "tblStaff", "LastName=' " & Forms!MyForm!La stName
& "' AND FirstName='" & Forms!MyForm!Fi rstName & "'") > 0 then
msgbox "Alarm! Such firstname/lastname already exists!",vbCrit ical
end if

I'm sure you'll want to modify this code to a MsgBox(..., vbOkCancel)
and upon OK cancel the current record, and jump to the existing
record.
This is why I also like autonumbers...s ince if I change the value in
last/first name and then restore to original value before moving on, it
will inform me that the record already exists.

WIth an autonumber you can also add
" And Employeeid <> " & Me.EmployeeID
in the Dcount so you are only checking other records besides the current
record.


-Tom.

Hi

We have a staff database and need to make sure that the same staff is not
entered twice. Is there a way for Access to flag if combination of forename
and surname fields is already in the system when adding a new record?
Preferably straight after entering forename & surname so user does not waste
time entering the rest of the info.

Thanks

Regards