Primary key - yes (no duplicates) option not working!

[Sheyla Ayala]

Hello,

I created a database for a data collection experiment for my department. One field in my master table is "client ID" , we want no duplicate entries and this is determined by client ID number, i set the field as primary key, and made sure that the option " Yes (no duplicates) was stablished. Now my team has come accross the following issue and dont know why! They have tried to enter new client info in the respective form, often times the system tells them what they are trying to enter is a duplicate value, but when i go check the master table where all the info goes, the client ID is not in the list, ghe system should accept it, it's not a duplicate! What is happening?

[chocomon]

please refer to this answer only if you have a secondary table that depends on the primary (master) table with the primary key, because i'm not sure if you do from your post!!!


are you saying that you have a primary table with a primary key?

and then you have another table what is embedded ex. as a datagridview in your other form? and then when your employees add to that second table...it gives you this error?

because the problem may be that unless you add the new clients FIRST in you're primary table..then you'll get that 'duplicate error'

this link talks about foreign keys...which your second table would have, i presume? it might help you understand what your problem is...

http://www.1keydata.com/sql/sql-foreign-key.html

(there's also a link on the side regarding primary tables)

[Sheyla Ayala]

No, this is a rather very simple database, it consist of a primary table, which already contains a given number of client IDs and names. I have other tables that are linked to the primary table, however not linked to the client ID Field. For instance, I have a table called "client responsiveness" and all it has is a column with three entries: positive, neutral, negative" I linked this table to the primary table to serve as combo box source for my primary table's client responsiveness field.

I had instructed my team to add more info into the primary table for each client already in it. I created a simple split form for this purpose. They asked if they can enter info for clients not included in the list, I said yes, as long as they are new clients (no duplicates), so they did. But they keep getting the error message that the data cannot be saved because that may cause duplicate values in the primary key...

[Mihail]

Check Indexes.
I think that accidentally you have define more than one index.
You must have, as index, only the primary key.

In Access 2007 you find Indexes under Design tab.
I can't remember where is this feature in older versions but the Help files should help you to find it.

If this solve your problem, Ok.
If not I think you must post your database here and I'll take a look.

[TheSmileyCoder]

While I think Mihail is correct in assuming its a question of multiple defined indexes, I just want to add that there is nothing wrong with using more then 1 index, on the contrary. Any field you would expect to often perform joins or searches on, you should index.

That said it is application dependent on whether the field should allow duplicate values.

[NeoPa]

It sounds like the problem is with something you're not telling us. It may be interesting to see exactly what happens with a specific record on entry. If you'd like us to check this for you then please follow the instruction in Attach Database (or other work) very carefully and include the ID of the item you tried to add that resulted in the error.