strange access error

[00allen_iverson]

I got this error when I type in a value in a form and going to next record:

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.
(Error 3022)


You tried to duplicate a value in a field that is the underlying table's
primary key or an index that doesn't allow duplicates. 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.

I am using access 97. PLease help. Thanks.

 

[00allen_iverson]

I got this error when I type in a value in a form and going to next record:

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.
(Error 3022)


You tried to duplicate a value in a field that is the underlying table's
primary key or an index that doesn't allow duplicates. 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.

I am using access 97. PLease help. Thanks.

 

[John Vinson]

I got this error when I type in a value in a form and going to next record:

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.
(Error 3022)


You tried to duplicate a value in a field that is the underlying table's
primary key or an index that doesn't allow duplicates. 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.

I am using access 97. PLease help. Thanks.

Well, what's strange? The error message seems clear: something you
have entered on to the form is causing a duplication in a field which
does not allow duplications. It sounds like you're trying to enter the
same record twice, or there might be a unique Index on a field that
you don't actually want to be unique.

John W. Vinson[MVP]

 

[Guest]

The error means exactly what it says. There is a record already in the table
with the value you are entering. If you want to be able to allow duplicates
in the field you are trying to update, then you need to change the field in
the table to allow duplicates. I doubt, however, that this is what you
really want. Probably what you need is to check in the Before Update event
of the control on the form that is bound to that field to see if the value
already exists and present your own text box to tell the user to change the
value.

 

[Guest]

In general that means that you have entered data into the one field or the
collection of fields that make up the primary key of the underlying table.
So, you will need to look in design view at the fields of the underlying
table to determine what your primary key fields are. Then you can look at
your form to figure out where the duplication is. Because the definition of
the primary key fields is that the data they contain are unique to each
record, access won't allow you to have a 2nd record with the same primary key
data. If you want to close the form without figuring out your duplication,
you can simply press the ESC button. This clears all of the data entry you
did in that form for that particular record during that session. If you made
changes, it reverts back to the original data that was in the record.

Hope this helps!

 

[00allen_iverson]

The strange thing is when I press esc button and go to next record and come
back to the exactly same record and input the same data, it will let me
through without any error. I don't understand this happens on and off. It
is very inconsistant. Please help. Thanks.

 

[00allen_iverson]

Can anyone please help? Thanks.

 

[Rick Brandt]

Can anyone please help? Thanks.

The error message seems fairly straight-forward. What part of it don't you
understand?

If you have a table with a field that is the Primary Key or has a unique index
applied to it, then entering the same value in more than one record produces
that error.

 

[00allen_iverson]

What about this?


The strange thing is when I press esc button and go to next record and come
back to the exactly same record and input the same data, it will let me
through without any error. I don't understand this happens on and off. It
is very inconsistant. Please help. Thanks.

 

[Guest]

It's hard to diagnose without looking at it directly. I will say that the
first thing I do when I feel like an Access Database is behaving erraticly is
to Compact and Repair the database. You can do this from the Objects Window,
under Tools. This can take anywhere from a second to a long time, depending
upon the size of the database and the speed of the computer running it.
Compact and Repair resets some of the internal flags and settings that may
not have properly initialized due to the database crashing or the computer
locking while the database was in use. Also, unreliable network connections
can cause a problem if the file is being worked on by a remote computer on a
network.

Besides that, I'm afraid that I don't have much else to offer on this one.
If the compact and repair doesn't help you, maybe you could create a new post
with the information about it being erratic and see what someone else has to
say.

Good luck!
Andrea