Can I retrieve the key of my record?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stapes - 21 Jan 2008 11:59 GMT

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

Reply to this Message

Allen Browne - 21 Jan 2008 12:06 GMT

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

Signature

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
>
[quoted text clipped - 11 lines]
>
> Stapes

Reply to this Message

Stapes - 21 Jan 2008 14:09 GMT

> Assuming these are Access (JET) tables in Access 2000 or later, try this
> kind of thing:
[quoted text clipped - 36 lines]
>
> - 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.

Reply to this Message