Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    4

    Unanswered: How to fetch value of Last allotted Autonumber

    Hi,
    I am using Autonumber field in Access as a primary Key. What i need is to get the value of the next allotted autonumber.

    I have got two tables and i want to insert some data into the second table relating it with the record of the first one(with autonumber as primary key). So i need to know the value of the number generated.

    Also as the users in the system are concurrent i cant use the query to get the maximun number in the database.

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by sanjay_raghani
    Hi,
    I am using Autonumber field in Access as a primary Key. What i need is to get the value of the next allotted autonumber.

    I have got two tables and i want to insert some data into the second table relating it with the record of the first one(with autonumber as primary key). So i need to know the value of the number generated.

    Also as the users in the system are concurrent i cant use the query to get the maximun number in the database.
    Hi,
    Not sure if I understand you correctly, but you already have the AutoNumber field and you wish to know the NEXT number? That field will populate the very next number the very instant you start a NEW record.
    As far as the two tables go, do you already have your Relationships set up between the two with the PrimaryKey/ForeignKey???

    Bud

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there are two methods

    the first, and recommended way, is to use @@identity, which apparently works in access the same way it does in sql server

    the second is to query back the just-inserted row of the first table using values from the non-autonumber fields

    so insert into table1(name) values ('Joe Bfstplk')

    and then select id from table1 where name='Joe Bfstplk'

    see The surrogate key of the row just inserted
    (site registration may be required, but it's free)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •