Autonumber Duplicates Problem

[John Baker]

Hi:

I have a table which is indexed on an auto number (supposedly unique for each record). For
reasons I cannot explain, I have started to encounter a problem with duplicate auto
numbers. The numbers went up to 250, and the suddenly the system started to try and
generate numbers starting at 150. Because the auto numbers duplicated, the system now will
accept NO new records!!

Can someone tell me how to fix this, either by giving a new starting base (say 1000) or
some other technique. Also, if a nee starting base HOW do i set it?

Thanks..Im stuck!

JOhn Baker

[Allen Browne]

Hi John

This is a known problem with the autonumbers in Access 2000 and later. There
are 2 steps to fixing it.

1. Run the code in this link:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html
This reset the Seed of the Autonumbers, which fixes the immediate problem.

2. Download the latest service pack for your version of Office from
support.microso ft.com. This prevents the problems recurring.

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

"John Baker" <Ba******@Veriz on.net> wrote in message
news:s4******** *************** *********@4ax.c om...

I have a table which is indexed on an auto number (supposedly unique for each record). For reasons I cannot explain, I have started to encounter a problem with duplicate auto numbers. The numbers went up to 250, and the suddenly the system started to try and generate numbers starting at 150. Because the auto numbers duplicated, the system now will accept NO new records!!

Can someone tell me how to fix this, either by giving a new starting base (say 1000) or some other technique. Also, if a nee starting base HOW do i set it?

Thanks..Im stuck!

JOhn Baker

[John Baker]

Thank you.

I have updated Access and reset the data base with an earlier version. Things appear to be
OK.

I am going to distribute this application to others in the not too distant future. Is it
likely that they will have the same problems (they have 2002 version with XP, I have 2000
with W98), and should I make the effort to update their systems with the patch too?"

Thanks for your help
John Baker

Allen Browne" <Al*********@Se eSig.Invalid> wrote:

Hi John

This is a known problem with the autonumbers in Access 2000 and later. There
are 2 steps to fixing it.

1. Run the code in this link:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html
This reset the Seed of the Autonumbers, which fixes the immediate problem.

2. Download the latest service pack for your version of Office from
support.micros oft.com. This prevents the problems recurring.

[Allen Browne]

It would be a very good idea to insist that they have the latest service
pack for their version of Access.

On the Help | About screen in my open Access applications, I always display
the version of Access and of Jet. Then if the user is experiencing a
problem, they can tell me what versions they are using. The two text boxes
have this Control Source:
=fGetProductVer sion(SysCmd(9) & "msaccess.e xe")
=fGetProductVer sion(fReturnSys Dir() & "\msjet40.d ll")
and the API-call functions can be copied from:
http://www.mvps.org/access/api/api0065.htm
http://www.mvps.org/access/api/api0010.htm

If by "districute this application" you are considering generating a runtime
installation, there are additional issues with the different versions of
Windows.

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

"John Baker" <Ba******@Veriz on.net> wrote in message
news:ql******** *************** *********@4ax.c om...

Thank you.

I have updated Access and reset the data base with an earlier version. Things appear to be OK.

I am going to distribute this application to others in the not too distant future. Is it likely that they will have the same problems (they have 2002 version with XP, I have 2000 with W98), and should I make the effort to update their systems with the patch too?"
Thanks for your help
John Baker

Allen Browne" <Al*********@Se eSig.Invalid> wrote:

Hi John

This is a known problem with the autonumbers in Access 2000 and later. Thereare 2 steps to fixing it.

1. Run the code in this link:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html
This reset the Seed of the Autonumbers, which fixes the immediate problem.
2. Download the latest service pack for your version of Office from
support.micros oft.com. This prevents the problems recurring.

[John Baker]

Allen:

I hate to tell you butb the problem has NOT been solved. Do you know som way I can reset
the seed for autonumber? Its still trying to issue autonumbers that duplicate the ones
already in the table!!

Im perplexed because it appeared to work yesterday.

Regards

John
"Allen Browne" <Al*********@Se eSig.Invalid> wrote:

It would be a very good idea to insist that they have the latest service
pack for their version of Access.

On the Help | About screen in my open Access applications, I always display
the version of Access and of Jet. Then if the user is experiencing a
problem, they can tell me what versions they are using. The two text boxes
have this Control Source:
=fGetProductVer sion(SysCmd(9) & "msaccess.e xe")
=fGetProductVer sion(fReturnSys Dir() & "\msjet40.d ll")
and the API-call functions can be copied from:
http://www.mvps.org/access/api/api0065.htm
http://www.mvps.org/access/api/api0010.htm

