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