Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 1 / July 2005

Tip: Looking for answers? Try searching our database.

Auto Number

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mike N. - 31 Jul 2005 01:36 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.