If by "districute this application" you are considering generating a runtime
installation , there are additional issues with the different versions of
Windows.

[Allen Browne]

The code in this link illustrates how to set Properties("See d") of a Column
in a Table of the Catalog:
http://allenbrowne.com/ser-40.html

Running that code after installing the update should solve the problem.

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

"John Baker" <Ba******@Veriz on.net> wrote in message
news:31******** *************** *********@4ax.c om...

Allen:

I hate to tell you but the problem has NOT been solved. Do you know som way I can reset the seed for autonumber? Its still trying to issue autonumbers that duplicate the ones already in the table!!

Im perplexed because it appeared to work yesterday.

Regards

John
"Allen Browne" <Al*********@Se eSig.Invalid> wrote:

It would be a very good idea to insist that they have the latest service
pack for their version of Access.

On the Help | About screen in my open Access applications, I always displaythe version of Access and of Jet. Then if the user is experiencing a
problem, they can tell me what versions they are using. The two text boxeshave this Control Source:
=fGetProductVer sion(SysCmd(9) & "msaccess.e xe")
=fGetProductVer sion(fReturnSys Dir() & "\msjet40.d ll")
and the API-call functions can be copied from:
http://www.mvps.org/access/api/api0065.htm
http://www.mvps.org/access/api/api0010.htm

If by "districute this application" you are considering generating a runtimeinstallation , there are additional issues with the different versions of
Windows.

[John Baker]

Allen:

I ran into the following error at compile time:

Dim Rst As DAO.Recordset
It said it was a user defined varaiable, and not defined.

Any thoughts on this?
Regards

John Baker
"Allen Browne" <Al*********@Se eSig.Invalid> wrote:

The code in this link illustrates how to set Properties("See d") of a Column
in a Table of the Catalog:
http://allenbrowne.com/ser-40.html

Running that code after installing the update should solve the problem.

[Allen Browne]

You need a reference to the DAO library.

From the code window, choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library.

More info on references:
http://allenbrowne.com/ser-38.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.

"John Baker" <Ba******@Veriz on.net> wrote in message
news:5q******** *************** *********@4ax.c om...

Allen:

I ran into the following error at compile time:

Dim Rst As DAO.Recordset
It said it was a user defined varaiable, and not defined.

Any thoughts on this?
Regards

John Baker
"Allen Browne" <Al*********@Se eSig.Invalid> wrote:

The code in this link illustrates how to set Properties("See d") of a Columnin a Table of the Catalog:
http://allenbrowne.com/ser-40.html

Running that code after installing the update should solve the problem.

[John Baker]

Allen:

I am totally perplexed. I have made the updates to Access, and run your routine.
Everything works fine until I Close th application, and reboot the system. At that point
the duplicate problem returns. You have been most helpful, and I am totally, utterly,
frustrated and have no idea as to what is happening or what I can do!
Regards

John Baker

"Allen Browne" <Al*********@Se eSig.Invalid> wrote:

You need a reference to the DAO library.

From the code window, choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library.

More info on references:
http://allenbrowne.com/ser-38.html

[Allen Browne]

John, re-reading this thread, I think you have Access 2000.
You have installed the service pack, so Help | About should show SP3?

Just to verify that the correct version of JET is running, search your hard
disk for msjet40.dll. You will probably find it in \windows\system 32, but
are there any other copies of this file on your drive? If there is another
copy in the Office folder or in Windows or somewhere on path, it is possible
that the wrong one is being loaded into memory.

After verifying that there is only one copy, right-click it in the Windows
Explorer and choose Properties. On the Version tab, what version do you see?
It should be 4.0.8015.0. If it is this version, then I'm at a loss to know
what to suggest further.

--
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.
"John Baker" <Ba******@Veriz on.net> wrote in message
news:8g******** *************** *********@4ax.c om...

Allen:

I am totally perplexed. I have made the updates to Access, and run your routine. Everything works fine until I Close th application, and reboot the system. At that point the duplicate problem returns. You have been most helpful, and I am totally, utterly, frustrated and have no idea as to what is happening or what I can do!
Regards

John Baker

"Allen Browne" <Al*********@Se eSig.Invalid> wrote:

You need a reference to the DAO library.

From the code window, choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library.

More info on references:
http://allenbrowne.com/ser-38.html