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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mk2" <(E-Mail Removed)> wrote in message
news:5C891231-E6F5-4ED5-9000-(E-Mail Removed)...
>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:

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" wrote:

> 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
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "mk2" <(E-Mail Removed)> wrote in message
> news:5C891231-E6F5-4ED5-9000-(E-Mail Removed)...
> >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
>
>
>

Answers in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mk2" <(E-Mail Removed)> wrote in message
news:96731699-3393-41EC-A6F8-(E-Mail Removed)...
> 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.)

>
> Thanks again!
>
> "Allen Browne" wrote:
>
>> 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
>>
>> "mk2" <(E-Mail Removed)> wrote in message
>> news:5C891231-E6F5-4ED5-9000-(E-Mail Removed)...
>> >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:

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!!

"Allen Browne" wrote:

> Answers in-line.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "mk2" <(E-Mail Removed)> wrote in message
> news:96731699-3393-41EC-A6F8-(E-Mail Removed)...
> > 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.)
>
> >
> > Thanks again!
> >
> > "Allen Browne" wrote:
> >
> >> 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
> >>
> >> "mk2" <(E-Mail Removed)> wrote in message
> >> news:5C891231-E6F5-4ED5-9000-(E-Mail Removed)...
> >> >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
>
>
>