Auto Number

[Mike N.]

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]

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

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

"Mike N." <Mi******@optonline.net> wrote in message
news:%1******************@fe08.lga...

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

[Ivan Carey]

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
"Mike N." <Mi******@optonline.net> wrote in message
news:%1******************@fe08.lga...

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