Hi guys,
So I'm editing an existing database (Access 2003), and I need a bit of help in making a validation rule.
My table has many fields, 3 of which are indexed (location, number & revision)
Each of these 3 fields can have duplicates (Same location can exist for multiple items, same number can exist for multiple items, and same revision can exist for multiple items).
However, all 3 fields cannot be the same in multiple records (Ex.. There cannot be multiple items from location X, with Number 1, and revision A).
Is there a way to create a validation rule for the 3 fields in question to enforce this? If not, is there a way to code something like this into VBA?
Thanks!!
Ian
8 10452
Hi Ian,
Looks like the Primary key issue to me.
If you don't want these fields to have the same values twice you should make a Primary key composed of these three fields.
..or maybe add them to the existing PK if you have one???
H.
You may create multifield index to enforce unique combinations only.
Regards,
Fish
Hi!
I just searched help for "index" (multiple-field index), tried it out and got the result you expect. Here is explanation from help:
1. Open the table in Design view
2. Click Indexes on the toolbar.
3. In the first blank row in the Index Name column, type a name for the index ). You can name the index after one of the index fields, or use another name.
4. In the Field Name column, click the arrow and select the first field for the index.
5. In the next row in the Field Name column, select the second field for the index. (Leave the Index Name column blank in that row.) Repeat this step until you have selected all the fields you want to include in this index.
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
This solution is better than what I suggested in the first place. I learned something new too :-)
Regards,
H.
Guys,
Thanks for the help, but it still isn't working for me.
In table design view, I click the indexes button on the table design toolbar, and I added location, number and revision as indexes. Near the bottom of the box, I can click either "primary" "unique" and "ignore nulls". The problem is I can only make them unique individually, instead of unique combinations of the three.
I've tried selecting all of them at once in the indexes box, but if more than one index is selected, the "primary" "unique" and "ignore nulls" options disappear.
Any thoughts??
And with regards to the triple primary key idea, this almost work, except sometimes revision is left null, and primary keys cannot be null :(
Access "Indexes..." dialog is not an intuitive eye candy. ;)
....
5. In the next row in the Field Name column, select the second field for the index. (Leave the Index Name column blank in that row.) Repeat this step until you have selected all the fields you want to include in this index.
....
Regards,
Fish
Thanks guys!
The reason it wasn't working for me is that the table I'm using is already full of duplicates, and I need to get of them before I make my indexes.
Cheers!
@hjozinovic
Hi H
I had a similar problem and fixed it using this great solution. However, when I now try and submit an update to a table I get the error "changes were not successful because they would create a duplicate", which I would expect.
How can I turn off the error message so it doesn't appear?
Iann
Iann,
You can't turn off the warning. You need to change the table so that it allows duplicates.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Dalan |
last post by:
I apparently need a bit of assistance regarding the structure of some
validation code on the BeforeUpdate or AfterUpdate event on a form for
several fields that need to controlled. I did search the...
|
by: Doslil |
last post by:
I am trying to validate the fields in my database.I have already
validated the fields to check for not null.Here is what I have written
for Numeric and text field.
Private Function EENUM()
On...
|
by: Joey P |
last post by:
Hi all,
I am doing a project for university whereby i have to implement a
simple database related to a frozen foods company. I am having some
trouble though creating a validation rule for one...
|
by: Chuck |
last post by:
A97. A database has a table: tblA which has a single text field, B. It is a
primary field, indexed and no duplicates. It is used as a lookup for table
tblC. A form based on tblA is used to add...
|
by: Bob Alston |
last post by:
I have a routine to copy data to new versions of my app via insert into
sql statements. Unfortunately, due to evolution of my app, sometimes
the new version has more restrictive editing than an...
|
by: ryan.paquette |
last post by:
In the table there are 2 fields in which I wish to limit (i.e. No
Duplicates)
Although I do not want to limit them to "No Duplicates" separately.
I need them to be limited to "No Duplicates" as...
|
by: sharsy |
last post by:
Hi guys,
I would like to setup a validation rule for a database in microsoft
access that restricts data entry so that a certain field can only be
filled in if another field has a specific answer...
|
by: MLH |
last post by:
Anyone remember if A97 append query failure would ever report
data breaking validation rule when such was not the case. I have
an old SQL statement - several years old now. I've encountered
a case...
|
by: john.cole |
last post by:
I have searched all the groups I can, and I still haven't been able to
come up the solution I need. I have the following problem.
In my form named sbfrmSpoolList, I am entering a job, spool and...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |