Checking for duplicate name

J

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
 
D

Damien McBain

John said:
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.
Click to expand...

You can make both fields primary keys in the same table.
 
J

John

Ideally I just want this flagged as sometimes there can be two persons of
the same name. I primary key will totally block entry.

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

Resti M. Guay

after you udate your textbox you can assign a code that check if the staff
is allready on the list, you can use:

Private Sub TextBox_AfterUpdate()
If DCount("Name of Field","Table","Name of Field=TextBox")>0 then
MsgBox "Name is allready on the list."
'you can add code here to clean the form or open the a pop-up form showing
'the complete details of the staff found in the database.
End If
hope this help you.
resti,
 
V

Van T. Dinh

In the DesignView of the (local) Table, select the 1st Field by
left-clicking the rectangle just before the Field name. Hold the Ctrl key
down while selecting the 2nd Field. Now you should have 2 Fields selected
and simply click the PK icon (with the key image).

IIRC, you can have up to 10 Fields in the multi-Field PK.

HTH
Van T. Dinh
MVP (Access)
 
Top