retrieving new value after insert

[warmduvet]

I have an insert query that inserts to a table that has an autonumber as pkey, the pkey is auto incremented when query runs. I then want to insert that new pkey value in another table. Both these insert queries run off the same button, after the first query runs I need some code or query that will show the new pkey value in a control on the form so I can then take that value and use it in the second insert query.
Hope this makes sense.

WD

 

[warmduvet]

thanks for the reply

this does the job

runs first...

DoCmd.RunSQL "INSERT INTO clientservices ( serviceid, stuid, orgid ) VALUES 
( " & serviceid & ", " &   stuid & "," & orgid & " )"

then this....

DoCmd.RunSQL "INSERT INTO invoice ( clientid, [date], total ) SELECT
 Max (clientservices.clientid) AS Maxclientid, #" & invdate & "# ," & total 
& " FROM clientservices, invoice"

WD

 

[warmduvet]

ok, fortunately this is a standalone db