[AccessD] Getting the Primary Key of a New records in Oracle

That looked really promising, but as soon as the nPrimKey = ![ID] line executed I got an error 3167: Record is deleted. ;-(

Sub GetLastModfied()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim nPrimKey As Long

    Set db = curdb
    Set rs = db.OpenRecordset("IB_Amounts", dbOpenDynaset)
    With rs
        .AddNew
        !CovId = "Test"
        !strComment = "Testing getting primary key"
        .Update
        .Bookmark = .LastModified
        nPrimKey = !ID
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
    Debug.Print nPrimKey, DLookup("Description", "IB_Amounts", "ID=" & nPrimKey)
End Sub

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rusty Hammond
Sent: Tuesday, November 05, 2013 4:49 PM
To: Access Developers discussion and problem solving; ACCESS-L at PEACH.EASE.LSOFT.COM
Subject: Re: [AccessD] Getting the Primary Key of a New records in Oracle

Lambert, use the LastModified property:

     With rst
	  .AddNew
             ![SomeField1] = "SomeValue"
             ![SomeField2] = "SomeValue2"
        .Update
        .BookMark = .LastModified
        intPriKey = ![PrimKeyID]
    End With

HTH,

Rusty

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Tuesday, November 05, 2013 3:14 PM
To: 'Access-D Email (accessd at databaseadvisors.com)'; 'ACCESS-L Email(ACCESS-L at PEACH.EASE.LSOFT.COM)'
Subject: [AccessD] Getting the Primary Key of a New records in Oracle

Does anyone know how I can retrieve the primary key value from a new record just added to a linked Oracle table using VBA?

I.e. I create a records using VBA, populate the fields with VBA and update the record. How do I then find out what the primary key value allocated by Oracle is? The key field is of type NUMBER(10) and it is populated by a trigger (which I have no control over).

Lambert
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
**********************************************************************
WARNING: All e-mail sent to and from this address will be received, scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
corporate e-mail system and is subject to archival, monitoring or review by, and/or disclosure to, someone other than the recipient.
**********************************************************************

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com