avoid duplicate record.

[Anton Ayrapetov]

I have a club members database. The member table includes member's
Last Name and First name. I need to prevent multiple entry of the
member but still allow entry for persons having same last name. for
instance:
first record
Last Name: Smith
First Name: John
If clerk enters Smith John again, it should give him an error
Message something like : " Smith, John already exists in the database
"
However, if Smith Rebeca joins the club, there should be no problem to
put the record.
How to make the entry checked against two fields , and prompt for the
error only if both fields match the new entry?

thanks

 

[John Spencer]

One way to prevent this is to use a unique index built on the two fields.

To create a multiple field unique index (Compound index)
--Open up the table in design mode
--Select View: Index from the menu
--Enter a name for the iIndex in first row under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
(Do NOT skip rows, do NOT enter the index name again)
--Continue moving down and selecting fieldnames until all needed fields are
included.
--Close the index window and close and save the table


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County