Solved

Resume AutoNumber MS access 2007

Posted on 2011-02-28
3
327 Views
Last Modified: 2012-05-11
I have via query, re-inserted a record that was accidentally deleted. When the record was restored I have used the same PK number before the deletion. However, now when attempting to add a new record it appears that access is assigning an incremental number from the record that was restored and not from the Max of the PK + 1, therefore each new record will create a duplicate PK and no record can be added. How can I solve this problem? I cannot reset the PK because are related to other records. Example:
Total records 100
Record PK# that was restore was: 51
New Record PK: 52 (Wrong) instead of 101 (Correct)
0
Comment
Question by:Cobra967
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
Try a Compact and Repair. This will often reset the Autonumber Seed correctly.
0
 
LVL 14

Expert Comment

by:pteranodon72
Comment Utility
Try a Compact & Repair of the database. That should reseed the autonumber to the highest value + 1.

HTH,

pT72
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility


To set your own starting point and incrementing value, here is a method.
In all cases, make a BACKUP of your table first!!

** VBA Code method - Example**
Place this code in a standard module. Do not name the module the same as the Function below.

Public Function mResetAutoNumber(lStartVal As Long, lIncrement As Long) As String
   'Example: Set the Starting Value and Increment for a table named Table1ANTest and an AutoNumber field called AutoNum
    Dim sSQL As String
    sSQL = "ALTER TABLE [Table1ANTest] ALTER COLUMN [AutoNum] COUNTER (" & lStartVal & ", " & lIncrement & ");"
    CurrentDb.Execute sSQL
    mResetAutoNumber = "Auto Number has been re-numbered"
End Function

The first number (lStartVal) is the starting value, and the second (lIncrement) is the increment.
The example above will start the Auto Number at 1000 and increment by 50.

To run the code one time, open the VBA Immediate Window, and type
this line (including the question mark, followed by Enter:

?Function mResetAutoNumber(<YourStartValueNumber>, <YourIncrementNumber>)
Example:
?Function mResetAutoNumber(500,3)

You can run this against an empty existing table, or to modify the *next higher* Auto Number value and increment.
It will *not* change any existing auto number values.

This works for A2K and later.

mx
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now