Table Auto Number Problems

[tehgreatmg]

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!!

[maxamis4]

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.

[tehgreatmg]

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.

[maxamis4]

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

[Rabbit]

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").

[tehgreatmg]

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>

[maxamis4]

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

Public Function Increment() As Long
 
On Error GoTo NextID_Err
 
Dim lngNextID As Long
 
    'Find highest Employee ID in the tblPracticeEmployeeData table and add 1
    lngNextID = DMax("[The Field you want to increment]", "Your Table") + 1 
 
    'Assign function the value of the Next ID
    Increment = lngNextID
 
    'Exit function now after successful incrementing or after error message
Exit_Increment:
Exit Function
 
    'If an error occurred, display a message, then go to Exit statement 
NextID_Err:
    MsgBox "Error " & Err & ": " & Error$
 
    Resume Exit_Increment
 
End Function
 

set a button to call this or you can do it to trigger on new records. Your choice

[tehgreatmg]

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

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
 
 
  Set db = CurrentDb() 
  Set rs1 = db.OpenRecordset("Query1") 
 
 
  rs1.MoveFirst 
  i=1
  Do Until rs1.EOF
 
    rs1.Edit
    rs1![ID] = i
    rs1.Update
    i=i+1
    rs1.MoveNext
  Loop