Finding Record ID of newly inserted record (1 Viewer)

[JMongi]

Dim db As DAO.Database
Dim strSQL As String
Dim lngNewID As Long

Defining a DAO database object, string variable and long variable

Set db = CurrentDb()

Creating/assigning an instance based on the current db.

strSQL = "INSERT INTO tblDrawings(Description) VALUES('Sample Drawing')"

Setting the string of the SQL command

With db
.Execute strSQL, dbFailOnError

Executing the SQL string with a setting about handling error messages I believe.

lngNewID = .OpenRecordset("SELECT @@IDENTITY")(0)
End With

Assigning a number ID to the variable.
So, what is the syntax of the @@IDENTITY portion. I must not recognize the syntax of the VBA .OpenRecordset. I recognize the SELECT @@IDENTITY from the tutorial but not how it is actually being implemented.

MsgBox "One new record was added to tblDrawings with the assigned new ID of " & lngNewID, vbInformation, "Done!"

Demoing the use of the shiny new ID.

Set db = Nothing

Closing the recordset.

So, would this still work with SQL server? I've seen the db.execute methodology as well as the DoCmd.RunSQL methodology. Is one more compatible with migrating to SQL server than the other?

 

[theDBguy]

So, would this still work with SQL server? I've seen the db.execute methodology as well as the DoCmd.RunSQL methodology. Is one more compatible with migrating to SQL server than the other?

