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

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" <(E-Mail Removed)> wrote in message
news:9092D805-5C41-4799-8332-(E-Mail Removed)...
> 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