Auto Number Problem

[Guest]

I have a table that has the Auto Number primary key set to increment. It has
been working fine for a couple of years. I have 573 records in this table. I
have had no problems adding new records until today. For some reason, when I
try to add a record, the ID# is coming up 510, which is a duplicate entry.
(Since this is a primary key field, I have the Index property set to Yes (No
Duplicates).)

Why all of a sudden does Auto Number not recognize that the next new record
should be 574? Moreover, how do I fix it?

Thanks in advance for your help.

 

[Allen Browne]

All recent versions of Access (2000 onwards) are subject to this kind of
problem.

For a discussion of some of the causes and how to fix the problem, see:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

 

[Guest]

Allen:

I have followed the instructions on your website. My Jet 4.0 release level
is 4.0.8618.0 - Windows XP SP2 and Security Bulletin MS04-014. Does this
count as SP 7 or Later?

Assuming it does, I got to the point of running the Debug. I am getting the
following error message from within your function code:

Compile Error:
User-defined type not defined

It is highlighting the following line -
Dim cat As New ADOX.Catalog

I also have a couple of other questions stemming from your instructions. I
apologize for my lack of knowledge in the area of VBA.

1. Do I need to save the module after I have copied the function code into it?
2. After I hit Control + G to pull up the immediate window and insert "?
AutoNumFix()", what do I do? Does is run automatically or do I need to Close
and Return To Access for it to run?

Thanks again!

 

[Allen Browne]

Answers in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

I have followed the instructions on your website. My Jet 4.0 release level
is 4.0.8618.0 - Windows XP SP2 and Security Bulletin MS04-014. Does this
count as SP 7 or Later?

The 8 indicates you have SP8. No further action needed on this point.

Assuming it does, I got to the point of running the Debug. I am getting
the
following error message from within your function code:

Compile Error:
User-defined type not defined

It is highlighting the following line -
Dim cat As New ADOX.Catalog

You missed step 4 in the instructions.

I also have a couple of other questions stemming from your instructions. I
apologize for my lack of knowledge in the area of VBA.

1. Do I need to save the module after I have copied the function code into
it?

Only if you want to keep it. Once the problem is fixed, you don't have to
have this code in your database if you don't want to. It will run without
saving the module.

2. After I hit Control + G to pull up the immediate window and insert "?
AutoNumFix()", what do I do? Does is run automatically or do I need to
Close
and Return To Access for it to run?

Just type the line and press Enter to get it to run.
(Obviously you have to fix the compile issues before you get to here.)

 

[Guest]

Allen:

Hmmmm...I had done that step. But when I went back the box was not checked.
So I did it again and now it works.

Thank you so very much!!