Autonumber is reusing previously assigned numbers-creating duplica

[Tammy]

Hi,

This is a new one to me - in an Access 2007 database, my user inputs data on
a form that contains a subform. However, (all of the sudden) when entering a
new record on the subform, she gets the message that the changes were not
successful because they would create duplicate values in the index, primary
key, or relationship.

The subform is based on a table which uses an autonumber as the primary key
- which means (I thought) that Access automatically increments to a new
number when a new record is entered (thus, not duplicating the primary key).
Upon further review, when entering new records, the autonumber is not
incrementing, but is trying to reuse a previously assigned autonumber.

I have not seen this before - does anyone know why this would happen, and
how I can fix it?

Thanks so much for any suggestions!

 

[KARL DEWEY]

Which table has the autonumber - the main form table or the subform table?
The subform table should be using a Number - Long Integer (foreign key) in a
one-to-many relationship to the main form table primary key field
(autonumber).

 

[Linq Adams via AccessMonster.com]

Allen Browne has a short article on just this thing! Maybe it will help!

http://allenbrowne.com/ser-40.html

 

[Tammy]

Hi Karl,

Thanks so much for your response. Yes - the subform table uses the
autonumber. The design is good - the autonumber is bad.

I just read Allen Browne's article, which was suggested by Linq, and running
a compact and repair fixed the problem...I know I would never have thought of
that.

Thanks again!

 

[Tammy]

Hi Linq,

Thanks so much for your response, and for the link to the article. Running a
compact and repair fixed the problem! That was a new one to me, so your post
was very helpful. (And thanks to Allen Browne, too!)

Thanks again!