Can't seem to retrieve primary key number of newly created record in VB

[plh]

I'm using MS Office Pro 2010. I'm creating a new record using INSERT
INTO. I need the primary key number of that newly created record to do
something else. Can't seem to get it. I keep getting primary key
number of the NEXT TO last recird, not the newly created one.
As you can see below I tried 2 requery methods, but even that did not
do it:
Here is the code.

Private Sub cmdDuplicate_Click()
On Error GoTo Err_cmdDuplicate_Click
Dim strSQL As String

Dim lngID As Long
Dim lngIDNewPart As Long
Dim r As Recordset

lngID = Me.txtID.Value

strSQL = "INSERT INTO Partlist " _
& "SELECT Name, Family, TurnedSize, Style, Channel1Prog, " _
& "Channel2Prog, StockDia, Channel1Integer " _
& "FROM Partlist WHERE IDPart = " & lngID & ";"

DoCmd.RunSQL strSQL

DoCmd.Requery

Set r = Me.RecordsetClone
With r
r.Requery
r.MoveLast
lngIDNewPart = r.Fields("IDPart")
End With

lngIDNewPart ends up with 411 which is the next-to-the-last. The last
is now up to about 416 due to creating and deleting the new record as
I tried stuff. Help!
Thank You,
-plh

.....

 

[Vacuum Sealed]

I'm using MS Office Pro 2010. I'm creating a new record using INSERT
INTO. I need the primary key number of that newly created record to do
something else. Can't seem to get it. I keep getting primary key
number of the NEXT TO last recird, not the newly created one.
As you can see below I tried 2 requery methods, but even that did not
do it:
Here is the code.

Private Sub cmdDuplicate_Click()
On Error GoTo Err_cmdDuplicate_Click
Dim strSQL As String

Dim lngID As Long
Dim lngIDNewPart As Long
Dim r As Recordset

lngID = Me.txtID.Value

strSQL = "INSERT INTO Partlist " _
& "SELECT Name, Family, TurnedSize, Style, Channel1Prog, " _
& "Channel2Prog, StockDia, Channel1Integer " _
& "FROM Partlist WHERE IDPart = "& lngID& ";"

DoCmd.RunSQL strSQL

DoCmd.Requery

Set r = Me.RecordsetClone
With r
r.Requery
r.MoveLast
lngIDNewPart = r.Fields("IDPart")
End With

lngIDNewPart ends up with 411 which is the next-to-the-last. The last
is now up to about 416 due to creating and deleting the new record as
I tried stuff. Help!
Thank You,
-plh

....

Hey there

It's been a while since I've hacked into Access VBA, but you might try
it this way.

strSQL = "INSERT INTO Partlist " _
& "SELECT Name, Family, TurnedSize, Style, Channel1Prog, " _
& "Channel2Prog, StockDia, Channel1Integer " _
& "FROM Partlist WHERE Max(IDPart) = " & lngID & ";"

If memory serves me, and the syntax is correct then this should
essentially be asking the PartList to return the Max (Highest) number in
the recordset that matches lngID.

The other thing would be to update the recordset after the new value has
been entered.

With r
r.Update
r.Requery
r.MoveLast
lngIDNewPart = r.Fields("IDPart")
End With


I'm certain one of the many other MVP Guru's will correct any error on
my part for you.

HTH
Mick