Improve company productivity with a Business Account.Sign Up

x
?
Solved

Resume AutoNumber MS access 2007

Posted on 2011-02-28
3
Medium Priority
?
348 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 86

Accepted Solution

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

Expert Comment

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

HTH,

pT72
0
 
LVL 75
ID: 35000615


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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

601 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