If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Preventing entries based on a field
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 |
#2
|
|||
|
|||
Preventing entries based on a field
"Gabe" wrote in message
... 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. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#3
|
|||
|
|||
Preventing entries based on a field
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Gabe" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|