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    
post May 30 2008, 12:49 AM

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
Start new topic
post May 30 2008, 01:35 AM

UA Admin
Posts: 35,841
Joined: 20-June 02
From: Newcastle, WA

See if the information in this KB Article helps.

I've also had success with the following syntax to retrieve the identity value:

Dim rst as DAO.Recordset

(Your other code here to insert records)

Set rst = CurrentDb.OpenRecordset("Select @@Identity as IdentityColumn from POTable")
strNewPOID = rst!IdentityColumn
set rst =Nothing

You need to alias the identity value in the SQL to retrieve it from the table so that you can address it properly in the returned recordset.

Go to the top of the page

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    13th October 2019 - 07:57 PM