Finding key for newly created record

[ugly]

When I double click on a record within my data sub sheet form a duplicate record is created. Access seems to allocate a new (auto number) for the key of the new record. I need to get hold of that key for the newly created record. In order that I can connect table units to another related table.

Private Sub unitKey_DblClick(Cancel As Integer)

sSQL = "INSERT INTO units(unit)" & _
" VALUES('" & unit & "');"
DoCmd.RunSQL sSQL
End Sub

 

[Remou]

How about:
thread700-1127445

 

[ugly]

Thanks Remou
If I use "me.nameofunitkey" it returns the id of the record that I am copying from, I guess I could increment this by one to get the id of the new record, but is there a better way, I am doing this via an insert statement so I do not have a reference to the new record to retreive its id.

 

[PHV]

What about this ?
newID = DLookUp("@@Identity","units")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ugly]

Thanks for the help the problem remains that this code returns the id of the unit that I am copying from – I want to obtain the id of the newly created unit? I should mention that unit appears as a subform of another form so that when the new record is created it does not appear since it is unassociated