Results 1 to 6 of 6
  1. #1
    saurabhpunn is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    4

    How to get the last value of an AutoNumber Column

    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.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    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

  3. #3
    saurabhpunn is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    4

    Re:

    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?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The first link suggests that AutoNumber fields are not for public consumption. If it is important, why not just do your own field?

  5. #5
    saurabhpunn is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2012
    Posts
    4

    Re:

    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?

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I guess I would then wonder why it is important to know this value? Are you planning to reuse the deleted values?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 01-13-2012, 09:17 AM
  2. Replies: 1
    Last Post: 12-08-2011, 08:03 AM
  3. Replies: 2
    Last Post: 12-05-2011, 04:53 AM
  4. Replies: 3
    Last Post: 09-28-2011, 04:29 PM
  5. Replies: 1
    Last Post: 04-15-2010, 02:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums