Finding Record ID of newly inserted record

JMongi said:
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?
Click to expand...
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.
 
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.

Code: 
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
 
Ok, I had a a lot of syntax issues with my SQL. Still having issues but here is the latest:

Code: 
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):
SQL: 
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');
 
You are not concatenating your variables correctly?
 
JMongi said:
Ok, I had a a lot of syntax issues with my SQL. Still having issues but here is the latest:

Code: 
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):
SQL: 
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');
Click to expand...
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
 
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?
 
JMongi said:
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?
Click to expand...
Can't remember, but try enclosing it in square brackets for now.
 
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.
 
JMongi said:
1. Is there a way to use @@IDENTITY without using it in conjunction with a DAO recordset like theDBguy's code?
Click to expand...
Hmm, I don't recall using a "recordset" in my sample code?
 
JMongi said:
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.
Click to expand...
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.
 
Your second sql statement will still fail according to your debug.print?, or so I would have thought?
 
With db
.Execute strSQL, dbFailOnError
lngNewID = .OpenRecordset("SELECT @@IDENTITY")(0)
End With
Click to expand...

Maybe that part was throwing me off?
 
JMongi said:
Maybe that part was throwing me off?
Click to expand...
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:
Code: 
Dim rs As DAO.Recordset

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

Set rs = Nothing
 
Last edited:
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.

Code: 
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
 
theDBguy said:
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:
Code: 
Dim rs As DAO.Recordset

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

Set rs = Nothing
Click to expand...

I got it now. Thanks.
 
Your debug.print should show your error??????
 
Gasman said:
Your debug.print should show your error??????
Click to expand...
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!
 
Really?, I thought it would be GetDomainUsername() ?
 
Maybe someone more knowledgeable can comment about why the function works when a string variable needs delimiters still.

For reference, here is the function code:

C-like: 
Public Function GetDomainUsername() As String
'Get the users Windows Domain Login username
Static sUser As String
If sUser = "" Then
    sUser = CreateObject("WScript.Network").UserName
End If
GetDomainUsername = sUser
End Function
 
You must log in or register to reply here.

Similar threads

C
Solved Position form to added record
Replies
8
Views
479
ClaraBarton
C
A
Open a form and insert data SQL
Replies
6
Views
591
andymartin3186
A
D
Opening record by ID opens a new record
Replies
8
Views
630
Mark_
M
G
Solved Update recordset skipping the last record
2
Replies
21
Views
814
The_Doc_Man
The_Doc_Man
E
Solved Add Record VBA Not working
Replies
18
Views
1,184
E9-Tech
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom