Validation (1 Viewer)
- Thread starter Kundan
- Start date
You use a composite key:
en.wikipedia.org
Instead of setting 1 field as the primary key that must be unique; you set 2 fields of your table as the primary key and their permutation must be unique.
Composite key - Wikipedia
Instead of setting 1 field as the primary key that must be unique; you set 2 fields of your table as the primary key and their permutation must be unique.
- Local time
- Today, 09:02
- Joined
- Feb 19, 2002
- Messages
- 43,213
Some rules to live by:
1. Object names should contain ONLY the letters a-z and A-Z, the numbers 0-9, and the underscore _. They should NEVER contain embedded spaces or special characters. My preference is to use CamelCase but others prefer the_underscore as a naming standard. The advantage to CamelCase is that you always type in lower case (except for the definition of the field) and intellisense will capitalize it. If Intellisense doesn't capitalize the word you just typed, it probably contains a typo.
2. All tables should have a primary key. My preference is an autonumber as the PK with a unique index to enforce any unique business rules. I was once a proponent of "natural" keys but that frequently leads to multi-field primary keys and that will cause a problem with combos. So, autonumbers it is.
3. RI (referential integrity) is your friend. Create relationships and enforce RI for all relationships. Also add simple rules such as <= Date() for fields like DateOfBirth and unique indexes. Let the database engine enforce rules whenever possible. You'll find as you develop an app that you occasionally end up with having to update a table using multiple forms or even by using external files to update your data and you want to have the rules apply regardless of what causes the data to be added/updated.
To be specific, I think you might need an Allocation table so you can define a unique list of values. Then your form would use a combo to select a value rather than the user having to type it and potentially make typos.
In this particular situation, add an autonumber to tblDeptAndAlloc and create a multi-field unique index so that the combination of CRNO and Alloc will be unique. To create a multi-column index, open the indexes dialog. On the first line, fill in the Index Name and the first field name as well as the properties. Then on each subsequent line (up to 10 fields might be use to create a composite index) just enter the field name. This will prevent duplicates from being added regardless of what form or other method is used to enter the data. On your data entry form, it is customary to use dLookup() to determine if the values already exist in the table. That way you can give the user a more user-friendly message. But you still need the unique index.
1. Object names should contain ONLY the letters a-z and A-Z, the numbers 0-9, and the underscore _. They should NEVER contain embedded spaces or special characters. My preference is to use CamelCase but others prefer the_underscore as a naming standard. The advantage to CamelCase is that you always type in lower case (except for the definition of the field) and intellisense will capitalize it. If Intellisense doesn't capitalize the word you just typed, it probably contains a typo.
2. All tables should have a primary key. My preference is an autonumber as the PK with a unique index to enforce any unique business rules. I was once a proponent of "natural" keys but that frequently leads to multi-field primary keys and that will cause a problem with combos. So, autonumbers it is.
3. RI (referential integrity) is your friend. Create relationships and enforce RI for all relationships. Also add simple rules such as <= Date() for fields like DateOfBirth and unique indexes. Let the database engine enforce rules whenever possible. You'll find as you develop an app that you occasionally end up with having to update a table using multiple forms or even by using external files to update your data and you want to have the rules apply regardless of what causes the data to be added/updated.
To be specific, I think you might need an Allocation table so you can define a unique list of values. Then your form would use a combo to select a value rather than the user having to type it and potentially make typos.
In this particular situation, add an autonumber to tblDeptAndAlloc and create a multi-field unique index so that the combination of CRNO and Alloc will be unique. To create a multi-column index, open the indexes dialog. On the first line, fill in the Index Name and the first field name as well as the properties. Then on each subsequent line (up to 10 fields might be use to create a composite index) just enter the field name. This will prevent duplicates from being added regardless of what form or other method is used to enter the data. On your data entry form, it is customary to use dLookup() to determine if the values already exist in the table. That way you can give the user a more user-friendly message. But you still need the unique index.
Thanks a lot. GOD BLESS YOU!!!!!
Yes I will keep this in mind.
- Local time
- Today, 09:02
- Joined
- Feb 19, 2002
- Messages
- 43,213
If you only used the BeforeUpdate event of the subform, you only did part of the job.
