Solved Cannot add more than two entries into table before getting a 3022 error

[JamieRhysEdwards]

Hi All,

I'm trying to add new entries into my table but I cannot add more than two and then access throws the following error:

Run-time error '3022':

The changes you requested to the table were not successful because they would create
duplicate values in the index, primary key, or relationship. Change the data in the
field or fields that contain duplicate data, remove the index, or redefine the index
to permit duplicate entries and try again.

The table this is referring to is structured as follows:

ID - Primary Key - Increment - Indexed (No Duplicates)
LastName - Short Text
FirstName - Short Text
EmailAddress - ShortText
Notes - Long Text
Attachments - Attachment
MobileNumber - Number
JobTitle - Number - Relationship with a Look Up table
JobStatus - Number - Relationship with a Look Up Table
DateStarting - Date/Time
DateFinishing - Date/Time

I'm very new to access so I've tried to slightly modify the Asset Database template that Access provides to hit the ground running.

 

[Uncle Gizmo]

The changes you requested to the table were not successful because they would create duplicate values in the index

Make a backup copy of your database first. Open your table in design view, in the ribbon along the top of the screen the tab "table design" should be selected. Under that tab, you should have a button named "indexes". Click on that, it opens a pop-up form which shows you the indexes attached to your table. Delete all the indexes. See if that fixes your problem.

 

[Gasman]

I doubt that you can add more than one with the same data, that must not be duplicated?

 

[The_Doc_Man]

First, since that was your first post: Hello and welcome to the forum.

Second, from your data description, it appears at first glance that the only field that could cause such an error is your ID field. If it is an AUTOINCREMENT number, it should not be able to cause that error. This means that an implied assumption must be false. Therefore,

1. It is NOT an autoincrement field. Examine ID's definition closely.

2. Some other field has a unique index. Examine their definitions including the presence of "Indexed" and "No Dups"

3. One of the fields involved in a relationship has the relationship "pointing the wrong way." Examine the relationships panel to verify that the two fields have their "relationship" arrows pointing the right way. If you are doing a lookup, the relationship should be with the ONE side on the lookup table and the MANY side on this table that references the lookups.

EDIT: As a side note, calling a field "ID" is usually not a great idea because it tells you so little. If this is a person table, maybe use PersID or something like that. You want to maximize the mnemonic value of a field name without going overboard.

 

[JamieRhysEdwards]

Hi All, (Thanks for the welcome @The_Doc_Man) what @Uncle Gizmo has suggested has seemed to fix the issue I've had wherein I needed to delete all indexes except (as I could not actually delete it) ID. Not sure what this would do?

I've had a look at the definition for my ID and it is definitely autoincrement (and autonumber).

 

[The_Doc_Man]

Since you made ID your PK, you cannot revoke the index without revoking its PK status. The other fields didn't appear to NEED any indexes, so if you removed one, that would have done the trick.

 

[Pat Hartman]

While you're at it, remove the table level lookups. They will haunt you later. Use combos on forms but never on tables. If you need to see both the id and the text value in a query, use a join to the lookup table.

 

[LarryE]

If you have a foreign key field in a table related to the Primary key, make sure that its index is set to Duplicates OK. Sometimes we forget foreign keys should allow duplicates.