Can I retrieve the key of my record?

[Stapes]

Hi

I am creating a record using the following code:

Set qd = db.QueryDefs("Append Purchase Order")
qd.Parameters("pSUPPLIER").Value = [Forms]![Purchase Orders].Form.
[FK_SUPPLIER_CODE]

qd.Execute dbFailOnError

The key field in Purchase Orders is PK_PurchaseOrder.

Is there any way I can retrieve the key of the record just created?

Stapes

 

[Allen Browne]

Assuming these are Access (JET) tables in Access 2000 or later, try this
kind of thing:

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

 

[Stapes]

Assuming these are Access (JET) tables in Access 2000 or later, try this
kind of thing:

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

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi

I am creating a record using the following code:

Set qd = db.QueryDefs("Append Purchase Order")
   qd.Parameters("pSUPPLIER").Value = [Forms]![Purchase Orders].Form.
[FK_SUPPLIER_CODE]

   qd.Execute dbFailOnError

The key field in Purchase Orders is PK_PurchaseOrder.

Is there any way I can retrieve the key of the record just created?

Stapes- Hide quoted text -

- Show quoted text -

Brilliant. That works fine. I tried adding a field and priming it with
a random number. Then used the random number to find the new record
and get it's key. A bit long-winded but it worked. I prefer your
method.