How to get the last value of an AutoNumber Column

[saurabhpunn]

Hello All,

I've a table that has 3 Columns:

Column A : Type - Auto Number.

Column B : Type - String.

Column C : Type - String.

The Current Data in the table is as follows:

1, "USA","Florida"
2, "USA, "Michigan"
3, "USA", "New York"

If a add new Row, the Column A, being Auto Number, takes my Count to 4 i.e.

4, "USA", "Texas"

Suppose I've deleted Row 3 and Row 4. The table data is now:

1, "USA","Florida"
2, "USA, "Michigan"

If I try to add a new Row, the Column A, being Auto Number, takes my Count to 5 (instead of 3). Its OK as Auto Number, by default, will follow this numbering.

My question is: Is there a way to programmatically figure out that my row is going to get added at 5, before I actually insert a row and figure it out by myself using @@Identity?

Thanks.

[RuralGuy]

It sounds like you are using an AutoNumber field for a purpose for which it was not designed. http://www.utteraccess.com/forum/Aut...t-t443604.html
Maybe this is what you want: http://www.baldyweb.com/CustomAutonumber.htm

[saurabhpunn]

Hi RuralGuy,

I tried the thread u have given to search into. It talks about DMax function to get the largest value

However, it only works if I do a regular "Insert Only" operations. If I add some rows and delete them, it returns last maximum value of the column.

Is there no other way to figure out the correct auto number value in case of delete operations?

[RuralGuy]

The first link suggests that AutoNumber fields are not for public consumption. If it is important, why not just do your own field?

[saurabhpunn]

Hi Rural Guy,

I don't have options because the Database I'm working on is having this structure and it cannot be changed.

The example is just an example to get my point through.

Is there no solution to the problem that I've mentioned?

[RuralGuy]

I guess I would then wonder why it is important to know this value? Are you planning to reuse the deleted values?