Solved

Problem with auto number field inserting a record

Posted on 2014-01-17
8
489 Views
Last Modified: 2014-01-17
I have a table with an auto number as the primary key.  The first ID in the table is 5069.

I have a vba function to add a new record to the table, using addnew and update.  The function does not include a value for the ID field.  

When I run the function, the ID of the appended record is 50.  

How do I make the ID the next sequential number in the table?

Thanks in advance.
0
Comment
Question by:CRB1609
  • 4
  • 3
8 Comments
 
LVL 75
ID: 39790110
Is the AutoNumber the 'ID' you are referring to ?
And if so, as you saying it goes from  5069 to  50 ?
If So. what is before 5069 ? Are they sequential ?

mx
0
 

Author Comment

by:CRB1609
ID: 39790116
The auto number is the ID I'm referring to.
If I look at the table in ID sequence, the first record ID is 50, second record ID 5069, and the rest of the records follow on sequentially from 5069.
0
 
LVL 75
ID: 39790119
Did you delete a bunch of records at some point ?
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:CRB1609
ID: 39790121
Yes.
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 39790165
If you delete last records, then Compact and repair database, it restarts from last value of autonumber+1.
0
 

Author Closing Comment

by:CRB1609
ID: 39790176
Perfect, thank you.  Why didn't I think of that???
0
 
LVL 75
ID: 39790236
Sorry ... I had to leave work. hnasr beat me to the solution.
0
 

Author Comment

by:CRB1609
ID: 39790274
Sorry about that.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

839 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