Yes, that should still work with SQL Server (although I can't verify it right now, since I don't have a SQL Server instance available where I am currently, but I believe I've used it before).

The difference between using the Execute and RunSQL methods is that you will get the warning message when you use latter.

 

[JMongi]

Here is the code I came up with. I still get prompted for all of the variables in the SQL statements. Must be forgetting something about using them.

Private Sub Form_AfterInsert()
Dim RevDate As Date
Dim strSQL As String
Dim lngLastID

'Add a new record to tblDrawingRevisions
strSQL = " INSERT INTO tblDrawingRevision " _
       & "(DrawingFK, Revision, Reason) VALUES " _
       & "(Me.DrawingID, '0', 'New Drawing');"
Debug.Print Me.DrawingID
Debug.Print strSQL
DoCmd.RunSQL strSQL

'Get the ID of the most recent record in tblDrawingRevisions
lngLastID = CurrentDb.OpenRecordset("SELECT @@IDENTITY from tblDrawingRevision")(0)
Debug.Print lngLastID

'Add a new record into the revision status table
'1 is the Status code for "In Process"
RevDate = Date
strSQL = " INSERT INTO tblRevisionStatus " _
       & "(Status, UpdateOn, UpdatedBy, RevisionFK) VALUES " _
       & "('1', RevDate, GetDomainUsername(), lngLastID);"
Debug.Print RevDate
Debug.Print strSQL
DoCmd.RunSQL strSQL
DoCmd.Close acForm, Me.Name

End Sub

 

[Pat Hartman]

@JMongi I told you why I use DAO. Using an append query is the equivalent BUT if you do that, then you MUST use @@Identity to get the ID whereas if you are working with Jet/ACE and will not ever convert, then you can use the line that is commented out instead. The RDBMS and whether you use DAO or an append query affect your options for retrieving the ID of the record you just inserted.

 

[JMongi]

Ok, I had a a lot of syntax issues with my SQL. Still having issues but here is the latest:

Private Sub Form_AfterInsert()
Dim RevDate As Date
Dim strSQL As String
Dim lngLastID

'Add a new record to tblDrawingRevisions
strSQL = " INSERT INTO tblDrawingRevision " _
       & "(DrawingFK, Revision, Reason) VALUES " _
       & "('" & Me.DrawingID & "', '0', 'New Drawing');"
Debug.Print Me.DrawingID
Debug.Print strSQL
DoCmd.RunSQL strSQL

'Get the ID of the most recent record in tblDrawingRevisions
lngLastID = CurrentDb.OpenRecordset("SELECT @@IDENTITY from tblDrawingRevision")(0)
Debug.Print lngLastID

'Add a new record into the revision status table
'1 is the Status code for "In Process"
RevDate = Date
strSQL = " INSERT INTO tblRevisionStatus " _
       & "(Status, UpdatedOn, UpdatedBy, RevisionFK) VALUES " _
       & "('1', " & RevDate & ", GetDomainUsername(), '" & lngLastID & "');"
Debug.Print RevDate
Debug.Print strSQL
DoCmd.RunSQL strSQL
DoCmd.Close acForm, Me.Name

End Sub

Here are the debug.print of my sql statements (with test values):

INSERT INTO tblDrawingRevision (DrawingFK, Revision, Reason) VALUES ('17', '0', 'New Drawing');
INSERT INTO tblRevisionStatus (Status, UpdatedOn, UpdatedBy, RevisionFK) VALUES ('1', 9/20/2021, GetDomainUsername(), '0');

 

[Gasman]

You are not concatenating your variables correctly?

 

[theDBguy]

Ok, I had a a lot of syntax issues with my SQL. Still having issues but here is the latest:

Private Sub Form_AfterInsert()
Dim RevDate As Date
Dim strSQL As String
Dim lngLastID

'Add a new record to tblDrawingRevisions
strSQL = " INSERT INTO tblDrawingRevision " _
       & "(DrawingFK, Revision, Reason) VALUES " _
       & "('" & Me.DrawingID & "', '0', 'New Drawing');"
Debug.Print Me.DrawingID
Debug.Print strSQL
DoCmd.RunSQL strSQL

'Get the ID of the most recent record in tblDrawingRevisions
lngLastID = CurrentDb.OpenRecordset("SELECT @@IDENTITY from tblDrawingRevision")(0)
Debug.Print lngLastID

'Add a new record into the revision status table
'1 is the Status code for "In Process"
RevDate = Date
strSQL = " INSERT INTO tblRevisionStatus " _
       & "(Status, UpdatedOn, UpdatedBy, RevisionFK) VALUES " _
       & "('1', " & RevDate & ", GetDomainUsername(), '" & lngLastID & "');"
Debug.Print RevDate
Debug.Print strSQL
DoCmd.RunSQL strSQL
DoCmd.Close acForm, Me.Name

End Sub

Here are the debug.print of my sql statements (with test values):

INSERT INTO tblDrawingRevision (DrawingFK, Revision, Reason) VALUES ('17', '0', 'New Drawing');
INSERT INTO tblRevisionStatus (Status, UpdatedOn, UpdatedBy, RevisionFK) VALUES ('1', 9/20/2021, GetDomainUsername(), '0');

A few things:

1. Make sure you use the correct delimiters for your data values. Numbers don't need any, strings/text need single or double quotes as delimiters, dates need the octothorpe or hashtags as delimiters
2. Dates in SQL need to be in either U.S. or ISO format
3. The @@IDENTITY function is connection specific. Try to follow my sample code using a With/End With block to make sure you're using the same connection to get the correct ID value

 

[JMongi]

I'm still working through what you posted. But, I noticed "Status" flagged in the Code indentifier. Is that a reserved word in SQL? should i change my field name to something else?

 

[theDBguy]

I'm still working through what you posted. But, I noticed "Status" flagged in the Code indentifier. Is that a reserved word in SQL? should i change my field name to something else?

Can't remember, but try enclosing it in square brackets for now.

 

[JMongi]

So, maybe I've misunderstood @Pat Hartman and @theDBguy

1. Is there a way to use @@IDENTITY without using it in conjunction with a DAO recordset like theDBguy's code?
2. If I use DAO VBA commands will that be compatible with a future migration to SQL server?
3. Do my questions indicate that I am misunderstanding something else?

I have modified my code block and the delimiters are working for the first SQL statement as I'm getting new records inserted. As theDBguy mentioned, my @@IDENTITY is not functioning quite right as it returns a value of 0 which then causes the subsequent SQL statement to fail.

 

[theDBguy]

1. Is there a way to use @@IDENTITY without using it in conjunction with a DAO recordset like theDBguy's code?

Hmm, I don't recall using a "recordset" in my sample code?

 

[theDBguy]

As theDBguy mentioned, my @@IDENTITY is not functioning quite right as it returns a value of 0 which then causes the subsequent SQL statement to fail.

As I mentioned in my previous post, try copying the technique I showed you in my sample code where I used the With/End With block to keep the entire process using the same connection.

 

[Gasman]

Your second sql statement will still fail according to your debug.print?, or so I would have thought?

 

[JMongi]

With db
.Execute strSQL, dbFailOnError
lngNewID = .OpenRecordset("SELECT @@IDENTITY")(0)
End With

Maybe that part was throwing me off?

 

[theDBguy]

Maybe that part was throwing me off?

Think of it as just a method like any other method. It's not really creating a recordset object.

It would have been, if it looked like this:

Dim rs As DAO.Recordset

Set rs = db.OpenRecordset("SELECT @@IDENTITY")
lngNewID = rs!ID

Set rs = Nothing

 

[JMongi]

So, here is my latest code. First record insert works. Storing the ID of that record works. Still getting a type mismatch on the second.

Private Sub Form_AfterInsert()
Dim db As DAO.Database
Dim RevDate As Date
Dim strSQL As String
Dim lngLastID As Long

Set db = CurrentDb()
RevDate = Date

With db
    'Insert the child record into tblDrawingRevision
    strSQL = " INSERT INTO tblDrawingRevision (DrawingFK, Revision, Reason) " _
            & "VALUES (" & Me.DrawingID & ", 0, 'New Drawing');"
    Debug.Print strSQL
    .Execute strSQL, dbFailOnError
  
    'Obtain the ID of the new child record
    lngLastID = .OpenRecordset("SELECT @@IDENTITY")(0)
    Debug.Print lngLastID
  
    'Insert the grandchild record into tblRevisionStatus
    strSQL = " INSERT INTO tblRevisionStatus (RevStatus, UpdatedOn, UpdatedBy, RevisionFK) " _
            & "VALUES (1, #" & Format(RevDate, "yyyy-mm-dd") & "#, GetDomainUsername(), " & lngLastID & ");"
    Debug.Print strSQL
    .Execute strSQL, dbFailOnError
End With

Set db = Nothing

DoCmd.Close acForm, Me.Name

End Sub

 

[JMongi]

Think of it as just a method like any other method. It's not really creating a recordset object.

It would have been, if it looked like this:

Dim rs As DAO.Recordset

Set rs = db.OpenRecordset("SELECT @@IDENTITY")
lngNewID = rs!ID

Set rs = Nothing

I got it now. Thanks.

 

[Gasman]

Your debug.print should show your error??????

 

[theDBguy]

I got it now. Thanks.

Congratulations! Good luck with the rest of your project.

Let us know if you need more help.

 

[JMongi]

Your debug.print should show your error??????

It should. But it was a dumb one!

I had the field value set to number because originally it was going to be FK reference. I was trying to write a string to a number field! Duh!

Problem fixed! Code working!