Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2004
    Posts
    17

    Smile Unanswered: Getting the last number (autonumber)

    Hi~~

    I have a VB code to insert a new row to a table. Once the row is inserted, it comes with an autonumber for that row. How do I search the last autonumber so that I can insert the number to another table?

    Thanks so much for your help ~~

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    The easiest (and most reliable) way to do that depends on how you're adding the new record (you mentioned you're doing it in code).

    Can you supply a snipit of code?

    In general, simply searching for the MAX may not work, particularly in a multi-user.

  3. #3
    Join Date
    Mar 2004
    Posts
    17

    Smile

    hi~

    this is what i have to insert the first row

    Code:
    sSQL = "INSERT INTO cc(cc_number, cc_holder, cc_type, cc_expire) VALUES('" & Form![billing_cc_number] & "','" & Form![billing_cc_holder] & "','" & Form![billing_cc_type] & "','" & Form![billing_cc_expire] & "');"
    myDb.Execute (sSQL)
    This will create a row in table 'cc' with an autonumber called 'cc_id'

    Then next, I have

    Code:
    Dim cc_id As Integer
    ' ** HERE to look for the autonumber of the row inserted above and use it for the next query
    sSQL = "INSERT INTO orders(product_id, order_price, cc_id, [user]) VALUES(" & product_id & "," & product_price & "," & cc_id & "," & user & ");"
    myDb.Execute (sSQL)

    Thanks~~

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    A very reliable way would be to perform a lookup for the key feild using a unique value (or values) from the record you inserted

    Are one of these value unique?

    cc_number, cc_holder, cc_type, cc_expire

    If so, do a DLookup after the first insert with that value.

    If not, you can make a unique value: add a text field to the main table. Generate a unique vale (I like to use the Now() function as it gives the date/time down to the millisecond).

    Here's an example:

    Dim strKey as String
    strKey = Format(Now(),"mmddyyhhnnss") & Format(cc_number)

    Add the strKey to the table
    After the Execute SQL statement, use a DLookup function.


    May I suggest you look into Recordset objects? Recordsets give you the full functionality of the data base in a programmer friendly package.

    Have fun!

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select max(id) from table
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    select maxid wont work if another user inserts a record just after you but before you get maxid.

    the only sane way is to insert thru code: you are in a recordset and you issue the .addnew/.update and you are still on the same record... so you can read your new id

    in DAO-speak:

    dim dabs as database
    dim rex as recordset
    dim mynewid as long
    dim strSQL as string

    strSQL = "SELECT * FROM yourTable WHERE somethingUnlikely;"
    set dabs = currentdb
    set rex = dabs.openrecordset(strSQL)
    with rex
    .addnew 'your new id just got created
    !thisField = whatever
    !thatField = somethingElse
    .update
    'and i'm STILL in the new record
    mynewid = !whateverYourIDfieldIsCalled
    end with

    set rex = nothing
    set dabs = nothing

    ...and obviously choose somethingUnlikely to minimise the return since you have no need for the SELECTed records


    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    totally agree that select max(id) is bad

    totally disgree that code is "the only sane way"


    i am told that the best method is with @@IDENTITY

    sorry, i have no link to give you for where to find more info on @@IDENTITY


    tcace's method is basically querying back the row just inserted using the value of a candidate key, i.e. any unique column(s) which could have been chosen as the table's primary key instead of the autonumber surrogate key (and if necessary, tcace's idea of appending a timestamp is good)

    querying back by a candidate key is an excellent method, and it's reliable without any locking mechanism across queries, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2004
    Posts
    17

    Smile

    Originally posted by tcace
    A very reliable way would be to perform a lookup for the key feild using a unique value (or values) from the record you inserted

    Are one of these value unique?

    cc_number, cc_holder, cc_type, cc_expire
    No

    [SIZE=1]Originally posted by tcace
    If so, do a DLookup after the first insert with that value.

    If not, you can make a unique value: add a text field to the main table. Generate a unique vale (I like to use the Now() function as it gives the date/time down to the millisecond).

    Here's an example:

    Dim strKey as String
    strKey = Format(Now(),"mmddyyhhnnss") & Format(cc_number)

    Add the strKey to the table
    After the Execute SQL statement, use a DLookup function.
    Yes Thanks~ I have thought of using the time to generate a unique ID. Use it to insert a new row in cc table and order table.


    Originally posted by tcace
    May I suggest you look into Recordset objects? Recordsets give you the full functionality of the data base in a programmer friendly package.

    Have fun!
    Yes I will look into that~


    Thanks guyz~

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    well Rudy,

    i know it's dangerous to disagree with you (cos you are so regularly right), but i don't see any documentation on @@IDENTITY on the web and i know my recordset code route works.

    so (dammit, breath-held) i disagree with you.

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    @@IDENTITY (sql server)

    Intermediate Microsoft Jet SQL for Access 2000 -- scroll downabout half way to the section titled The @@IDENTITY variable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    @@IDENTITY variable listed in the previous SQL statements can be executed only through the Jet OLE DB provider and ADO
    told you it's dangerous to disagree with you, but my excuse is that i'm DAO not ADO.

    still holding my breath! izy
    currently using SS 2008R2

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you should try to breathe once in a while

    i have no idea (and no wish to learn, either) what the difference is between ADO and DAO

    although i do seem to remember something about how you have to use an asterisk in one and a percent sign in the other for the wildcard character, except i don't remember which is which

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yeah, it's all crap anyway. i'm off downstairs for a glass of wine, a hearty casserole, and some telly.

    by the way, you cannot believe how your
    WHERE 1=1
    changed my life (for the better)! so stupidly obvious once you've seen it, and so wonderfully useful.

    i regret that i have not yet experimented with
    WHERE 1=2
    but i guess that would fit the somethingUnlikely criteria in my (deprecated by you) code example.

    have a happy easter. izy
    currently using SS 2008R2

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks, and a pleasant first-sunday-on-or-after-the-first-full-moon-after-the-northern-hemisphere-spring-equinox non-denominational holiday to you too

    code is not deprecated

    it's just not "the only sane way"

    for example, in interfacing between the user and the user's data, if it can be done without code at all, it is a better solution than with code, and if there must be code, then the less code the better

    that is why sometimes a non-code answer is a better, dare i say, saner solution that one which involves code, even if the latter can be shown to be more efficient in certain circumstances

    not wishing to even think about what those circumstances might be, i too am off to find something to imbibe in...

    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
  •