duplicate values in form

[sammers101]

I have forms that when I edit them say: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship

I tried creating a new database form, any based on queries cause the error, but database forms based on my tables work fine.

I uploaded a smaller version here:
https://docs.google.com/open?id=0B0RLVeeWAtNxVUcxR2I0MWh5SUk

 

[jdraw]

Do you understand this message

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship

In very general terms, tables have Primary keys. A Primary key uniquely defines/identifies records in a Table.

When you attempt to add a record, and the key for that record already exists in the Table, you get this error. Since you want records to be uniquely identified, you can only have a given key value once in the table. So the message is protecting you from corrupting your data.

Forms do not store data. Data is stored in Tables. It appears the error is highlighted when you attempt to save a record in your Table.

I have acc2003 and can not open/use an accdb formatted database.

 

[sammers101]

I've uploaded a .mdb version:
https://docs.google.com/open?id=0B0RLVeeWAtNxRkdSa05mak1kRUk

These fields are not the primary key and do have many duplicates. One of them is a checkbox called Binder. Also, the changes are saved even though the error says they are not, and I am able to make the edit through the table without the error. The primary keys are: for Product table: ID, Categories table: ID and Inventory table: ID, which are linked together
Each item in Inventory has a Product ID which has a category ID

If you select Cards at the top of the main form, and select Innistrad, click edit at the top of the popup, if I select the Binder checkbox or the Have column the error pops up.

If you open the Inventory table, and edit the Binder checkbox no error, as well as InitialLevel, StandardCost, ListPrice, color, ProductName and those are only the ones that matter.

 

[jdraw]

You may want to zip your file. My virus checker is balking at the mdb and file size.

 

[sammers101]

zip file 8mb https://docs.google.com/open?id=0B0RLVeeWAtNxYWhMcTJtclFqV3c

or rar file
https://docs.google.com/open?id=0B0RLVeeWAtNxaU5qaDg0c2tWXzA

 

[jdraw]

I downloaded your zip file. When I try to open with Access 2003 it says unrecognizable format --- it's either accdb or perhaps corrupted?????

 

[sammers101]

blah,

http://www.filefactory.com/file/2edwa3dd7pv3/n/smaller_zip
maybe google docs doesnt like zip files
​

 

[steve21nj]

i haven't looked at your DB because of the error downloading but usually it means within the query you have not added the primary and fk within the query results. try added both in different columns

 

[sammers101]

I started with a template, it already had two of the three primary keys. I made the last one myself, but I forget how with a webform. When I looked at the query to check, in design view where the tables are, it had the primary key symbol next to the id's that I mentioned.

not sure what you mean, means within the query you have not added the primary and fk within the query results. try added both in different column.

none of the files work? what about the original accdb? no one has access 2010? the way I converted the file to .mdb was just changing the file extention, works on my computer. Is there something i'm doing wrong?

If I try to save as, there is no option to save as .mdb, only .accdb

 

[steve21nj]

i just downloaded the first one. which form(s) specifically are you having the issue with?

 

[sammers101]

Main, click cards at the top, select Innistrad, click edit and try to edit any of it, errors, but when I edit the inventory table it works fine

 

[steve21nj]

and to save as another file extension, if you are using ms access 2010: go to file, save and publish, and click the database file type under database file types.

 

[sammers101]

I uploaded a newer version, I think i've altered the layout a little bit since I uploaded the first one. The table structure and relationships and such should be the same though
https://docs.google.com/open?id=0B0RLVeeWAtNxbkN5Wk8tRWN5cDg

 

[steve21nj]

i have both files, and under cards i do not see "Innistrad" anywhere. The first list starts with unlimited, ice age, tempest, merc, and odyssey

 

[sammers101]

scroll down, I thought I had deleted the rest, all of them don't work though. Innistrad has data to experiment with though, anything you click, should give the error if you edit the binder checkboxes or color, number etc

 

[sammers101]

is it showing the error?

just in case
https://docs.google.com/open?id=0B0RLVeeWAtNxX0wwRVQtVGtVT3M
17th time's a charm, I've renamed this one to smaller version and tried to simplify it. It should say smaller version instead of cards that way I know we're both looking at the same file

 

[sammers101]

so while trying to convert to mdb format, I had to delete data macros, and then I realized, it was working!
apparently this is causing problems, I started with a template, which had built in macros, I must have broken this one:
Products: Before Update
<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><DataMacro Event="AfterUpdate"><Statements><Action Name="SetLocalVar"><Argument Name="Name">varFoundInventoryData</Argument><Argument Name="Value">False</Argument></Action><LookUpRecord><Data><Query><References><Reference Source="Inventory"/></References><Results><Property Source="Inventory" Name="ProductID"/></Results></Query><WhereCondition>[ProductID]=[ID]</WhereCondition></Data><Statements><EditRecord><Data/><Statements><Action Name="SetLocalVar"><Argument Name="Name">varFoundInventoryData</Argument><Argument Name="Value">True</Argument></Action></Statements></EditRecord></Statements></LookUpRecord><ConditionalBlock><If><Condition>[varFoundInventoryData]=False</Condition><Statements><Action Name="RunDataMacro"><Argument Name="MacroName">Inventory.NewItem</Argument><Parameters><Parameter Name="prmProductID" Value="[ID]"/></Parameters></Action></Statements></If></ConditionalBlock></Statements></DataMacro></DataMacros>
not sure why, hopefully it won't cause different problems by deleting it

thanks for the help guys! I had about given up, lucky I tried it again after deleting all the macros