How to get last inserted ID in a table

[crododo]

Hi,

I have this code in Access VBA and it is not working, I hope somebody can give me some help on this:
Private Sub btnAddWorkingday_Click()
Dim strSQL As String
Dim strDate As Integer
Dim lastID As Integer

strSQL = "INSERT INTO tblSchoolWorkingDays (CALENDAR_DATE) VALUES ('" & tBoxDateToAdd.Value & "'); SELECT @@IDENTITY AS LastID;"

DoCmd.RunSQL strSQL

End Sub

Thanks in advance.

 

[DavidAtWork]

Looks to me like there are both errors in syntax and object referencing, also where is the value for LastID coming from, don't understand the "SELECT @@IDENTITY AS LastID" at all, are you trying to select it from another table
Your INSERT statement states inserting a value into only one field but then you go on to state 2 values.

strSQL = "INSERT INTO tblSchoolWorkingDays (CALENDAR_DATE) VALUES ('" + format(Me.tBoxDateToAdd) + "')"

this would work just to add a date when tblSchoolWorkingDays has an autonumber field LastID

If you're meaning to add this value LastID which I assume is coming from your declared variable, then it should be

strSQL = "INSERT INTO tblSchoolWorkingDays (CALENDAR_DATE, IDfield) VALUES ('" + format(Me.tBoxDateToAdd) + "', '" + str(LastID) + "')"

or

strSQL = "INSERT INTO tblSchoolWorkingDays (CALENDAR_DATE, IDfield) VALUES ('" + format(Me.tBoxDateToAdd) + "', " & LastID & ")"

both should work, but I prefer the former of these 2 methods

David

 

[crododo]

Hi,

Thanks for your anwser, my mistake I did not explain in more details.

Table tblSchoolWorkingDays has two field one is [DATE] and another is [ID], this [ID] is autoincrecement number and after I add new [DATE] in this table I need to store value of this new [ID] number in a sting so I can use it in some other operation, so my problem is how to get the value of last inserted [ID] in tblSchoolWorkingDays.

Hope this is possible

Thx

 

[DavidAtWork]

try this:

Dim lastID As Integer
strSQL = "INSERT INTO tblSchoolWorkingDays (CALENDAR_DATE) VALUES ('" + format(Me.tBoxDateToAdd) + "')"
Docmd.RunSQL(strSQL)
lastID = DMax("[ID]","tblSchoolWorkingDays")

David

 

[namliam]

dmax("[ID]","tblSchoolWorkingDays")

or you can use a "proper" query to retrieve it but that is a bit more convoluted

Pft, shouldnt leave stuff open for to long when david is around.

 

[crododo]

This is exactly what I need, thanks a lot!

 

[spikepl]

dim lngLastID as long

lngLastId = CurrentDb.OpenRecordset("select @@Identity from myTable")(0)

The solution using Dmax can fail.

 

[MarkK]

DMax() might fail if another user inserts a record. If you use "SELECT @@Identity" on the connection or database object against which you ran your update, then you are guaranteed to get the correct ID. Consider this DAO code . .

Private Sub Test192674914368()
    Dim lastID As Long
    With CurrentDb
        With .CreateQueryDef("", _
            "INSERT INTO tblSchoolWorkingDays " & _
                "( CALENDAR_DATE ) " & _
            "VALUES " & _
                "( [prm0] )")
            .Parameters(0) = Date
            .Execute dbFailOnError
            .Close
        End With
        lastID = .OpenRecordset("SELECT @@IDENTITY").Fields(0)
    End With
End Sub

Here we implicitly create the database reference, and then a querydef, which we execute and close, but then still using the same database object reference (inside the With block) we open a recordset on that same dao.database object (CurrentDb). In this case "SELECT @@IDENTITY" is guaranteed to return the Primary Key value.

Rather than use With blocks you can also create object references and use those, and as long as you use the same database reference, @@Identity will return the last ID. Note that CurrentDb returns a different dao.database reference every time it is called.

hth