validation dependencies

Hello,

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

For example, I have a table with an "employeename" field and a "round"
field. If a user enters the record "John Doe" for employeename and "1" for
round, then the user must not be allowed to enter the same thing twice. "John
Doe" can only be entered again if the user specifies a different round (e.g.,
2,3,4,5,etc). 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.

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

Thanks,
~Gabe

I'm not quite clear from your post whether the same value of round can be
used more than once with different names. If not, i.e. every row has a
different value of round, then simply index the round column uniquely.

However, that sounds too simple so I suspect that each value of employeename
can be repeated, each value of round can be repeated, but the same
combination of employeename and round can't be repeated. If so create a
unique index on the employeename and round columns (fields) in combination.
The easiest way of doing this is by making the two columns the table's
composite primary key, which you do in table design view by Ctrl-clicking on
each field, making sure you click on the field selector (the little grey
rectangle to the left of the field name), then right-click and select
'Primary key' from the shortcut menu.

If you are already using another column as the primary key you can index the
fields uniquely by selecting indexes from the View menu. Enter a suitable
index name in one row of the left column, then enter the column names on two
rows of the Filed Name column. With the first row (the one with the index
name) selected enter Yes as the 'Unique' property.

With a unique index like this the user will get a rather cryptic error
message when trying to save a record if they violate the index. To give them
something more meaningful you'd trap the error in your data entry form's
Error event procedure and give the user a custom message by means of the
MsgBox function e.g.

Const INDEXVIOLATION = 3022
Const MESSAGE = "This employee has been previously loaded."

Select Case DataErr
Case INDEXVIOLATION
MsgBox MESSAGE, vbExclamation, "Invalid Operation"
Response = acDataErrContinue
Case Else
'unknown error so display system message
Response = acDataErrDisplay
End Select

Ken Sheridan
Stafford, England

Gabe wrote:
Hello,

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

For example, I have a table with an "employeename" field and a "round"
field. If a user enters the record "John Doe" for employeename and "1" for
round, then the user must not be allowed to enter the same thing twice. "John
Doe" can only be entered again if the user specifies a different round (e.g.,
2,3,4,5,etc). 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.

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

Thanks,
~Gabe

--
Message posted via http://www.accessmonster.com

On Mon, 8 Jun 2009 14:53:05 -0700, Gabe
wrote:

Hello,

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

For example, I have a table with an "employeename" field and a "round"
field. If a user enters the record "John Doe" for employeename and "1" for
round, then the user must not be allowed to enter the same thing twice. "John
Doe" can only be entered again if the user specifies a different round (e.g.,
2,3,4,5,etc). 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.

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

Thanks,
~Gabe

Do note that in addition to Ken's suggestion there is a possible problem with
your design: names are NOT unique! You could have a record for Karen Smith,
the Chief Financial Officer, and for Karen Smith, the trainee mail clerk (no
relation). Your employee table should - must, I'd say - have a unique
unambiguous EmployeeID. You can create a joint two-field index on the
employeeID and the Round.
--

John W. Vinson [MVP]