Robin-
That tells me that you had duplicate CartonNo values to begin with, which shouldn't be the case if CartonNo was the Primary Key.
When you defined the new field and set a Default Value, that didn't set the value in any existing rows. The default value will appear only for new records. If you had entered a few cartons with suffixes, the IS NULL test should avoid putting 0 in those records. Let's say your data looked like:
CartonNo Suffix
0001 Null
0002 0
0002 1
0003 Null
0004 Null
0005 0
0005 1
0006 Null
0007 Null
Setting all the Null values to 0 should get you unique combinations of CartonNo and Suffix. BUT if you had previously entered some duplicate cartons and failed to set the Suffix to different values, then that's why you will get the error when you try to define the new Primary Key.
I thought CartonNo was the Primary Key before you started this, but apparently you had some other field as the Primary Key. What was it?
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Oct 20, 2014, at 7:32 AM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
After running the update query "qryCartonSuffixUpdate"
UPDATE CollectionVoucher SET CollectionVoucher.CartonSuffix = 0
WHERE (((CollectionVoucher.CartonSuffix) Is Null));
Now again after defining CartonNo and CartonSuffix as Primary key and saving the table i get the error message "The changes you requested to the table were not successful because they would create duplicate values in the indes, Primary key, or relationship. Change the data in the field............"
Khalid
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (8) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar