Hello-
I have a database that uses an auto number field type that goes out of
sync periodically. My customer gets a "cannot add record, number already in
use" error message. I dump the records into a new table starting at auto
number 1, and sequentially up to about 20,000. Everything is fine for a
month or two, then the auto number field goes berserk again. Do you have
any ideas what the client may be doing to cause this problem, or is it
something in Access 2000? How many records can you add to an Access 2000
table before needing a better database?
Thanks again for your input.
Mike from Moriches
Allen Browne - 31 Jul 2005 04:47 GMT
Your client needs to install the free update for JET 4 (Service Patch 8)
from:
http://support.microsoft.com/gp/sp
There was a problem with the msjet40.dll distributed with the original
Access 2000. The problem was fixed long ago, but if your client has no
patches, it could still be the cause of the problem.
Installing the patch fixes the problem for the future, but you may also need
to run some code to fix the database that already has the problem. The code
in this link will let you do that:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

Signature
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.
> Hello-
> I have a database that uses an auto number field type that goes out of
[quoted text clipped - 7 lines]
> Thanks again for your input.
> Mike from Moriches
Ivan Carey - 31 Jul 2005 12:29 GMT
Hello Mike,
I found a similar problem and made a procedure to increment the primary key
each time a record is entered by 1.
This is attached to the data entry form.
The field refno is my primary key and needs to be incremented by 1 each time
a new record is entered. This reference number is then issued to the staff
member. It is very important that each number is sequential and independant
of the record number.
Since I have used this procedure I have had no further problems.
I hope this helps.
Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Err_Form_BeforeInsert
'this procedure increments the refno field
'autoincrement not used so that we do not rely on record numbers
Me.refno = Nz(DMax("refno", "tblDailyLog")) + 1
Exit_Form_BeforeInsert:
Exit Sub
Err_Form_BeforeInsert:
MsgBox Err.Description
Resume Exit_Form_BeforeInsert
End Sub
> Hello-
> I have a database that uses an auto number field type that goes out of
[quoted text clipped - 7 lines]
> Thanks again for your input.
> Mike from Moriches