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:
    150
    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:
    162
    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:
    245
    B. Comrie
    Dec 1, 2004
  4. Dale Fye

    RE: Auto Generated incremental Number

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

    Re: Auto Generated incremental Number

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

Share This Page