By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,401 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

Table Auto Number Problems

P: 49
I have a table that has the field "ID" set to auto number and also set as the primary key. Whenever I delete a record then go to add one, it will not re use the ID of the record I deleted. In other words say I have 125 records and I delete number 74, when I add a record instead of it giving the ID 74 it gives it the ID of 126. I need this to keep a certain number so is there a way around this? THANKS!!
Feb 12 '07 #1
Share this Question
Share on Google+
7 Replies

Expert 100+
P: 295
There are two fixes that I know of for this problem.

1. One create an exact copy of the table you have and only copy the structure not the data. Then append the data into this table and the count will restart. Use an append query to do this, only the data should be copied. Auto number is a stored procedure in access that was designed to account for unique controls that the database needed.

2. Setup your auto number programmatically using a module.

is auto number your unique ID???

if so you could lose realtionships with other tables.
Feb 12 '07 #2

P: 49
NO the ID is just there to keep track of how many records there are. And as for the first solution, I never use access and have no idea what you said, enlighten me.
Feb 13 '07 #3

Expert 100+
P: 295
1. For the first part what you want to do is right click on your table and select copy
2. Then you will have an option to copy data and structure or just structure. Select "The Just Structure option" This should give you an empty shell with no data but all the same fields you used. Make sure you name the table "Your Table Name-2".
3. Then go to queries.
4. Click on the "Create query in Design view" option
5. Select "Your Table Name" where your data is current stored.
6. Click Add and then go to query type in the top tool bar next to the red exclamation mark. It will be an icon that looks like two tables on top of each other. Select Append query from there, and it will ask you which table you want to append too. Select "Your Table Name-2" and click okay
7. Once this is done drag in drop all the fields except for your auto number field.
8. Finally click on the red exclamtion mark and say yes to all the defaults that it ask.

9. Verify that your data and your auto number are okay, and delete your old "Your table name" then rename your old "Your table name-2" to the deleted table.

Good luck

FYI programmatically would do you more justice than auto number
Feb 13 '07 #4

Expert Mod 10K+
P: 12,366
If you just want to know how many records there are in the table there are other ways of doing that without storing it in the table. One way is:
DCount("*", "Table Name").
Feb 13 '07 #5

P: 49
Well I tried doing that first step by appending the table but it still does the same thing. If I were going to do it programmatically would I do something like:

Count how many records there are>
For loop, adding numbers 1-how many records there are to the ID column on the table>
Feb 14 '07 #6

Expert 100+
P: 295
As long as you appended everthing except the auto number into a new table that should have worked.

Programmatically I would create a function called increment
Expand|Select|Wrap|Line Numbers
  1. Public Function Increment() As Long
  3. On Error GoTo NextID_Err
  5. Dim lngNextID As Long
  7.     'Find highest Employee ID in the tblPracticeEmployeeData table and add 1
  8.     lngNextID = DMax("[The Field you want to increment]", "Your Table") + 1 
  10.     'Assign function the value of the Next ID
  11.     Increment = lngNextID
  13.     'Exit function now after successful incrementing or after error message
  14. Exit_Increment:
  15. Exit Function
  17.     'If an error occurred, display a message, then go to Exit statement 
  18. NextID_Err:
  19.     MsgBox "Error " & Err & ": " & Error$
  21.     Resume Exit_Increment
  23. End Function
set a button to call this or you can do it to trigger on new records. Your choice
Feb 14 '07 #7

P: 49
This is how I ended up doing it. Thanks for the help.

Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rs1 As DAO.Recordset
  5.   Set db = CurrentDb() 
  6.   Set rs1 = db.OpenRecordset("Query1") 
  9.   rs1.MoveFirst 
  10.   i=1
  11.   Do Until rs1.EOF
  13.     rs1.Edit
  14.     rs1![ID] = i
  15.     rs1.Update
  16.     i=i+1
  17.     rs1.MoveNext
  18.   Loop
Feb 15 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.