Get last Primary Key Value

[abichap]

Hi,
May I know how do I go about getting the last value of the primary key that I get inserted? I need this PK to link the parent and child tables together.
Thanks for any valuable help!

 

[KenHigg]

Are you using an autonumber data type?

 

[garethl]

i have done this using the max() function

that gives you the highest value of the field which you pass it as an argument so if you asked it for the highest value of your primary key field you would get the primary key of the last record inserted NOT the primary key of the next record to be inserted

i had problems with this using this with an autonumber key when i assumed that the primary key of the next record to be inserted would be this value + 1 as if a record is inserted and then deleted again the autonumbers won't remain sequential altough depending on your version of access you can reset that by compacting the db

in my situation however i was ok to insert into the table on the 'one' side of one to many relationship, then get the primary key using max(), then insert into the table on 'many' side of the relationship

 

[boblarson]

Hi,
May I know how do I go about getting the last value of the primary key that I get inserted? I need this PK to link the parent and child tables together.
Thanks for any valuable help!

If you use forms and subforms you won't need it. But, you can set a variable to the current autonumber in the form's Before Update event so that you can use it anywhere.

If you are trying to enter data directly in tables or queries, that is bad and should not be done as you give up a lot of control when you do that. You should use forms for entering data, if you aren't already, because you have control via events for doing things like this.

 

[RuralGuy]

Some additional thoughts:

From MVP Ken Snell
rst.AddNew
    rst.Fields("FieldName1").Value = "YourNewValue1"
    rst.Fields("FieldName2").Value = "YourNewValue2"
'  (etc. -- do not include the autonumber field as one that
'   is given a value)
rst.Update
rst.Bookmark = rst.LastModified
lngAuto = rst.Fields("AutonumberFieldName").Value

'---  Another approach

From MVP Allen Browne
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

 

[abichap]

Thank you guys for your kind replies! I have managed to solve the problem using a SELECT MAX(COUNT) to retrieve the last (which is the biggest in amount) key.

Thank you all for your valuable suggestions!