How to get last inserted ID in a table (1 Viewer)

crododo

Registered User.
Local time
Today, 13:25
Joined
May 2, 2013
Messages
27
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

Registered User.
Local time
Today, 20:25
Joined
Oct 25, 2011
Messages
699
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.

Code:
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

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

or
Code:
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

Registered User.
Local time
Today, 13:25
Joined
May 2, 2013
Messages
27
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

Registered User.
Local time
Today, 20:25
Joined
Oct 25, 2011
Messages
699
try this:

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

David
 

namliam

The Mailman - AWF VIP
Local time
Today, 21:25
Joined
Aug 11, 2003
Messages
11,696
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

Registered User.
Local time
Today, 13:25
Joined
May 2, 2013
Messages
27
This is exactly what I need, thanks a lot!
 

spikepl

Eledittingent Beliped
Local time
Today, 21:25
Joined
Nov 3, 2010
Messages
6,144
dim lngLastID as long

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

The solution using Dmax can fail.
 

MarkK

bit cruncher
Local time
Today, 13:25
Joined
Mar 17, 2004
Messages
8,178
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 . .
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
 

Users who are viewing this thread

Top Bottom