Solved

Problem with auto number field inserting a record

Posted on 2014-01-17
8
490 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 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