UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Using DLookup to find the @@IDENTITY of a new Insert    
 
   
Stherrien
post May 30 2008, 12:49 AM
Post#1



Posts: 34
Joined: 3-April 08



I'm copying a purchase order, which is a PurchaseOrder(PO) record and 1+ item (POItems).
I have some sql that you pass an Identity in and a near identical record is inserted into the table (pretty much the same record with a different Identity).
Now After this, I need to do a similar query where I find all POItems that belonged to the PO by ID and make copies of the records but with the new PO's ID.
So in between the two SQL commands run, I need to retrieve that new records Identity.
Online in my searches I've found that you run "SELECT @@IDENTITY" to get this, which is what you do in SQLServer. Now the only way I know how to Select into a value is with DLookup:
' Inserts a new PO into table POTable
DoCmd.RunSQL (strCopyPO)
' Retrieves newly inserted identity.
strNewPOID = DLookup("@@IDENTITY", "POTable")
My first realization is that DLookup requires a table... but "SELECT @@IDENTITY" doesn't have a table in the statement, so I used the table I was selecting from. Maybe theres a system table?
Oalways get the value of 577 returned, no matter how many records I copy. There are over 6,000 records in POTable and the ID is up over 6,000 right now.
So, can anyone please give me some advice on how to retrieve this last value and place it into a variable? Thanks very much in advance sad.gif
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    23rd September 2019 - 04:44 AM