Go to newest order after append query.

[CatchinOn]

I have a situation where our clients place orders, but then they may need
more accessories at a later date which would be a new order with the same
information, so I have an append query that copies all the order information
(except the materials ordered which is listed on a subform) into a new order
so we don't have to fill in the information twice.

My problem is that after I run the query i want to open the new record that
was just created. So I guess what I need to do is open the newest order for a
specific client.

Thanks for any suggestions, you guys always seem to get it right...

-H-

 

[Allen Browne]

If only one user is adding records at a time, and the table has an
AutoNumber field, you might be able to just use DMax() to get the highest
number, and assume that was the one just added.

You can get the most recently added number after you Execute an append query
like this:
Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close

Set rs = Nothing
Set db = Nothing
End Function

A simplier alternative is to open the form (since you want to show the
result anyway), and AddNew to its RecordsetClone. You can then set the
form's Bookmark to the LastModified of the clone set. This is the most
efficient solution.

Here's an example of doing that:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html
The examples appends the data to the form's RecordsetClone, and then uses
the new foreign key to append related records also.