getting an autonumber field to go beyond 3999

[BigJohn]

I'm new at this and self-taught. My Master Item Table has a unique key field
and is set to autonumber. It has reached 3999 and is now trying to assign a
number already used instead of going beyond 3999 when adding a new record. I
appended the records into an empty table without appending the autonumber
field values and a new record is added with no problem. It is probably
something I know nothing about but every beginning programmer learns in
school. Any help. Incidentally, I'm storing information in two other tables
(inventory and invoicing) using the autonumber field in the master item
table. I do appreciate any helpful comments.

 

[Wayne-I-M]

Sound a little strange ?

It "may" that what you are seeing is the primary field of the main table but
on a record of the linked table?
This would give you the effect of seeing an auto number field that is being
"re-used" although it isn't.

Try opening the table (the one you think is reusing numbers) and just add a
few records on to the bottom of the table (so the auto number goes up past
3999). Don't do this in any forms or querries but in the table itself. Then
at least you'll know if this is the problem or something else.

Post back with the results.

 

[BigJohn]

Greetings Wayne-I-M,
Thanks for the help. I tried your suggestion and the same result occured.
I even deleted the relationships to the other two tables with the same
result; not being able to add a new record. I created a mirror image table
and appended the records from the old table, sans the key field, and adding
records then worked. My program currently deletes a record from the table
when requested by the user instead of identifying it as deleted but yet
keeping it in the table. could the gaps between records be confusing Access?
Then, maybe it's a limitation of the program itself or I'm not telling the
program to keep building the table beyond a certain point by omitting an
instruction somewhere. This program is a single user desktop application
using WinXP with the tables in a separate .mdb from the code. I took this
project over from someone when they departed unceremoniously so I'm learning
to swim while drowning as they say. LOL. Thanks for the insights.

John

 

[Wayne-I-M]

I have never heard of an autonumber field stopping at ###.

Try this. Copy the table thats having problems.
On the copy - open it and select all records then paste append.
What happens - it "should" create new records with new autonumbers - so
you'll end up with almost 8000.

If not then you have a problem that you'll some advice from one of the
experts on this forum.

Also - worth a try. Do the same with a new table.
Create a table with just field (autonumber) and another (Text)
add some text to the 1st record - the autonumber field should 1
copy this and paste append (now you have 2 records)
selectr all copy paste append (now 4)
kep doing the same (8, 16, 32, 64 records) until you have more than 3999 and
see what happens.

Post back with the results

 

[Banana]

Allen Browne wrote about how autonumber could start to behave
erroneously, and the fix for it.

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

See if it helps.

 

[BigJohn]

greetings,
Ok, back from the Labor Day Holiday weekend. Thanks for the advice and
testing suggestions. They really helped me. "banana" sent a link to an
article by Allen Browne concerning just such a problem along with some help
in clearing up the problem. since I inherited this challenge from someone
else, I don't know how the tables were treated in the past but from the data
it sure sounds like it is a seed problem in the orginal table. I have
managed to get the table reset and has been working fine since start of
business today. The table has increased to 4659 just today alone so I'm
happy. You will probably see me here again in the future when I begin
converting this program from an .mdb to a project using MS Server SQL
language. I will be attending the local community college prior to that for
two courses involving Access and SQL Server programming. Thanks again for
all your help in this issue. Have a great day today and a better one
tomorrow. John