Prev: DSum and Dlookup issue
Next: Reminder - Microsoft Responds to the Evolution of Community
From: Mark on 20 May 2010 10:16
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
From: Douglas J. Steele on 20 May 2010 11:21
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" <Mark(a)discussions.microsoft.com> wrote in message
news:9092D805-5C41-4799-8332-AFC78C763907(a)microsoft.com...
> 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


 | 
Pages: 1
Prev: DSum and Dlookup issue
Next: Reminder - Microsoft Responds to the Evolution of Community