Reset autonumber after deletion

[WillM]

Good afternoon,
We need to reset the autonumber after the last record is deleted.

For example:
2006
2007
2008

if 2008 gets deleted, we want the autonumber to reset back to 2008 rather than move to 2009.

Is this possible? This is the code we are using but cannot get the string to recognize the variable. If the variable is replaced with a number, it works, but it defeats the purpose of adding the plus 1 to the last autonumber.

Private Sub Command0_Click()

Dim RLMax As Integer
Dim Statement As String

RLMax = DMax("[id]", "Table1")
RLMax = RLMax + 1

strSQL = "Alter table table1 Alter Column Id Autoincrement(RLMax,1)"

DoCmd.RunSQL strSQL


End Sub

I realize we don't want to depend on the autonumber for anything other than a row identifier, but the table is setup that it is important for the rows to be sequential if the last record is deleted. Only if the last record is deleted.

Thanks,
Will

 

[Galaxiom]

Concatenate the value of the variable.

strSQL = "Alter table table1 Alter Column Id Autoincrement(" & RLMax & ",1)"

 

[vbaInet]

If you want it to attempt to go to next value, don't use an AutoNumber, just use a Number field and handle the increments with the DMax() + 1 value.

 

[Galaxiom]

I would not use autonumber either but DMax + 1 won't work when using an import spec.

Without knowing where the import comes from it is hard to comment but if it were text I would use VBA to parse it and increment the number.

 

[vbaInet]

Not sure where WillM mentioned it being an import Galaxiom. Perhaps you've got some insight on this matter?

 

[Galaxiom]

Not sure where WillM mentioned it being an import Galaxiom. Perhaps you've got some insight on this matter?

but the table is setup that it is important for the rows to be sequential

I read "important" as "import".

 

[vbaInet]

Hehe! I see.

 

[Galaxiom]

I was also primed to see it as "import" because I hadn't imagined someone would use an autonumber when they knew about DMax + 1 unless it was for an import.

 

[vbaInet]

Yes, it's an odd mix. Perhaps WillM didn't think it was possible to "auto increment" a different way.

 

[WillM]

Good morning, sorry for the delay in getting back to you all. The way the system was setup, before I inherited it, was that the auto-numbers were being used for identification of hard copies of files. Since there are many gaps in the numbers, we cannot reset them to do our own auto-increment.


Galaxiom - worked like a charm, thank you again!

I appreciate all the help, you guys are awesome!
Will

 

[vbaInet]

Because there are gaps doesn't mean that it cannot be changed, it's still very possible to switch it over to a Number data type. But since there are gaps, why then are you trying to fill in any gaps for future IDs?

 

[WillM]

vbaInet - the short of it is that there are lots of records that are tied to the "old" numbers that cannot be changed. Thousands upon thousands that are identified on the hardcopy by the auto-number ID that is already in the system. Since there are gaps, if we went to purely a Dmax+1 system, 1 would not always equal 1 if we reseeded and started the count over.

Yes, I realize we could start the count at the max of ID right now and go to Dmax+1 and achieve almost the same result. There are other moving parts to the puzzle (when aren't there?) that make it prohibitive.

We are eliminating gaps for future id's because the numbering system is still in place and we cannot change it, but we can clean it up and get the ordering of the records streamlined (ie: remove gaps in numbering of records).

I am not a fan of giving the ID any other purpose than row ID, however the system is in place and unfortunately I don't have the ability to change how they process those records and identify them.

Hopefully that answers some of your questions?

Thanks again!

 

[vbaInet]

Since there are gaps, if we went to purely a Dmax+1 system, 1 would not always equal 1 if we reseeded and started the count over.

Old IDs will be copied over from the Autonumber field to the Number field and the DMax()+1 numbering will take effect on new IDs. I.e. the gaps in old records will stay in tact.

...There are other moving parts to the puzzle (when aren't there?) that make it prohibitive.

... unfortunately I don't have the ability to change how they process those records and identify them.

But I see your challenge.