AutoNum not incrementing

[SusanM]

This was working. Access 2000 on Windows 2000. I have 5 tables linked. One
of my tables has an AutoNum as primary key. When I go into my form to ADD a
new record, it used to go to a new record and add the record, giving it a new
AutoNum number. Now, when I attempt to add, the AutoNum field shows me an
AutoNum of 640 (which was previously used). This is giving me a Duplicate
Rcd error msg. I cannot add any record via the form or via the table. Pls
Help!! I did a compact/repair on both the database and the application. I
made sure all of the Windows and Office updates are current. I did a system
scan and virus updates.
One hint: Two wks ago I went on vacation and did a backup. When I test the
application against this backup, I can 'add'. The person who did daily
backups for me said there were no problems. --- But I have 2 wks of data that
has been updated and added to, so don't want to have to lose that if I can't
resolve this. They say this problem just occured today. Can anyone help?
thank you!

 

[Van T. Dinh]

See if the following Microsoft Knowledge Base article helps:

http://support.microsoft.com/kb/257408/en-us

 

[SusanM]

I read the article you recommended. It states their problem as AutoNum
creating duplicate numbers. My problem is that when I attempt to add a
record, Access2000 does not go to the last new number (as it always did) but
finds a number in the middle of the database and attempts to reuse it. That
is when I get the error msg that the record I am attempting to add cannot be
a duplicate record. Anyway, I did as the article recommended anyway, and
checked my version of Windows Jet and it is 4.0.9025.0, which equates to
Windows Server 2003 SP1 and Update Rollup for Windows 2000 - SP4. I have
Access2000 and Windows2000. Then I installed Windows 2000 kb829558 (which is
basicaly reinstalling the above), rebooted and tested my add problem, and it
still exists.
** I really need to get this fixed. Any other suggestions? I have never
had a problem with my system since I created it 14 yrs ago. Thanks again!

 

[SusanM]

I just had a thought. I created this system without coding (using wizards,
etc.). But is there a way that I can force the Add command to go to a
specified record (which would be the one after the last, as a new record).
If I could do this, then the AutoNum should begin incrementing correctly
again. I just tried to do this in my Macro, with GoTo, then the offset set
to 4859, which would be the new record. But it didn't work. I am hoping
there is another way to solve this.

 

[Van T. Dinh]

Susan

Can you confirm in the Table Design View that the Field is the AutoNumber
Field and not simply Long?

From what you described, I get a nagging suspicion that this is not an
AutoNumber Field. I have a feeling that this is simply a Field of Long data
type and your code / macro increment the number as new Records are added.

If that is the case, it is the code / Macros that you have to check to see
why it keeps re-allocating the same number ...

 

[SusanM]

I just checked the table and ApplId is my primary key, data type of Autonum,
in the bottom section under General, field size is Long Integer, new values
is Increment and indexed is Yes(no duplicates). What are your thoughts with
the above info?

 

[Van T. Dinh]

Susan

The settings are correct.

Make a back-up of the current database FIRST.

Try Compact and Repair *the Back-End* if you have a split application. If
this still doesn't help, try the JetComp utility available on the Back-End.

If that still doesn't work, you may have to go back to one of the earlier
back-ups that works or you may need to use one of the professional data
recovery service ...

If the database is not too big and the data is not confidential, compact
then zip then send to me at bigpond.com (same name as the
discussions.microsoft.com) and I have a look if time permits.

 

[Tony Toews]

This was working. Access 2000 on Windows 2000. I have 5 tables linked. One
of my tables has an AutoNum as primary key. When I go into my form to ADD a
new record, it used to go to a new record and add the record, giving it a new
AutoNum number. Now, when I attempt to add, the AutoNum field shows me an
AutoNum of 640 (which was previously used). This is giving me a Duplicate
Rcd error msg. I cannot add any record via the form or via the table.

Set AutoNumbers to start from ...
http://allenbrowne.com/ser-26.html

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[SusanM]

thank you. I do have a split application. I had done a compact/repair on the
back end (the database) and nothing changed. What is the JetComp utility --
do I go to Microsoft for that to download? I will try that next. My
application and data combined is 71,684 kb, so not large.

 

[SusanM]

thank you. I looked at the site but that is far from my level of expertise.
I created the system 14 yrs ago using wizards, etc -- no coding. But it
sounds like this is what I need to do (if I knew how).... I guess after I
get through this problem I should learn SQL, huh?

 

[SusanM]

okay, I found the JetComp Utility and used it on the back end (the database)
but I received 'error compacting db' -- there was no code or further error
msg. I tried changing the options, but same thing. I was really hoping this
would work.

 

[Van T. Dinh]

Susan

It is more than likely that you have corruptions in your Back-End ...

Try Tony Toews' Web pages on corruptions:

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[Tony Toews]

thank you. I looked at the site but that is far from my level of expertise.
I created the system 14 yrs ago using wizards, etc -- no coding. But it
sounds like this is what I need to do (if I knew how)....

All you need to do is to copy in the VBA code and run it. You don't
actually need to know VBA.

Whoops. I have you the wrong URL.
Fixing AutoNumbers when Access assigns negatives or duplicates
http://allenbrowne.com/ser-40.html

I guess after I
get through this problem I should learn SQL, huh?

Not really. Queries create SQL and I mostly work in the GUI.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

It is more than likely that you have corruptions in your Back-End ...

I'll respectfully disagree. Or rather that there is minor corruption
in that the autonumber seed value is wrong But Allen Browne's chunk
of code should reset it quite nicely.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Van T. Dinh]

OK, a minor corruption as you wrote.

Thanks for jumping into, Tony.

--
Cheers
Van T. Dinh
MVP (Access)

 

[SusanM]

Thanks so much to both of you. My problem remains that I do not do coding
and cannot seem to locate anyone locally who does. I created this system 14
yrs ago for my daughters new business and luckily never ran into a problem
that I needed to know how to code for. So.... since I didn't get any sleep
last night worrying about this....can you give me detailed info on how/where
to put this code or are you allowed to recommend a consulting company that
could fix this - asap?
Again, thank you.
(my not-so-quick fix would be to keep trying to add records, from where
autonum got 'stuck' which is at 686 and let it keep incrementing with the
duplicate err msg until it reaches 4876 and it finally has one that is not a
duplicate. Which, I approximate would take me 35 hrs....entering 1 every 30
seconds. (but I am getting that desperate)

 

[SusanM]

Its Fixed!! Well, I did an append, renamed the table and it worked. Then
when I replicated the fix on my work computer, I could add a new record to
the table but not the form, so I transferred my application from home to the
office and it is fine. Evidently the application was only corrupted on my
work computer and not on any one else's because they could add fine. Don't
know what caused this but sure glad it is fixed. Thank you yet again.