Preventing entries based on a field

[Gabe]

Hello,

I want to prevent duplicate record entries based on a field.

For example, I have a table with an "lastname" field and a "round"
field. If a user enters the record "Doe" for lastname and "1" for
round, then the user must not be allowed to enter the same thing twice.
"Doe" can only be entered again if the user specifies a different round
(e.g., round 2,3,4,5,etc). The round value can be repeated, it just can't
have the same name.

So in other words, the table is only allowed to have 1 name per
round. When the user trys to input a duplicate record, there could be a
message that says: "this employee has been previously loaded" or something
like that.

The data I'm using kind of looks like this:
RecordID - lastname - round
001 - Doe - 1
002 - Smith - 1
003 - Davis - 1
004 - Doe - 1

So looking at this, RecordID 004 would be wrong because "Doe" is already in
round 1.

Does anyone out there know how to code something like this? I'm using Access
2003.

Thanks,
~Gabe

 

[Dirk Goldgar]

Hello,

I want to prevent duplicate record entries based on a field.

For example, I have a table with an "lastname" field and a "round"
field. If a user enters the record "Doe" for lastname and "1" for
round, then the user must not be allowed to enter the same thing twice.
"Doe" can only be entered again if the user specifies a different round
(e.g., round 2,3,4,5,etc). The round value can be repeated, it just can't
have the same name.

So in other words, the table is only allowed to have 1 name per
round. When the user trys to input a duplicate record, there could be a
message that says: "this employee has been previously loaded" or something
like that.

The data I'm using kind of looks like this:
RecordID - lastname - round
001 - Doe - 1
002 - Smith - 1
003 - Davis - 1
004 - Doe - 1

So looking at this, RecordID 004 would be wrong because "Doe" is already
in
round 1.

Does anyone out there know how to code something like this? I'm using
Access
2003.

Without any code at all, you could create a unique index on those two fields
in the table. You would do this in design view of the table. In this case,
the index would be composed of not just one field, but two: both the
LastName field and the Round field. (Note: I recommend you change the
field name from "Round" to something that is not a reserved word. It will
save you ttouble down the road.)

If you create an index on the two fields, and don't allow duplicates in that
index, then it will be impossible for the user to store two records with the
same LastName and Round values. When the user attempts to save such a
record, the form will throw an error, which you could trap in the form's
Error event. In that event, you could check that the error number is the
one associated with a duplicate key, and display your own message instead of
the default one.

If you want to catch the error the database engine rejects the record, you
could also use the form's BeforeUpdate event to see if there's already a
record with the same LastName and Round. In that case, you would cancel the
event, preventing Access from even attempting to save the record.

 

[Jerry Whittle]

No code needed. It can be done at table level. Create a unique index based on
those two fields together in table design view.

Go up to View, Indexes. Put in an Index Name then drop down the first field.
Next drop down the second index but leave the name blank.

Set the index to Unique Yes.