Getting the auto generated number.

Discussion in 'Microsoft Access Form Coding' started by Mark, May 20, 2010.

  1. Mark

    Mark Guest

    Hi All,

    I'm inserting a record into a table and have one field using the auto
    number. I need to get this number so I can email it. I'm using the code
    below to do this and it works fine. I just wanted to see if there is a
    better way of doing this.
    Thanks for the input!

    vSQL = "SELECT audit_history.audit_id " & _
    "FROM audit_history " & _
    "where Associate_id = '" & [txAssociate_id] & "'"

    Set rs = db.OpenRecordset(vSQL)
    rs.MoveLast

    vSQL = rs!audit_id
     
    Mark, May 20, 2010
    #1
    1. Advertisements

  2. What you're using really isn't a good idea in the first place! Using
    MoveLast only makes sense if you know the order in which the records are
    retrieved, and since you don't have an Order By clause in your SQL
    statement, you don't know that.

    Try just using

    vSQL = DMax("audit_id ", "audit_history ", _
    "[Associate_id] = '" & Me.txAssociate_id & "'")


    --
    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/DJSteele
    (no e-mails, please!)

    "Mark" <> wrote in message
    news:...
    > Hi All,
    >
    > I'm inserting a record into a table and have one field using the auto
    > number. I need to get this number so I can email it. I'm using the code
    > below to do this and it works fine. I just wanted to see if there is a
    > better way of doing this.
    > Thanks for the input!
    >
    > vSQL = "SELECT audit_history.audit_id " & _
    > "FROM audit_history " & _
    > "where Associate_id = '" & [txAssociate_id] & "'"
    >
    > Set rs = db.OpenRecordset(vSQL)
    > rs.MoveLast
    >
    > vSQL = rs!audit_id
     
    Douglas J. Steele, May 20, 2010
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Rhonda Fischer

    => Need to save auto generated supplierID to Join table

    Rhonda Fischer, Sep 22, 2003, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    188
    Rhonda Fischer
    Sep 22, 2003
  2. Frank Dulk

    number of the line that generated .

    Frank Dulk, Jul 31, 2004, in forum: Microsoft Access Form Coding
    Replies:
    3
    Views:
    189
    Dan Artuso
    Aug 1, 2004
  3. Guest

    Auto-enter a Subject in an Email Generated by Access

    Guest, Nov 29, 2004, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    316
    B. Comrie
    Dec 1, 2004
  4. gbwanabe via AccessMonster.com

    Random PO Number Generated with a Format Mask? Possible?

    gbwanabe via AccessMonster.com, Aug 31, 2006, in forum: Microsoft Access Form Coding
    Replies:
    9
    Views:
    246
    gbwanabe via AccessMonster.com
    Aug 31, 2006
  5. Elfae

    System generated combined number from existing fields - Long

    Elfae, Dec 12, 2007, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    187
    Jeanette Cunningham
    Dec 15, 2007
  6. Convert the number field to Auto number field.

    , Apr 8, 2008, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    875
    Douglas J. Steele
    Apr 8, 2008
  7. Dale Fye

    RE: Auto Generated incremental Number

    Dale Fye, Sep 15, 2008, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    793
    Dale Fye
    Sep 15, 2008
  8. Roger Carlson

    Re: Auto Generated incremental Number

    Roger Carlson, Sep 15, 2008, in forum: Microsoft Access Form Coding
    Replies:
    3
    Views:
    310
Loading...